Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I loop this somehow?
Public Sub UpdateVarianceSheets()
Worksheets("Variance").Range("C5:E5").Value = Worksheets("Numbers").Range("C3:E3").Value Worksheets("Variance").Range("C6:E6").Value = Worksheets("Numbers").Range("F3:G3").Value Worksheets("Variance").Range("C7:E7").Value = Worksheets("Numbers").Range("I3:K3").Value End Sub The destination range ("Variance") is in 3 columns increasing by a row for each label. The target range ("Numbers") is in 3 columns but moves over to the next 3 columns on the same row for each label. This goes until row 19 for "Variance" and columns AV-AX for "Numbers" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I loop this somehow?
Try the below
Dim ws1 As Worksheet, ws2 As Worksheet Dim lngCol As Long, intTemp As Integer Set ws1 = Worksheets("Variance") Set ws2 = Worksheets("Numbers") For intTemp = 5 To 19 lngCol = (intTemp - 4) * 3 ws1.Range(ws1.Cells(intTemp, "C"), ws1.Cells(intTemp, "E")).Value = _ ws2.Range(ws2.Cells(3, lngCol), ws2.Cells(3, lngCol + 2)).Value Next If this post helps click Yes --------------- Jacob Skaria "Qaspec" wrote: Public Sub UpdateVarianceSheets() Worksheets("Variance").Range("C5:E5").Value = Worksheets("Numbers").Range("C3:E3").Value Worksheets("Variance").Range("C6:E6").Value = Worksheets("Numbers").Range("F3:G3").Value Worksheets("Variance").Range("C7:E7").Value = Worksheets("Numbers").Range("I3:K3").Value End Sub The destination range ("Variance") is in 3 columns increasing by a row for each label. The target range ("Numbers") is in 3 columns but moves over to the next 3 columns on the same row for each label. This goes until row 19 for "Variance" and columns AV-AX for "Numbers" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I loop this somehow?
Public Sub UpdateVarianceSheets()
RowCount = 3 NewRow = 5 With Worksheets("Numbers") Do While .Range("C" & RowCount) < "" ColCount = 3 Do While .Cells(RowCount, ColCount) < "" Worksheets("Variance") _ .Range("C" & NewRow & ":E" & NewRow).Value = _ .Range(.Cells(RowCount, ColCount), _ .Cells(RowCount, ColCount + 2)).Value NewRow = NewRow + 1 ColCount = ColCount + 3 Loop RowCount = RowCount + 1 Loop End With End Sub "Qaspec" wrote: Public Sub UpdateVarianceSheets() Worksheets("Variance").Range("C5:E5").Value = Worksheets("Numbers").Range("C3:E3").Value Worksheets("Variance").Range("C6:E6").Value = Worksheets("Numbers").Range("F3:G3").Value Worksheets("Variance").Range("C7:E7").Value = Worksheets("Numbers").Range("I3:K3").Value End Sub The destination range ("Variance") is in 3 columns increasing by a row for each label. The target range ("Numbers") is in 3 columns but moves over to the next 3 columns on the same row for each label. This goes until row 19 for "Variance" and columns AV-AX for "Numbers" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming |