VBA to insert a row with -999.99

J

James

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.
 
J

JLatham

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
 
J

James

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
J

JLatham

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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

James

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

JLatham said:
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 said:
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 said:
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


:

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.
Click to expand...
Click to expand...
Click to expand...
 
J

JLatham

Glad to hear that the fix worked. Enjoy.

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

JLatham said:
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 said:
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

:

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


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to update/change cell references in a formula 2
*Issue* Concatenate dates in a large range of cells in a row. 5
macro adding rows 8
How to insert row on Col A field change 2
Help!! find two number recurring numbers in row?? 1
Macro match function problem 12
Reverse Index Match 2
Do I use a formula or a macro to copy cells? 3

Top