creating sequential rows

[PVT]

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!

 

[OssieMac]

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

 

[PVT]

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

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

 

[PVT]

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!

 

[OssieMac]

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.

 

[PVT]

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.

 

[OssieMac]

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)

 

[OssieMac]

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.

 

[PVT]

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

 

[PVT]

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!

 

[OssieMac]

Having After:=ActiveCell in this code is prone to problems. If the active
cell happens to be outside the column A:A then the code will fail. Suggest
you leave it out of the code and just use the following:-

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

However, having said that, if you are ever using the find and want to
guarantee that the code will find the first occurrence (of multiple
occurrences) in the specified range then use after the last cell in the
specified range because the next cell after the last cell in the specified
range in which you are looking is the first cell in the range. (Finding next
after the active cell does not find the active cell, even if it meets the
criteria, unless it is the only occurrence of the find criteria in the range;
it finds the next one after it.)

Like I said, ActiveCell is prone to problems so avoid it in the Find method.

In your code you could use the following:-

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

Where Rows.Count is the last row in the column.

Note that it does not mention the Activecell so it does not matter which
cell is the active cell.

Anyway hope it all works out OK for you and that this little lesson also
helps.

 

[PVT]

Thank you again, you've been a great help!

 

[PVT]

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

One additional question regarding the above exerpt out of the code:
I noticed that while my starting value is the same for every branch,
the ending value may be different. This leads to errors while running
the macro. How can I adjust the coding so that the ending value posted
is always going to be the last value?

For example, for branch 1, the ending value is 240241, but for branch
3 the ending value is 240235. How can I make sure that all the numbers
until 240241 get prefilled for all branches?

Thanks in advance for any help!

 

[OssieMac]

The code below uses a different approach for the find. However, under test I
believe that the first code example that I posted does work.

You have said that the first number in each branch is always the same so the
code below is searching for that number.

Edit the following variable in the code to match the first number:-

lngMinValue = 240229

Edit the following variable to match the highest number required:-

lngMaxValue = 240241

Note that lngMaxValue must be at least the value of the highest number in
the entire set of numbers.

Sub Insert_Numbers2()

Dim rngBranch As Range
Dim strBranch As String
Dim rngFind As Range
Dim rngTarget As Range
Dim lngMinValue As Long
Dim lngMaxValue As Long
Dim strFirstAddr As String


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

'Find Branch and assign cell to a variable
Set rngFind = Columns("A:A") _
.Find(What:=lngMinValue, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)

If Not rngFind Is Nothing Then
'Number found so process
'Save address of the first find
strFirstAddr = rngFind.Address

Do
Set rngTarget = rngFind
Do While rngTarget < 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

Set rngFind = Columns("A:A").FindNext(rngFind)
Loop While Not rngFind Is Nothing _
And rngFind.Address <> strFirstAddr

Else
'Minimum value not found on first find
'so cease processing
Exit Sub
End If

End Sub

 

[PVT]

Thank you again for helping me. While working with the codes, I have
found two issues:

1. In the first code you posted, if the last number is not 240241, it
will be a text string (for example 'totals'). Therefore, the following
formula seems to give an error: Abs(rngTarget.Offset(1).Value -
rngTarget.Value) > 1

2. The code in your last post gives an error, could that be because
there is no initial search for branch?

It's getting there but not quite perfect yet... If you have any
further help for me I would greatly appreciate it!