find and replace with an incremented value

[MarkRush]

So I have "quote" form that has over 16000 lines on it The Lines basically repeat evrry 112 lines but the site numbers are different.. The quote tool can handle up to 150 sites, My problem is when we need to update or add parts it takes me a whole day or more to update the quote sheet.

each line has a unique count that comes from a named cell such as Site__1_Widget2_qty, Site__1_Widget3_QTY etc

What i do when I need to make changes is delete sites 2 thru 150, Make my additions or deletions to site 1, Copy Site 1 section 149 times , then go thru and select all cells in site 2, Find Site__1 replace with Site__2, Move to next section , Find Site__1 replace with Site__3 etc etc

Right now i have a macro created that moves the cursor and selects the range to do the find and replace but I need help with the actual find and replace section and would love for it to increment

Sub select_rows()
'macro to move to next site and select all entries

ActiveCell.Offset(112, 0).Select
Range(ActiveCell, ActiveCell.Offset(107, 7)).Select



End Sub

here is a macro i recorded for the find and replace part


'
'

Sub findreplace()
' test
'


'
    Selection.Replace What:="Site__1", Replacement:="Site__2", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Ideally the macro asks me for my start value and end value and just increments the number...

 

[AlphaFrog]

This will copy Range("A2:G113") 149 times and paste it in the rows below .Offset(112) and each time replace Site__1 with Site__n

[color=darkblue]Sub[/color] Copy_Sites()
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Application.Calculation = xlCalculationManual
    [color=darkblue]With[/color] Range("A2:G113")
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] 149
            .Copy Destination:=.Offset(112 * i)
            .Offset(112 * i).Replace What:="Site__1", _
                                     Replacement:="Site__" & (i + 1), _
                                     LookAt:=xlPart, _
                                     SearchOrder:=xlByRows, _
                                     MatchCase:=False, _
                                     SearchFormat:=False, _
                                     ReplaceFormat:=[color=darkblue]False[/color]
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[MarkRush]

Thank you so much,, That pointed me in the right direction.. cut 8 hrs of tedious typing to a 15 minute exercise..

 

[MarkRush]

ALphaFrog, just wanted to say thank you again and share my final code..

This will copy Range("A2:G113") 149 times and paste it in the rows below .Offset(112) and each time replace Site__1 with Site__n

[COLOR=darkblue]Sub[/COLOR] Copy_Sites()
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    Application.Calculation = xlCalculationManual
    [COLOR=darkblue]With[/COLOR] Range("A2:G113")
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] 149
            .Copy Destination:=.Offset(112 * i)
            .Offset(112 * i).Replace What:="Site__1", _
                                     Replacement:="Site__" & (i + 1), _
                                     LookAt:=xlPart, _
                                     SearchOrder:=xlByRows, _
                                     MatchCase:=False, _
                                     SearchFormat:=False, _
                                     ReplaceFormat:=[COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

here is my final macro.. there were a few other items that needed to be changed

Sub Copy_Sites()
    Dim i As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    With Range("A116:M227")
        For i = 1 To 149
            .Copy Destination:=.Offset(112 * i)
            .Offset(112 * i).Replace What:="Site__1", _
                                     Replacement:="Site__" & (i + 1), _
                                     LookAt:=xlPart, _
                                     SearchOrder:=xlByRows, _
                                     MatchCase:=False, _
                                     SearchFormat:=False, _
                                     ReplaceFormat:=False
                                     
            .Offset(112 * i).Replace What:="Site 1", _
                                     Replacement:="Site " & (i + 1), _
                                     LookAt:=xlPart, _
                                     SearchOrder:=xlByRows, _
                                     MatchCase:=False, _
                                     SearchFormat:=False, _
                                     ReplaceFormat:=False
                                     
            .Offset(112 * i).Replace What:="SITE # 1", _
                                     Replacement:="Site #  " & (i + 1), _
                                     LookAt:=xlPart, _
                                     SearchOrder:=xlByRows, _
                                     MatchCase:=False, _
                                     SearchFormat:=False, _
                                     ReplaceFormat:=False
                                     
                                                            
            .Offset(112 * i).Replace What:="Site_1", _
                                     Replacement:="Site_" & (i + 1), _
                                     LookAt:=xlPart, _
                                     SearchOrder:=xlByRows, _
                                     MatchCase:=False, _
                                     SearchFormat:=False, _
                                     ReplaceFormat:=False
        Next i
    End With
    
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[AlphaFrog]

You're welcome. Give this a go...

[color=darkblue]Sub[/color] Copy_Sites()
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], vSite [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Application.Calculation = xlCalculationManual
    [color=darkblue]With[/color] Range("A116:M227")
        [color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] 149
            .Copy Destination:=.Offset(112 * i)
            [color=darkblue]For[/color] [color=darkblue]Each[/color] vSite [color=darkblue]In[/color] Array("Site__", "Site ", "SITE # ", "Site_")
                .Offset(112 * i).Replace What:=vSite & "1", _
                                         Replacement:=vSite & (i + 1), _
                                         LookAt:=xlPart, _
                                         SearchOrder:=xlByRows, _
                                         MatchCase:=False, _
                                         SearchFormat:=False, _
                                         ReplaceFormat:=[color=darkblue]False[/color]
             [color=darkblue]Next[/color] vSite
        [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[MarkRush]

That looks much simpler.. I will try it next time I need to make changes..