How to write loop to fill the values?

[dmadhup]

Hi,

I need help to convert following code with any loop. Can I get any help, please.
thank you

Dim lastR As Long
lastR = Cells(Rows.count, "N").End(xlUp).Row - 5
Range("O6").Resize(lastR) = "=IF(N6<>"""",N6)"
Range("P6").Resize(lastR) = "=IF(N6<>"""",N6)"
Range("Q6").Resize(lastR) = "=IF(N6<>"""",N6)"
Range("R6").Resize(lastR) = "=IF(N6<>"""",N6)"
Range("S6").Resize(lastR) = "=IF(N6<>"""",N6)"
Range("T6").Resize(lastR) = "=IF(N6<>"""",N6)"
Range("U6").Resize(lastR) = "=IF(N6<>"""",N6)"

 

[Rick Rothstein]

You do not need a loop to do what that code does; the following should do the same thing

[table="width: 500"]
[tr]
	[td]Dim lastR As Long
lastR = Cells(Rows.Count, "N").End(xlUp).Row - 5
Range("O6").Resize(lastR, 7) = "=IF(N6<>"""",N6)"[/td]
[/tr]
[/table]

 

[6StringJazzer]

Your question isn't that clear but it sounds like you want to loop instead of having a line of code for each column:

Dim lastR As Long
Dim C As Long 

lastR = Cells(Rows.count, "N").End(xlUp).Row - 5

For C = Cells(1,"O").Column To Cells(1,"U").column
   Cells(6, C).Resize(lastR) = "=IF(N6<>"""",N6)"
Next C

 

[6StringJazzer]

Missed Rick's post on preview, his answer is preferred.

 

[dmadhup]

Thank you both of you!

I was trying to minimize the lines of code. Considering that I prefer to use Rick answer.

 

[Rick Rothstein]

I was trying to minimize the lines of code. Considering that I prefer to use Rick answer.

If you want to minimize lines of code, you can replace what I posted earlier with this single line of code...

[table="width: 500"]
[tr]
	[td]Range("O6").Resize(Cells(Rows.Count, "N").End(xlUp).Row - 5, 7) = "=IF(N6<>"""",N6)"[/td]
[/tr]
[/table]

 

[dmadhup]

Great! Thanks again, Rick.