Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return the values in another sheet
I have 2 sheets in a workbook. I am trying to code a procedure which
will cause the value of Sheet 2 to return the value contained is Sheet 1. Each row increments by 4 (ie - B4, B8, B12, B16...) and starts at row 4, except for B5. Sheet 2 A1 = value in sheet1 B4 Sheet 2 B1 = value in sheet1 C4 Sheet 2 C1 = value in sheet1 B5 Sheet 2 A2 = value in sheet1 B8 Sheet 2 B2 = value in sheet1 C8 Sheet 2 C2 = value in sheet1 B9 Sheet 2 A3 = value in sheet1 B12 Sheet 2 B3 = value in sheet1 C12 Sheet 2 C3 = value in sheet1 B13 And so on, until Sheet 1 ends, around row 2,500. Thanks in advance for any assistance... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return the values in another sheet
Hi Steve
This should do the trick. I assume you are in Sheet2 when this is run. Take care Marcus Sub Down4Rows() Dim i As Long, j As Long, lw As Long 'Run in sheet2 Application.ScreenUpdating = False 'Last row of sheet 1 lw = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row j = 4 ' the 4th row in sheet1 For i = 1 To lw Range("A" & i).Value = Sheets("Sheet1").Range("B" & j).Value Range("B" & i).Value = Sheets("Sheet1").Range("C" & j).Value Range("C" & i).Value = Sheets("Sheet1").Range("D" & j).Value 'etc etc j = j + 4 Next i Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return the values in another sheet
Thanks Marcus.
One small problem though. C1 needs to first equal the value in B5, not D4, and then every 4th row after that (B5, B9, B13, B17, etc...) It's a little different than the other 2. On Feb 11, 9:03*pm, marcus wrote: Hi Steve This should do the trick. *I assume you are in Sheet2 when this is run. Take care Marcus Sub Down4Rows() Dim i As Long, j As Long, lw As Long 'Run in sheet2 Application.ScreenUpdating = False 'Last row of sheet 1 lw = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row j = 4 ' the 4th row in sheet1 *For i = 1 To lw * * Range("A" & i).Value = Sheets("Sheet1").Range("B" & j).Value * * Range("B" & i).Value = Sheets("Sheet1").Range("C" & j).Value * * Range("C" & i).Value = Sheets("Sheet1").Range("D" & j).Value * * 'etc etc * * j = j + 4 *Next i Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return the values in another sheet
Hi Steve
My mistake. I made an assumption. Replace the third line in the loop with this; Range("C" & i).Value = Sheets("Sheet1").Range("B" & j + 1).Value That should get the job done. Take care Marcus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Used to return a worke sheet to origanan values | Excel Discussion (Misc queries) | |||
Look up in another sheet for values to return in 1st worksheet | Excel Discussion (Misc queries) | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Worksheet Functions | |||
copy values generated by conditional formula in one sheet to the other work sheet as values | Excel Discussion (Misc queries) | |||
VBA Syntax for VLOOKUP to return array of return values | Excel Programming |