Reference a range with string concatenation

  • Thread starter Thread starter Russ3Z
  • Start date Start date
R

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
 
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)
 
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
 
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
 
Back
Top