Simple Loop

[normanbox]

I'm trying to create what I believe to be a simple loop to my VBA code, but unfortunately I don't know enough about VBA to create it, so I'm just posting the code I have and am hoping somebody could tell me what I need to do to loop the code. I need the loop to continue from column to column using the stock symbols that are in each row until I run out of stock symbols. All the stock symbols are in row 3, starting in column B.

Sub CopyReplace()
'
' CopyReplace Macro
'


'
Dim LstCo As Long, LstRw As Long
ActiveWindow.SmallScroll ToRight:=-2
Range("B5:B483").Select
Selection.Copy
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
LstRw = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, True).Row
LstCo = Rows("5:" & LstRw).Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, True).Column
With Range(Cells(5, LstCo), Cells(LstRw, LstCo))
.Replace What:="A.", Replacement:=Cells(3, LstCo).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
End With
ActiveWorkbook.Save


End Sub

Any help to get the above code to loop until there are no more stock symbols in row 3 is much appreciated!

 

[humdingaling]

not entirely sure what you are trying to achieve
the code you have copies column B and paste xltoright (from b5)

then replace "A." in this new "last column" with whatever is in row 3?
last row is always 483 as it you have already defined with the range you have selected?

I have shortened your code based on what you have said

Sub CopyReplace()

' CopyReplace Macro
    Dim LstCo As Long
    LstCo = Range("b5").End(xlToRight).Column + 1
    Range("B5:B483").Copy Cells(5, LstCo)
    With Range(Cells(5, LstCo), Cells(483, LstCo))
        .Replace What:="A.", Replacement:=Cells(3, LstCo).Value, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
    End With
    ActiveWorkbook.Save

End Sub

who not sure where the loops occurs and what you want to loop

 

[normanbox]

Right, that's exactly what the code does. All I'd like to do is loop the entire code until there is no more text (stock symbols) in row 3. My code works fine with what I need it to do, but I have over 5000 stock symbols in row 3 currently and I don't want to sit there and hit my macro button each time to copy the formulas in column B to the new column and updating the stock symbol to the symbol in the new column. I'm sure it's really confusing, sorry.

 

[humdingaling]

ok understand what you want now
see below code
i added extra comments so you can follow along
changed lstco only gets used for looping
curCo = the column you are working on within the loop

Sub CopyReplace()

' CopyReplace Macro
    Dim LstCo As Long 'number of loops
    Dim CurCo As Long 'Current Column
    Dim i As Long 'counter for loops
    
    i = 2 'start of loop column B = 2
    LstCo = Range("b3").End(xlToRight).Column 'determine number of loops
    
    Application.DisplayAlerts = False 'turn off alerts incase replace yields no results
    
    For i = 2 To LstCo
        
        If i = 2 Then
            CurCo = 3 'if first loop...curco set to 2
        Else
            CurCo = Range("b5").End(xlToRight).Column + 1 'current column = last with data +1
        End If
        
        Range("B5:B483").Copy Cells(5, CurCo)
        With Range(Cells(5, CurCo), Cells(483, CurCo))
            .Replace What:="A.", Replacement:=Cells(3, CurCo).Value, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True
            
        End With
        
    Next
        
    Application.DisplayAlerts = True
    
    'ActiveWorkbook.Save ' suggest you turn this turn in initial testing...get rid of the ' to turn back on

End Sub