CODEWITHSUNDEEP
vbaexcelvariablesrange
Sebastian Koefoed
Sebastian Koefoed

Reputation: 143

Range based on cell value

I need code to change the range based on a cell value:

I can get it to work where the row number is dependent on the cell value as shown below, but I need the column value to be variable:

For Nassets = 1 To ws_data.Range("d2")
ws_data.Range("B" & Nassets).Value = 3
Next Nassets

If "d2" have the value 4, range B1:B4 = 3, however I want range B4:E4 = 3

Thanks in advance!

Upvotes: 0

Views: 234

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Based on the comments below, it look like you need to change the code so that the value in D2 represents the column within your loop, not the row - In which case:

For Nassets = 1 To ws_data.Range("d2")
    ws_data.Cells(4, Nassets).Value = 3 '// Where 4 is the row number
Next Nassets

This can be re-written to exlude the loop altogether like so:

ws_data.Range(Cells(4, 2), Cells(4, ws_data.Range("D2").Value)).Value = 3

Upvotes: 1

Related Questions