I have a file that needs to go into my application but all the data is
grouped together. My application will recognize it is moved on to a new data
set when it see's -999.99, and the application is smart enough to say hey
this an entirely new data different from the data above it.

Here is generic example of what I have:

23 95.6 10003
23 80 650
23 2 897
49 600 10000
49 23 11

Here is what I would like to have:
23 95.6 10003
23 80 650
23 2 897
-999.99 -999.99 -999.99
49 600 10000
49 23 11

So whenever the value changes in column A to a new value I need it to insert
a new row of data that have the values of -999.99.

Thank you for the help.

I've assumed, and coded things so that the split is based on values in column
A and you just need the 3 -999.99 entries on the row

Press [Alt]+[F11] to open the VB editor, use Insert -- Module to add a code
module, then copy the code below, do any editing you need to it, and run it
via Tools -- Macro -- Macros

As I always recommend, try this on a copy of your data first.

Sub InsertMarkerRows()
Const dataMarker = -999.99
Const columnToTest = "A"

Dim currentValue As Integer ' may have to change type
Dim lastRow As Long
Dim LC As Long ' Loop Counter

lastRow = Range(columnToTest & Rows.Count). _
End(xlUp).Row
currentValue = Range(columnToTest & lastRow).Value
For LC = lastRow To 2 Step -1
If Range(columnToTest & LC).Offset(-1, 0) _
< currentValue Then
Range(columnToTest & LC).EntireRow.Insert
Range(columnToTest & LC).Value = dataMarker
Range(columnToTest & LC).Offset(0, 1) _
= dataMarker
Range(columnToTest & LC).Offset(0, 2) _
= dataMarker
currentValue = Range(columnToTest & LC). _
Offset(-1, 0)
End If
Next
End Sub


"James" wrote:

I have a file that needs to go into my application but all the data is
grouped together. My application will recognize it is moved on to a new data
set when it see's -999.99, and the application is smart enough to say hey
this an entirely new data different from the data above it.

Here is generic example of what I have:

23 95.6 10003
23 80 650
23 2 897
49 600 10000
49 23 11

Here is what I would like to have:
23 95.6 10003
23 80 650
23 2 897
-999.99 -999.99 -999.99
49 600 10000
49 23 11

So whenever the value changes in column A to a new value I need it to insert
a new row of data that have the values of -999.99.

Thank you for the help.

I am having problems with this line of code it says I have a "Type Mismatch"

Dim currentValue As Integer 'may have to change type

currentValue = Range(columnToTest & lastRow).Value

What should I change the "type" to.

Thank you

"JLatham" wrote:

I've assumed, and coded things so that the split is based on values in column
A and you just need the 3 -999.99 entries on the row

Press [Alt]+[F11] to open the VB editor, use Insert -- Module to add a code
module, then copy the code below, do any editing you need to it, and run it
via Tools -- Macro -- Macros

As I always recommend, try this on a copy of your data first.

Sub InsertMarkerRows()
Const dataMarker = -999.99
Const columnToTest = "A"

Dim currentValue As Integer ' may have to change type
Dim lastRow As Long
Dim LC As Long ' Loop Counter

lastRow = Range(columnToTest & Rows.Count). _
End(xlUp).Row
currentValue = Range(columnToTest & lastRow).Value
For LC = lastRow To 2 Step -1
If Range(columnToTest & LC).Offset(-1, 0) _
< currentValue Then
Range(columnToTest & LC).EntireRow.Insert
Range(columnToTest & LC).Value = dataMarker
Range(columnToTest & LC).Offset(0, 1) _
= dataMarker
Range(columnToTest & LC).Offset(0, 2) _
= dataMarker
currentValue = Range(columnToTest & LC). _
Offset(-1, 0)
End If
Next
End Sub


"James" wrote:

I have a file that needs to go into my application but all the data is
grouped together. My application will recognize it is moved on to a new data
set when it see's -999.99, and the application is smart enough to say hey
this an entirely new data different from the data above it.

Here is generic example of what I have:

23 95.6 10003
23 80 650
23 2 897
49 600 10000
49 23 11

Here is what I would like to have:
23 95.6 10003
23 80 650
23 2 897
-999.99 -999.99 -999.99
49 600 10000
49 23 11

So whenever the value changes in column A to a new value I need it to insert
a new row of data that have the values of -999.99.

Thank you for the help.

I presumed your entries in column a were relatively small whole numbers
(integers). If they happen to get very large or if there is text in them,
then we need something else.

Let's go with the very generic type; Variant

So change
Dim currentValue As Integer
to
Dim currentValue As Variant

hopefully that'll handle things for you.

"James" wrote:

I am having problems with this line of code it says I have a "Type Mismatch"

Dim currentValue As Integer 'may have to change type

currentValue = Range(columnToTest & lastRow).Value

What should I change the "type" to.

Thank you

"JLatham" wrote:

I've assumed, and coded things so that the split is based on values in column
A and you just need the 3 -999.99 entries on the row

Press [Alt]+[F11] to open the VB editor, use Insert -- Module to add a code
module, then copy the code below, do any editing you need to it, and run it
via Tools -- Macro -- Macros

As I always recommend, try this on a copy of your data first.

Sub InsertMarkerRows()
Const dataMarker = -999.99
Const columnToTest = "A"

Dim currentValue As Integer ' may have to change type
Dim lastRow As Long
Dim LC As Long ' Loop Counter

