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

I tried this a different way before, but now I would like to approach
it slightly differently:

At the start of the macro, I would an inputbox to appear which asks me
for the min and max values (for example, min=240229 and max=240241).
Then, for as long as there are values, I would like it to loop through
and insert rows in between until everything is sequential between the
min and max values. Even if the last number in the sequence is 240240,
I still need it to insert 240241.

The most efficient way to achieve this seems to be by using the offset
function, to step (max-min) +2 rows down and start over again, instead
of looking for the branch number.

Unfortunately- whatever I try doesn't work. Any help would be greatly
appreciated!

Please try this.///If you are new to macros set the Security level to
low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11.
Insert a module and paste the below code. Save. Get back to Workbook.
Tools|Macro|Run Macro()

Sub Macro()

Dim lngRow
Dim lngTemp

lngRow = 1
Do While Range("A" & lngRow) <> ""
If Range("A" & lngRow) Like ["######"] Then
If Range("A" & lngRow) <> lngTemp Then
Rows(lngRow).Insert
Range("A" & lngRow) = lngTemp
End If
lngTemp = lngTemp + 1
Else
lngTemp = 240229
End If
lngRow = lngRow + 1
Loop

End Sub

--
If this post helps click Yes
---------------
Jacob Skaria


"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
>
> I tried this a different way before, but now I would like to approach
> it slightly differently:
>
> At the start of the macro, I would an inputbox to appear which asks me
> for the min and max values (for example, min=240229 and max=240241).
> Then, for as long as there are values, I would like it to loop through
> and insert rows in between until everything is sequential between the
> min and max values. Even if the last number in the sequence is 240240,
> I still need it to insert 240241.
>
> The most efficient way to achieve this seems to be by using the offset
> function, to step (max-min) +2 rows down and start over again, instead
> of looking for the branch number.
>
> Unfortunately- whatever I try doesn't work. Any help would be greatly
> appreciated!
>

A small correction..

Sub Macro()

Dim lngRow
Dim lngTemp

lngRow = 1
Do While Range("A" & lngRow) <> ""
If Range("A" & lngRow) Like ["######"] Then
If Range("A" & lngRow) <> lngTemp Then
Rows(lngRow).Insert
Range("A" & lngRow) = lngTemp
End If
lngTemp = lngTemp + 1
Else
If lngTemp > 240229 And lngTemp < 240242 Then
Rows(lngRow).Insert
Range("A" & lngRow) = lngTemp
lngTemp = lngTemp + 1
End If
lngTemp = 240229
End If
lngRow = lngRow + 1
Loop

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"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
>
> I tried this a different way before, but now I would like to approach
> it slightly differently:
>
> At the start of the macro, I would an inputbox to appear which asks me
> for the min and max values (for example, min=240229 and max=240241).
> Then, for as long as there are values, I would like it to loop through
> and insert rows in between until everything is sequential between the
> min and max values. Even if the last number in the sequence is 240240,
> I still need it to insert 240241.
>
> The most efficient way to achieve this seems to be by using the offset
> function, to step (max-min) +2 rows down and start over again, instead
> of looking for the branch number.
>
> Unfortunately- whatever I try doesn't work. Any help would be greatly
> appreciated!
>

That is awesome! It works like a charm!!!

How could I modify it if there would be an empty row in between 240241
(max) and the 240229 (the next min), to skip that empty row and go on?

Yesterday it was a bit late....Please find the below modified code to handle
blank entries. You can change the while condition if you have more than 1
blanks inbetween.

If this post helps click Yes
---------------
Jacob Skaria

Dim lngRow
Dim lngTemp
Dim lngBlank

lngRow = 1
Do
If Range("A" & lngRow) Like ["######"] Then
If Range("A" & lngRow) <> lngTemp Then
Rows(lngRow).Insert
Range("A" & lngRow) = lngTemp
End If
lngTemp = lngTemp + 1
lngBlank = 0
ElseIf Trim(Range("A" & lngRow)) <> "" Then
If lngTemp > 240229 And lngTemp < 240242 Then
Rows(lngRow).Insert
Range("A" & lngRow) = lngTemp
lngTemp = lngTemp + 1
End If
lngTemp = 240229
lngBlank = 0
Else
lngBlank = lngBlank + 1
End If
lngRow = lngRow + 1
Loop While lngBlank < 2