Changing static code to dynamic

[Todd Huttenstine]

Set rng1 = .Range("AJ4:AJ500")

The above code sets a range. The above code is static, but needs to be
dynamic because the columns will not always be AJ. The value of cell W4
contains the column letter so how would I rewrite the code to specify this?
For instance if cell W4 contains the value "CB" then the code would need
show CB4:CB500.


Also the below code is static and needs to be dynamic:
TextBox6.Value = .Cells(Cell.Row, 31).Value
The number (column number) will not always be 31, it may be 25 so in this
case the code would need to reference 25 instead of 31. The number is found
in W6.
How would I rewrite this code as well?

Thanx
Todd Huttenstine

 

[Frank Kabel]

Hi Todd
for the first question try (I assume you have a with statement before)
set rng1 = .range(.range("W4").value & "4:" & .range("W4").value &
"500")

for the second one use
TextBox6.Value = .Cells(Cell.Row, Activesheet.range("W6").value).Value

 

[Todd Huttenstine]

Hey thank you. Works perfectly.

Have a good night.

 

[Tom Ogilvy]

Todd,
You couldn't make the transition from this
? "OFFSET(" & Worksheets(8).Name & "!" & Range("W4").Value & "4,,,COUNTA(" &
Worksheets(8).Name & "!" & Range("W4").Value & ":" & Range("W4").Value &
"))"
OFFSET(Sheet8!AA4,,,COUNTA(Sheet8!AA:AA))

to your current question?