lastRow = Range(columnToTest & Rows.Count). _
End(xlUp).Row
currentValue = Range(columnToTest & lastRow).Value
For LC = lastRow To 2 Step -1
If Range(columnToTest & LC).Offset(-1, 0) _
< currentValue Then
Range(columnToTest & LC).EntireRow.Insert
Range(columnToTest & LC).Value = dataMarker
Range(columnToTest & LC).Offset(0, 1) _
= dataMarker
Range(columnToTest & LC).Offset(0, 2) _
= dataMarker
currentValue = Range(columnToTest & LC). _
Offset(-1, 0)
End If
Next
End Sub


"James" wrote:

I have a file that needs to go into my application but all the data is
grouped together. My application will recognize it is moved on to a new data
set when it see's -999.99, and the application is smart enough to say hey
this an entirely new data different from the data above it.

Here is generic example of what I have:

23 95.6 10003
23 80 650
23 2 897
49 600 10000
49 23 11

Here is what I would like to have:
23 95.6 10003
23 80 650
23 2 897
-999.99 -999.99 -999.99
49 600 10000
49 23 11

So whenever the value changes in column A to a new value I need it to insert
a new row of data that have the values of -999.99.

Thank you for the help.

Thank you very much it works and this well help a lot.

"JLatham" wrote:

I presumed your entries in column a were relatively small whole numbers
(integers). If they happen to get very large or if there is text in them,
then we need something else.

Let's go with the very generic type; Variant

So change
Dim currentValue As Integer
to
Dim currentValue As Variant

hopefully that'll handle things for you.

"James" wrote:

I am having problems with this line of code it says I have a "Type Mismatch"

Dim currentValue As Integer 'may have to change type

currentValue = Range(columnToTest & lastRow).Value

What should I change the "type" to.

Thank you

"JLatham" wrote:

I've assumed, and coded things so that the split is based on values in column
A and you just need the 3 -999.99 entries on the row

Press [Alt]+[F11] to open the VB editor, use Insert -- Module to add a code
module, then copy the code below, do any editing you need to it, and run it
via Tools -- Macro -- Macros

As I always recommend, try this on a copy of your data first.

Sub InsertMarkerRows()
Const dataMarker = -999.99
Const columnToTest = "A"

Dim currentValue As Integer ' may have to change type
Dim lastRow As Long
Dim LC As Long ' Loop Counter

lastRow = Range(columnToTest & Rows.Count). _
End(xlUp).Row
currentValue = Range(columnToTest & lastRow).Value
For LC = lastRow To 2 Step -1
If Range(columnToTest & LC).Offset(-1, 0) _
< currentValue Then
Range(columnToTest & LC).EntireRow.Insert
Range(columnToTest & LC).Value = dataMarker
Range(columnToTest & LC).Offset(0, 1) _
= dataMarker
Range(columnToTest & LC).Offset(0, 2) _
= dataMarker
currentValue = Range(columnToTest & LC). _
Offset(-1, 0)
End If
Next
End Sub


"James" wrote:

I have a file that needs to go into my application but all the data is
grouped together. My application will recognize it is moved on to a new data
set when it see's -999.99, and the application is smart enough to say hey
this an entirely new data different from the data above it.

Here is generic example of what I have:

23 95.6 10003
23 80 650
23 2 897
49 600 10000
49 23 11

Here is what I would like to have:
23 95.6 10003
23 80 650
23 2 897
-999.99 -999.99 -999.99
49 600 10000
49 23 11

So whenever the value changes in column A to a new value I need it to insert
a new row of data that have the values of -999.99.

Thank you for the help.

Glad to hear that the fix worked. Enjoy.

"James" wrote:

Thank you very much it works and this well help a lot.

"JLatham" wrote:

I presumed your entries in column a were relatively small whole numbers
(integers). If they happen to get very large or if there is text in them,
then we need something else.

Let's go with the very generic type; Variant

So change
Dim currentValue As Integer
to
Dim currentValue As Variant

hopefully that'll handle things for you.

"James" wrote:

I am having problems with this line of code it says I have a "Type Mismatch"

Dim currentValue As Integer 'may have to change type

currentValue = Range(columnToTest & lastRow).Value

What should I change the "type" to.

Thank you

"JLatham" wrote:

I've assumed, and coded things so that the split is based on values in column
A and you just need the 3 -999.99 entries on the row

Press [Alt]+[F11] to open the VB editor, use Insert -- Module to add a code
module, then copy the code below, do any editing you need to it, and run it
via Tools -- Macro -- Macros

As I always recommend, try this on a copy of your data first.

Sub InsertMarkerRows()
Const dataMarker = -999.99
Const columnToTest = "A"

Dim currentValue As Integer ' may have to change type
Dim lastRow As Long
Dim LC As Long ' Loop Counter

lastRow = Range(columnToTest & Rows.Count). _
End(xlUp).Row
currentValue = Range(columnToTest & lastRow).Value
For LC = lastRow To 2 Step -1
If Range(columnToTest & LC).Offset(-1, 0) _
< currentValue Then
Range(columnToTest & LC).EntireRow.Insert
Range(columnToTest & LC).Value = dataMarker
Range(columnToTest & LC).Offset(0, 1) _
= dataMarker
Range(columnToTest & LC).Offset(0, 2) _
= dataMarker
currentValue = Range(columnToTest & LC). _
Offset(-1, 0)
End If
Next
End Sub


"James" wrote:

I have a file that needs to go into my application but all the data is
grouped together. My application will recognize it is moved on to a new data
set when it see's -999.99, and the application is smart enough to say hey
this an entirely new data different from the data above it.

Here is generic example of what I have:

23 95.6 10003
23 80 650
23 2 897
49 600 10000
49 23 11

Here is what I would like to have:
23 95.6 10003
23 80 650
23 2 897
-999.99 -999.99 -999.99
49 600 10000
49 23 11

So whenever the value changes in column A to a new value I need it to insert
a new row of data that have the values of -999.99.

Thank you for the help.