Trouble with creating a loop

[MrsFraser07]

Hi all, Thanks in advance for your help! I'm trying to learn VBA by reading a book! I know this is completely wrong, but it is how I've started.

[FONT=times new roman][COLOR=#0000ff]Option Explicit[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Dim x As Long[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]x = Worksheets("Forecast").Range("B2:B591").Value[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Dim y As Long[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]y = Worksheets("Forecast").Range("C2:C591").Value[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Dim z As Long[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]z = Worksheets("Forecast").Range("D2:D591").Value[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]
[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Sub Macro3()[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]
[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Do While x <> Empty[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]    Worksheets("Forecast").Range("B2").Copy[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]    Worksheets("Interests").Range("S1").PasteSpecial Paste:=xlPasteValues[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]    Worksheets("Interests").Range("T36").Copy[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]    Worksheets("Forecast").Range("C2").PasteSpecial Past:=xlPasteValues[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Loop[/COLOR][/FONT]

[FONT=times new roman][COLOR=#0000ff]End Sub[/COLOR][/FONT]

What I am trying to accomplish is:

1. Go to Worksheets ("Forecast") and copy cell B2.
2. Go to Worksheets ("Interests") and paste the Value into cell S1.
3. While in Worksheets ("Interests") go to the very last cell in column T (currently T36, but it would be nice if I could tell it to just find the last cell as it will change with different info) and copy that value.
4. Go to Worksheets("Forecast") and paste the Value into cell C2.

I need the loop to move step 1 and step 4 down a row each time. Steps 2 and 3 will be the same cell all the time.

Hope that makes sense? Very much appreciate your help.

 

[Joe4]

There is a problem with your x, y, and z declarations. You cannot set a whole range of cells equal to a single numeric value.

As for your code loop, maybe try something like this:

Sub Macro3()
    
    Dim myRange As Range
    Dim cell As Range
    Dim myRow As Long
    
    Set myRange = Worksheets("Forecast").Range("B2:B591")
    
    Application.ScreenUpdating = False

    For Each cell In myRange
        If cell = "" Then Exit For
        myRow = cell.Row
        
        Worksheets("Forecast").Cells(myRow, "B").Copy
        Worksheets("Interests").Range("S1").PasteSpecial Paste:=xlPasteValues
        Worksheets("Interests").Cells(Rows.Count, "T").End(xlUp).Copy
        Worksheets("Forecast").Cells(myRow, "C").PasteSpecial Paste:=xlPasteValues
    Next cell

    Application.ScreenUpdating = True

End Sub

 

[MrsFraser07]

Thanks Joe4! This worked beautifully! I appreciate the help.

 

[codeliftsleep]

Hi all, Thanks in advance for your help! I'm trying to learn VBA by reading a book! I know this is completely wrong, but it is how I've started.

[FONT=times new roman][COLOR=#0000ff]Option Explicit[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Dim x As Long[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]x = Worksheets("Forecast").Range("B2:B591").Value[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Dim y As Long[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]y = Worksheets("Forecast").Range("C2:C591").Value[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Dim z As Long[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]z = Worksheets("Forecast").Range("D2:D591").Value[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]
[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Sub Macro3()[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]
[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Do While x <> Empty[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]    Worksheets("Forecast").Range("B2").Copy[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]    Worksheets("Interests").Range("S1").PasteSpecial Paste:=xlPasteValues[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]    Worksheets("Interests").Range("T36").Copy[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]    Worksheets("Forecast").Range("C2").PasteSpecial Past:=xlPasteValues[/COLOR][/FONT]
[FONT=times new roman][COLOR=#0000ff]Loop[/COLOR][/FONT]

[FONT=times new roman][COLOR=#0000ff]End Sub[/COLOR][/FONT]

What I am trying to accomplish is:

1. Go to Worksheets ("Forecast") and copy cell B2.
2. Go to Worksheets ("Interests") and paste the Value into cell S1.
3. While in Worksheets ("Interests") go to the very last cell in column T (currently T36, but it would be nice if I could tell it to just find the last cell as it will change with different info) and copy that value.
4. Go to Worksheets("Forecast") and paste the Value into cell C2.

I need the loop to move step 1 and step 4 down a row each time. Steps 2 and 3 will be the same cell all the time.

Hope that makes sense? Very much appreciate your help.

First thing I'll say is do not write code immediately underneath and in between variables. Declare the variables at the top of the page and then use a space between them, then you can assign them. I also like to leave a space between constants and variables if I use them, but that's probably me just being anal...