On Feb 21, 5:52*am, OssieMac
wrote:
Hi,

Backup your workbook first in case the code does not perform exactly as you
require.

Note: The code is written to run on the Active worksheet.

If it does not do exactly as you want then let me know. I have included
comments to help you follow what is occurring.

Sub Insert_Numbers()

Dim rngBranch As Range
Dim strBranch As String
Dim lngBranchNo As Long
Dim rngTarget As Range
Dim lngMinValue As Long
Dim lngMaxValue As Long

'Edit following 2 lines if start/finish numbers change
lngMinValue = 240229
lngMaxValue = 240241

lngBranchNo = 1

Do
* * 'Create a string variable with Branch and Number.
* * strBranch = "Branch " & lngBranchNo

* * 'Find Branch and assign cell to a variable
* * Set rngTarget = Columns("A:A") _
* * * * .Find(What:=strBranch, _
* * * * LookIn:=xlFormulas, _
* * * * LookAt:=xlPart, _
* * * * SearchOrder:=xlByColumns, _
* * * * SearchDirection:=xlNext, _
* * * * MatchCase:=False, _
* * * * SearchFormat:=False)

* * If Not rngTarget Is Nothing Then
* * * * 'Branch Number found
* * * * 'Ensure first value is at correct starting value
* * * * If rngTarget.Offset(1).Value lngMinValue Then
* * * * * * rngTarget.Offset(1).EntireRow.Insert
* * * * * * Set rngTarget = rngTarget.Offset(1)
* * * * * * rngTarget.Value = lngMinValue
* * * * Else
* * * * * * Set rngTarget = rngTarget.Offset(1)
* * * * End If

* * * * Do While rngTarget.Value < lngMaxValue
* * * * * * If Abs(rngTarget.Offset(1).Value - rngTarget.Value) 1 Then
* * * * * * * * rngTarget.Offset(1).EntireRow.Insert
* * * * * * * * Set rngTarget = rngTarget.Offset(1)
* * * * * * * * rngTarget.Value = rngTarget.Offset(-1).Value + 1
* * * * * * Else
* * * * * * * * Set rngTarget = rngTarget.Offset(1)
* * * * * * End If

* * * * Loop
* * Else
* * * * 'Branch number not found so exit sub
* * * * Exit Sub
* * End If

* * 'Increment the Branch number to find
* * lngBranchNo = lngBranchNo + 1
Loop

End Sub

--
Regards,

OssieMac



Thank you for your help. For some reason it is giving me an error run
time error 448, named argument not found?