Does Excel VBA have a cell location variable that can be set to return to later?

[Frisbeeman]

I would like to know if there is a way to mark or identify a cell so I can return to that cell later in an Excel macro. The cell I want to return to varies as the macro executes. So I can’t use “E5” for instance. The macro works down data in a spreadsheet looking for a specific value. When it finds that value it copies nearby data to a different location. I need the macro to return to the cell where it found that value so it can continue to work down the worksheet. Does VBA have some sort of cell location variable that can be set before copying the adjacent data, going off to paste to the copied data in the data collection area such that I can return to where it left off?

Thank you in advance.

 

[Zot]

I just create a sheet and make it hidden to store variables that I need to recall on future execution.

 

[still learning]

Hi *Frisbeeman
First I'm have a lot to learn
What I do is :
before the macro leaves the cell it creates a range name for it .. like "here"
Then it can go back to it by having the macro go to that range.
then it deletes the range name.. ...so I can use it again

Not pretty...but it works

mike

 

[Frisbeeman]

But is there a way to store a cell location for later use? I know how to store a variable. X = ActiveCell.Value

Is there a way to store a cell location, like "E5", so the macro can return to that cell location after it goes off and does other things. The cell location I want it to come back to changes each iteration of the macro.

Something like X = ActiveCell.Location

Or perhaps X = Range(ActiveCell).Define

 

[RoryA]

You can store it in a range variable but there really shouldn't be any need for your macro to be selecting cells as a rule.

 

[Frisbeeman]

Dear Still Learning,

Do you know the syntax. I tried

L = Range(ActiveCell)

and

L = Range("ActiveCell")

But neither of those worked.

There must be a way to define the active cell as a location to eventually return to, and be able to later redefine the location as the macro repeats steps in the program working through the data. Like a location variable.

 

[Frisbeeman]

RoryA,

No that’s not true. I do have to be able to select a cell in this macro. This macro goes down a column of 1500 values looking for any value between say 40 and 50. As the macro goes down the column if the number is less than 40 or greater than 50 it ignores that and moves down 1 cell to see if the next value is between 40 and 50. Let’s say the first value between 40 and 50 is in cell “A33”. From cell “A33” the macro moves 1 cell to the right and copies the range of data from “B33” to “B283”. Then the macro goes to the data collection area in the worksheet and pastes that data. Then I need the macro to come back to cell “A33” and then resume working down the column of data.

So I need something like:

temporary location = ActiveCell - Before moving over one column to copy the range of data.

Then when the macro is ready to continue working through the data to go to that temporary location. And if the next value between 40 and 50 is in the cell “A45” then redefine temporary location as “A45”.

I googled and studied up on Range Variables but in the examples I saw I didn’t see anything that will do the above for me.

 

[RoryA]

I do have to be able to select a cell in this macro. This macro goes down a column of 1500 values looking for any value between say 40 and 50. As the macro goes down the column if the number is less than 40 or greater than 50 it ignores that and moves down 1 cell to see if the next value is between 40 and 50. Let’s say the first value between 40 and 50 is in cell “A33”. From cell “A33” the macro moves 1 cell to the right and copies the range of data from “B33” to “B283”. Then the macro goes to the data collection area in the worksheet and pastes that data. Then I need the macro to come back to cell “A33” and then resume working down the column of data.

None of that would require actually selecting anything, and doing so will make your macro a lot slower than it needs to be.

 

[Dan_W]

As RoryA points out, none of what you've described requires you to select or activate anything. The quicker (and less error prone) solution would be do process all of that in memory - if you'd like to research it yourself, I'd recommend using the keyword "array" or a really good website about arrays in VBA is: VBA for smarties: Arrays

I would also suggest storing that this information in the form of a range variable, but if you would nonetheless rather use that 'location' and you're going to be using the Select/Activecell approach you set above, the location information that you're looking for is stored in the .Address property of the cell. So:

Dim CellAddress As String
CellAddress = Activecell.Address

Hope that helps.

 

[Frisbeeman]

That worked great!!! Thank you!!