Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to insert a row with -999.99
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to insert a row with -999.99
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to insert a row with -999.99
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to insert a row with -999.99
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to insert a row with -999.99
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA to insert a row with -999.99
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot insert worksheet in exel - not available in insert menu | Excel Worksheet Functions | |||
insert row / insert column command buttons | Excel Worksheet Functions | |||
How can I insert a date with an icon (calendar) insert | Excel Discussion (Misc queries) | |||
Can I auto insert a worksheet when I insert a value in a cell. | Excel Worksheet Functions | |||
Insert Next? Or insert a variable number of records...how? | Excel Discussion (Misc queries) |