Log calculation steps 1

[pruleone]

Hi

I have starting number 0 and max number 3.
After pressing Start system will start adding 0,25 until total sum is 3, then system stops working.

Do to it, I used LOOP and as I didn't know how to do it simply I used following logic -
Add 0,25 to into first cell
Add 0,25 to next free cell (ActiveCell.Offset(1, 0).Select)
etc

As I added additional values into first column than I added to into cell B1 formula =SUM(A:A)
So shortly code I used for Looping is following:

Do
If Sheets("Sheet4").Range("B1") >= Max_Value Then Exit Do
ActiveCell.Value = Add_Value
ActiveCell.Offset(1, 0).Select
Loop

Everything is fine (I works quick and it is not a problem). But now I want to show so called log file to user.
It should start with sentence: Calculation started, then I should see step-by-step adding this 0,25 and when 3 is reach there should be showed Calculation finished.

I used very ugly solution (at least I think so my self) - I copied into one cell in Excel text what I want to show as log file. Then made new userForm in VBA, added there textbox without editing option and chose value to be displayed from one excel sheet.
Why I don't like this solution is that currently in this example I have few lines and it is simple to drag them together into one cell in excel cell. But if loop result would be longer than I would have problems. Also this solution what I used is not dynamical - I can't just change Max_Value and then keep using file. I need to go and update also this one excel cell formula.

As I haven't come up with better solution for Loop command to be able to get any data for logging than question is - How to show in text box those calculation steps?. I understand there should be some kind of dynamical solution where start and end labels are fixed and than with dynamical formula middle part will be added, something like that:

 TextBox1.Value = Sheets("Sheet4").Range("e2") & [i]Dynamical part[/i] & Sheets("Sheet4").Range ("e2")

How put together such log file?

 

[SkipVought]

Hi,

I'm sorry, but I'm having difficulty understanding you.

A "log file" is an external file into which data is written or logged, for future reference. Is this what you are referring to?

OR it seems that you might be referring to simply an indication of where the program is in the loop to "show the user." Actually, looping through 16 (0 to 3 by 0.25) values and showing each value to the user would be meaningless sine it would happen almost instantaneously. This would typically be done I larger loops (tens of thousands) where every 100th or 1000th value would be shown. You can use the status bar. Look up StatusBar in VBA HELP. You will see a coded example, if that's what you're looking for.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pruleone]

Hi

First - Yes I know that looping through 16 steps in general is meaningless (it was just example also). Unless I will add comment to Loop code that after performing action wait 5 sec and then proceed with loop. Anyway this is not a wish currently.

So what I need or let's say what would be enough in this point is to have solution which takes text/info in several cells and displays it in one text box to user.
Start point of such range is set but end point may vary (dynamical range).
Also this text from different cells should be displayed as well in separate rows in text box (not as one long row).

Any ideas?
This simple solution is not working:

Private Sub UserForm_Activate()
TextBox1.Value = Sheets("Sheet1").Range("a1:a20")
End Sub

 

[Andrzejek]

Could you SHOW us an example of what you would have in your cells in Excel and what you would like to see in the [tt]TextBox1.Value [/tt]?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[pruleone]

I found one idea and modified this code:

Public Sub ChChChain()
 
  Dim rSource As Range
  Dim rTarget As Range
  Dim oCell As Range
  Dim sConcat As String
  
  On Error GoTo woops
  Set rSource = Sheets("Sheet1").Range("A1:A200")
  Set rTarget = Sheets("Sheet1").Range("c1")
  
  For Each oCell In rSource
    sConcat = sConcat & CStr(oCell.Value)
  Next oCell
  
  rTarget.Value = sConcat
 
woops:

End Sub

What does it give me now?
It gives me that texts in cells A1 till A200 are merged into one long line.

Using this code:

Private Sub UserForm_Activate()
TextBox1.Value = Sheets("Sheet1").Range("c1")
End Sub

I will get text box filled with text when UserForm will be activated.
Problem is that text what is merged is just one long row, all results copied from A1:A200 are presented as one long line. I would like to see this text in text box also in separate rows (look example file).

Basically if currently I have data in column A like this:
Buy
Sell
Sell
Buy
Buy
Buy
Sell

Then using CONCATENATE command in VBA this text will be presented to me like this: BuySellSellBuyBuyBuySell

In excel there is a way to use following formula / logic:
=Text1&"
"&Text2&"
"&Text3 ...
If I will now create manually such formula in excel
=Buy&"
"&Sell&"
"&Sell&"
"&Buy ...
and I will ask to get text box value from that cell then I will get such picture / solution what can be found here: [link ]

https://drive.google.com/file/d/0B9_1r5dG8HOZTHI0NklYS0tqbTg/view?usp=sharing

[/url]

So how it looks in picture this is what I want but I want to skip manual formula generation.
Example file can be found here: [link ]

https://drive.google.com/file/d/0B9_1r5dG8HOZNTRhOUttVUlzR0E/view?usp=sharing

[/url]

 

[Andrzejek]

How about - when concatenating your text, add to it something that will make it show nice in the text box, like a new line character:

  For Each oCell In rSource
    sConcat = sConcat & CStr(oCell.Value)[blue] & vbNewLine[/blue]
  Next oCell

Or add any character (a Space?) and then use Replace function to replace this character with new line?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[pruleone]

Thank you,

it works