Reference a range with string concatenation

[Russ3Z]

I am trying to run a macro which, among other things, copies
information to a cell 1 row down from the previous cell each time it
is run.

The column, in this case D, remains the same each time, but the row is
referenced by a count that increases by 1 each time the macro is run,
stored as a variable.

So, basically I want to reference a cell like this:
Sheets("DB1").Range("D" & Variable).Select

where variable is 8, 9, 10, etc, thus giving me D8, D9, D10 as a
cell each time.

I get an error when trying to do this, can anyone provide any insight
into if or how I can make this work?

Thanks,
Russ

 

[Bob Phillips]

With Sheets("DB1")
.Range("D" & .Range("D1").End(xlDown)).Offset(1,0).Select
End With

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

hi russ,
you can't store a variable from a macro for future use. when the macro end,
all declared variable values are lost. to use your code, you would have to
store the variable in a cell on the sheet somewhere for future use.
something like
range("iv65000").value = yourvariable
then use something like this
Sheets("DB1").Range("D" & range("IV65000")).Select

but bob has suggested an alternative that seems to get around storing the
value on the sheet. I like his better. but your project....your call.

regards
FSt1

 

[Russ3Z]

Thanks for the responses.

Regarding storage of the variable, this was not an issue. To
illustrate, when this macro runs, it also creates a new worksheet, and
assigns the worksheet a numerical name (1, 2, 3, etc) and this number
is also stored in a cell on that sheet. The variable in question,
that I was trying to use, takes this number and adds 7 to it, so it
would only be a temporary use each time the macro is run.

So, I start with Sheet "1", and when i create sheet "2" I want to
paste information into cell D9, then D10 for sheet "3", each time
having the row offset by 7 relative to the sheet number.

Anyway, I will see if I can use what you two have suggested. Thanks
for the timely responses.

Russ