VBA - I'm probably approaching this all wrong, but...

[Khazimula]

I've been banging my head against this problem for a while now and its really frustrating me. I'm hoping someone out there may be able to help...

I am trying to find a way of storing the value of a variable, which keeps changing its value within a loop. The idea behind this is to have a statement within a loop that says a set variable "VariableA" equal to the value of the active cell. As the loop runs the active cell changes and in turn the value of VariableA changes too. I want to store all these values so that when the loop is complete, I can concatenate the list of active cell values.

All of this is straight forward to code, apart from finding a way to store the changing variable. My original idea was to have a variable that changed name with each loop:

Variable & Count.value = Activecell

This is not working though as I am not being allowed to concatenate a variable name.

Is there a way around this or will I have to find another way of recording and concatenating the values of a group of cells.

Any thoughts are very welcome!

 

[tusharm]

Keeping in mind that changing the activecell in a loop is not something I would do, how about

    x = x & activecell.value

 

[Khazimula]

Hmmmm...
Very interested in your first statement, but looking at your firstly, that wouldn't achieve the desired result as x would simply change value with each iteration, but the changed value of x would not be recorded.

Why wouldn't you change the activecell in a loop? How would you then go about concatenating the values of successive cells using a macro?

 

[tusharm]

If you say so...

{snip}but looking at your firstly, that wouldn't achieve the desired result as x would simply change value with each iteration, but the changed value of x would not be recorded.

 

[Khazimula]

I apologise if I offended Tusharm, I do appreciate your trying to help. Does anyone have any ideas?

 

[ChrisM]

Try using an array. If your loop range is fixed then you can safely dim it ahead of time with enough elements to contain all of your changing values. Otherwise you will need to dynamically determine the range size and use that to redim the array on the fly.

dim i as long

i = 0

Do

'your code

YourArray(i) = whatever

i = i + 1

Loop

Then when you're ready to concatenate:

YourArray(0) & YourArray(1) & .........

Back to tusharm's point, changing the activecell in code is inefficient and 98% of the time not neccessary. Depending on the size of your range, you could just read it all into an array in one step and go from there. Or, dim a range in code

dim myRange as Range

Then use offsets to loop through your range

myRange.offset(1,2) = whatever

This saves you from moving the active cell. Anytime you move that cell, Excel is executing a graphical operation, moving that little black outline, with all the associated graphical overhead, plus any selection change and recalculation procedures are being run.

 

[Khazimula]

Thank you very much for this response!! I have determined that your first solution works, but am now looking into the other options you have suggested. Thank you for clarifying the point about efficiency. Just out of interest would these inefficiences be bypassed if I had: Application.ScreenUpdating = False
Regardless though, I do appreciate that there is often another way of doing something, and take that on-board.

Thanks again!