For Loop Offset

youngst7

New Member
Joined
Sep 17, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi there, I'm new to Macros and am trying to set up a for loop between two worksheets. I have been able to create a macro that reads the first row of data but I want the macro to continue running down the next 149 rows of data using offset but can't figure it out. Any help is appreciated! I have included the code of the macro for the first row. I recorded copying and pasting between the worksheets to create the macro but if there is a better way to run it that would be helpful also!

VBA Code:
Sub Macro1()

    Range("B2").Select
    Selection.Copy
    Sheets("Analysis").Select
    Range("C9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    Range("D2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("C10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    Range("E2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("C11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    Range("F2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("C12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B41").Select
    Sheets("Data").Select
    Range("H2").Select
    Sheets("Analysis").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Select
    Range("J2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Analysis").Select
    Range("C29").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("L2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("N2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("O2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("P2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Analysis").Select
    Range("O15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Data").Select
    Range("Q2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Hi and welcome to MrExcel.

It is simpler than it seems.
But I don't understand some of your data.
I show you another approach for the data in row 2.
You show me in which cells you want the data for row 3 and I help you complete the cycle.

VBA Code:
Sub macro2()
  'Pass data from the "Data" sheet to the "Analysis" sheet
  Sheets("Analysis").Range("C9").Value = Sheets("Data").[B2]
  Sheets("Analysis").Range("C10").Value = Sheets("Data").[D2]
  Sheets("Analysis").Range("C11").Value = Sheets("Data").[E2]
  Sheets("Analysis").Range("C12").Value = Sheets("Data").[F2]
  Sheets("Analysis").Range("C29").Value = Sheets("Data").[J2]
  Sheets("Analysis").Range("B41").Value = Sheets("Data").[F2]  '<--- Could you confirm if this is correct
  
  'Pass data from the "Analysis" sheet to the "Data" sheet
  Sheets("Data").Range("K2").Value = Sheets("Analysis").[O2]
  Sheets("Data").Range("L2").Value = Sheets("Analysis").[O7]
  Sheets("Data").Range("M2").Value = Sheets("Analysis").[O8]
  Sheets("Data").Range("N2").Value = Sheets("Analysis").[O10]
  Sheets("Data").Range("O2").Value = Sheets("Analysis").[O12]
  Sheets("Data").Range("P2").Value = Sheets("Analysis").[O13]
  Sheets("Data").Range("Q2").Value = Sheets("Analysis").[O15]
  
  'Then, based on the above, put the movements for the next row.
  'That is, I need to know which cells go from the data sheet to the analyis sheet.
  'and which cells go from the analysis sheet to the data sheet.
  'With those 2 examples, I could identify the results and I can cycle.
  
End Sub
 

youngst7

New Member
Joined
Sep 17, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi and welcome to MrExcel.

It is simpler than it seems.
But I don't understand some of your data.
I show you another approach for the data in row 2.
You show me in which cells you want the data for row 3 and I help you complete the cycle.

VBA Code:
Sub macro2()
  'Pass data from the "Data" sheet to the "Analysis" sheet
  Sheets("Analysis").Range("C9").Value = Sheets("Data").[B2]
  Sheets("Analysis").Range("C10").Value = Sheets("Data").[D2]
  Sheets("Analysis").Range("C11").Value = Sheets("Data").[E2]
  Sheets("Analysis").Range("C12").Value = Sheets("Data").[F2]
  Sheets("Analysis").Range("C29").Value = Sheets("Data").[J2]
  Sheets("Analysis").Range("B41").Value = Sheets("Data").[F2]  '<--- Could you confirm if this is correct
 
  'Pass data from the "Analysis" sheet to the "Data" sheet
  Sheets("Data").Range("K2").Value = Sheets("Analysis").[O2]
  Sheets("Data").Range("L2").Value = Sheets("Analysis").[O7]
  Sheets("Data").Range("M2").Value = Sheets("Analysis").[O8]
  Sheets("Data").Range("N2").Value = Sheets("Analysis").[O10]
  Sheets("Data").Range("O2").Value = Sheets("Analysis").[O12]
  Sheets("Data").Range("P2").Value = Sheets("Analysis").[O13]
  Sheets("Data").Range("Q2").Value = Sheets("Analysis").[O15]
 
  'Then, based on the above, put the movements for the next row.
  'That is, I need to know which cells go from the data sheet to the analyis sheet.
  'and which cells go from the analysis sheet to the data sheet.
  'With those 2 examples, I could identify the results and I can cycle.
 
End Sub
So, need to copy numbers from cells B2, D2, E2, F2, H2, and J2 of the Data sheet over to C9, C10, C11, C12, C19 and C29 on the Analysis sheet respectively. There are formulas in the Analysis sheet that I want to then bring back the numbers in Analysis O2, O7, O8, O10, O13, and O9 and paste them in Data K2, L2, M2, N2, O2, P2 and Q2 respectively. Then I want it to run the same function for the rest of the sheet. I was able to record a macro which I posted initially which runs on the first line but I don't know how to get it to run successfully the rest of the 149 rows down the Data sheet.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
Sub macro2()
  Dim shD As Worksheet, shA As Worksheet
  Dim i As Long
  
  Set shD = Sheets("Data")
  Set shA = Sheets("Analysis")
  
  For i = 2 To shD.Range("B" & Rows.Count).End(3).Row
  
    'copy numbers from cells B2, D2, E2, F2, H2, and J2 of the Data sheet
    'over to C9, C10, C11, C12, C19 and C29 on the Analysis sheet respectively.
    shA.Range("C9").Value = shD.Range("B" & i).Value
    shA.Range("C10").Value = shD.Range("D" & i).Value
    shA.Range("C11").Value = shD.Range("E" & i).Value
    shA.Range("C12").Value = shD.Range("F" & i).Value
    shA.Range("C19").Value = shD.Range("H" & i).Value
    shA.Range("C29").Value = shD.Range("J" & i).Value
    
    'in Analysis O2, O7, O8, O10, O13, and O9 and paste them in Data K2, L2, M2, N2, O2, P2 and Q2
    'According to your code:
    'in Analysis O2, O7, O8, O10, O12, O13 and O15 and paste them
    'in Data K2, L2, M2, N2, O2, P2 and Q2 respectively.
    shD.Range("K" & i).Value = shA.Range("O2").Value
    shD.Range("L" & i).Value = shA.Range("O7").Value
    shD.Range("M" & i).Value = shA.Range("O8").Value
    shD.Range("N" & i).Value = shA.Range("O10").Value
    shD.Range("O" & i).Value = shA.Range("O12").Value
    shD.Range("P" & i).Value = shA.Range("O13").Value
    shD.Range("Q" & i).Value = shA.Range("O15").Value
  Next
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,928
Messages
5,545,080
Members
410,652
Latest member
Zot
Top