Hello,

I am looking for a macro or other solution which can loop through my
worksheet. The worksheet looks as follows:

Branch 1
240230 x $22
240239 y $25
240240 z $26

Totals

Branch 2
240230 x $30
240231 a $58
240232 b $22
240241 c $18

Totals

Branch 3
240229 d $20
240231 a $22
240232 b $23
240240 z $22

Totals

I would like it to compare the value of the number in column A with
that of the number above/below it. If there is a difference of more
than 1, I would like it to insert a row with that number, so that
eventually all the numbers are sequential. The inserted row should
only have the number, no other values in that row.

There should be a minimum (240229) and a maximum (240241) found for
all values in column A of sheet 1 and everything in between there for
each branch should be filled in.

For example:

Branch 1
240229
240230 x $22
240231
240232
240233
240234
240235
240236
240237
240238
240239 y $25
240240 z $26
240241

Totals

Branch 2
240229
240230 x $30
240231 a $58
240232 b $22
240233
240234
240235
240236
240237
240238
240239
240240
240241 c $18

Totals

Branch 3
240229 d $20
240230
240231 a $22
240232 b $23
240233
240234
240235
240236
240237
240238
240239
240240 z $22
240241

Totals

Any help would be greatly appreciated!

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


"PVT" wrote:

> Hello,
>
> I am looking for a macro or other solution which can loop through my
> worksheet. The worksheet looks as follows:
>
> Branch 1
> 240230 x $22
> 240239 y $25
> 240240 z $26
>
> Totals
>
> Branch 2
> 240230 x $30
> 240231 a $58
> 240232 b $22
> 240241 c $18
>
> Totals
>
> Branch 3
> 240229 d $20
> 240231 a $22
> 240232 b $23
> 240240 z $22
>
> Totals
>
> I would like it to compare the value of the number in column A with
> that of the number above/below it. If there is a difference of more
> than 1, I would like it to insert a row with that number, so that
> eventually all the numbers are sequential. The inserted row should
> only have the number, no other values in that row.
>
> There should be a minimum (240229) and a maximum (240241) found for
> all values in column A of sheet 1 and everything in between there for
> each branch should be filled in.
>
> For example:
>
> Branch 1
> 240229
> 240230 x $22
> 240231
> 240232
> 240233
> 240234
> 240235
> 240236
> 240237
> 240238
> 240239 y $25
> 240240 z $26
> 240241
>
> Totals
>
> Branch 2
> 240229
> 240230 x $30
> 240231 a $58
> 240232 b $22
> 240233
> 240234
> 240235
> 240236
> 240237
> 240238
> 240239
> 240240
> 240241 c $18
>
> Totals
>
> Branch 3
> 240229 d $20
> 240230
> 240231 a $22
> 240232 b $23
> 240233
> 240234
> 240235
> 240236
> 240237
> 240238
> 240239
> 240240 z $22
> 240241
>
> Totals
>
> Any help would be greatly appreciated!
>

On Feb 21, 5:52*am, OssieMac <Ossie...@discussions.microsoft.com>
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?

Actually, if it would be easier, I can also fix the range to compare
the one column to. Say 240230 is the min and 240249 is the max.

Each branch will have 240230 as its first number.

What I need is a loop that goes through and will insert rows and also
should add the missing number.

Something like:
1. Find first occurrence of 240230
2. Go to row below.
- If number equals 240231, go to next row.
- If number > 240231, insert a row and insert the number 240231 in
column A. Start over at 240230.
3. Loop through list until 240249 is reached

4. Find second occurrence of 240230
etc


There will be a maximum of 15 occurrences of 240230 for the 15
branches.

The desired end result is still the same as in the first post.



I realize this is probably real basic VBA but I am struggling so any
help is greatly appreciated!

The code works perfectly with the sample data you posted and it produces a
result identical to the desired result you posted so we need to establish
what the problem is.

Error 448 suggests that you are calling the sub routine from another sub and
that you are trying to pass a parameter to it.
The code that I provided is not desiged to accept a parameter. If this is
what you are doing then post the sample of code you are using to call the
subroutine and let me know what the value of parameter is that you are trying
to pass to it.

Your quote: "Actually, if it would be easier, I can also fix the range to
compare the one column to. Say 240230 is the min and 240249 is the max."
Just edit the following two lines of code for whatever start and finish
values you require.

lngMinValue = 240229
lngMaxValue = 240241


Your quote: "What I need is a loop that goes through and will insert rows
and also should add the missing number."
The code does this. Inserts lines plus the missing number.

--
Regards,

OssieMac


"PVT" wrote:

> Actually, if it would be easier, I can also fix the range to compare
> the one column to. Say 240230 is the min and 240249 is the max.
>
> Each branch will have 240230 as its first number.
>
> What I need is a loop that goes through and will insert rows and also
> should add the missing number.
>
> Something like:
> 1. Find first occurrence of 240230
> 2. Go to row below.
> - If number equals 240231, go to next row.
> - If number > 240231, insert a row and insert the number 240231 in
> column A. Start over at 240230.
> 3. Loop through list until 240249 is reached
>
> 4. Find second occurrence of 240230
> etc
>
>
> There will be a maximum of 15 occurrences of 240230 for the 15
> branches.
>
> The desired end result is still the same as in the first post.
>
>
>
> I realize this is probably real basic VBA but I am struggling so any
> help is greatly appreciated!
>

In theory the code looks like it would work perfectly!

Could it have something to do with the fact that I am using Excel
version 2000 9.0.3821 SR-1 ?
I can't get it to work even on the sample data from a brand new
workbook. I am not trying to have it accept a parameter.

I haven't got xl2000 to test but I am guessing that it is a parameter in the
find code giving a problem. (Probably SearchFormat but you don't need it
anyway.)
That could also explain the error code you were getting but I did not think
of it before.

A little extra info in case you are not aware of it because it affects my
next statement. A space and an underscore at the end of a line is a line
break in an otherwise single line of code.

Anyway replace the entire line of find code with the following.

Set rngTarget = Columns("A:A") _
.Find(What:=strBranch, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)

--
Regards,

OssieMac


"PVT" wrote:

> In theory the code looks like it would work perfectly!
>
> Could it have something to do with the fact that I am using Excel
> version 2000 9.0.3821 SR-1 ?
> I can't get it to work even on the sample data from a brand new
> workbook. I am not trying to have it accept a parameter.
>

If my previous post does not work and it appears to be failing on the Find,
then perhaps you could do something for me to help solve the problem.

Turn on the macro recorder and then find something on the worksheet. (find
the word Branch will do.) In the options section of the Find dialog box,
ensure Match entire cell contents is un-checked.

Turn off the macro recorder then post the actual find code that was
generated. I just want to see what parameters are there.

--
Regards,

OssieMac


"PVT" wrote:

> In theory the code looks like it would work perfectly!
>
> Could it have something to do with the fact that I am using Excel
> version 2000 9.0.3821 SR-1 ?
> I can't get it to work even on the sample data from a brand new
> workbook. I am not trying to have it accept a parameter.
>

Cells.Find(What:="branch", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate

Awesome, that did it! This is the code I ended up using (the part with
the find function):

Set rngTarget = Columns("A:A") _
.Find(What:=strBranch, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

I can't thank you enough for your help!