CODEWITHSUNDEEP
excelvba
Jonorl
Jonorl

Reputation: 47

Need to create a loop until last row, but only for certain ranges

Sub asd()

Sheets("Price calculator other regions").Range("E6").Value = Sheets("Sheet1").Range("B5")

Sheets("Price calculator other regions").Range("E25").Value = Sheets("Sheet1").Range("C5")

Sheets("Sheet1").Range("D5").Value = Sheets("Price calculator other regions").Range("E32")

End Sub

I need the values B5, C5 and D5 to move one row below (B6, C6, D6 and so on) until the last row, but the values on "Price calculator other regions" need to remain fixed.

Thanks in advance

Upvotes: 1

Views: 222

Answers (2)

urdearboy
urdearboy

Reputation: 14590

Kudos for going for the value transfer instead of copy/pasting!

To complete your loop, you just need to sub your relevant row numbers with a counter variable (variable i). To implement, find the last row (determined by Sheet 1 Column B), then, loop through your rows.

The loop is currently set to start at the 2nd row on Sheet 1. If you need to start on the 5th row, change the loop to For i = 5 to LR

Sub asd_v2()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim ps As Worksheet: Set ps = ThisWorkbook.Sheets("Price calculator other regions")

Dim LR As Long, i As Long
LR = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

For i = 2 To LR
    ps.Range("E6").Value = ws.Range("B" & i).Value
    ps.Range("E25").Value = ws.Range("C" & i).Value
    ws.Range("D" & i).Value = ps.Range("E32").Value
Next i

End Sub

Upvotes: 1

Harassed Dad
Harassed Dad

Reputation: 4714

Dim r as range
set r = Sheets("Sheet1").Range("B5")
do  'start loop
with Sheets("Price calculator other regions")

.Range("E6").Value = r

.Range("E25").Value = r.offset(0,1)

r.offset(0,2) = .Range("E32")
set r = r.offset(1,0)
loop until r.row > r.parent.usedrange.rows.count

end with

Upvotes: 0

Related Questions