Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
I think I need a macro for the following situation. I have a table (Curve
Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
I'm having a little trouble following where your existing data is located
and where it is supposed to end up at. I know you are talking about baseline, centerline or survey line stationing (I was a road design engineer for over 30 years), but I am not sure what you have now in your worksheet and where you are trying to go with it. It sounds like you have your existing data (five columns worth of it per station) listed row by row and I'm thinking you are looking to insert the even 50-foot station in between and around them... is that correct? Also, which columns is your data in (Columns A through E or some other arrangement) and what row is your first piece of data in (Row 2 after a header row)? -- Rick (MVP - Excel) "cranen" wrote in message ... I think I need a macro for the following situation. I have a table (Curve Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
You are correct. It is roadway stationing. I have already written a formula
that allows me to have the 50 foot stationing before, after, and between. I just need to clean up the data so that it can be used elsewhere. I enter into 5 columns (TS, SC, PI, CS, and ST)~(2083.25, 2383.25, 2448.23, 2511.02, and 2811.02). These rows of data are transformed into columns with added stationing. As I said below, I want the unwanted stationing gone, and all of the stationing combined into one column. The reason for the unwanted stationing is because I think I will need it in the future depending on where my entry data lands. Does this make sense? Can you help? Do I need to give more info? Thanks. "Rick Rothstein" wrote: I'm having a little trouble following where your existing data is located and where it is supposed to end up at. I know you are talking about baseline, centerline or survey line stationing (I was a road design engineer for over 30 years), but I am not sure what you have now in your worksheet and where you are trying to go with it. It sounds like you have your existing data (five columns worth of it per station) listed row by row and I'm thinking you are looking to insert the even 50-foot station in between and around them... is that correct? Also, which columns is your data in (Columns A through E or some other arrangement) and what row is your first piece of data in (Row 2 after a header row)? -- Rick (MVP - Excel) "cranen" wrote in message ... I think I need a macro for the following situation. I have a table (Curve Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
Sub FindCurveData()
' curve data in A2:A40 Dim Find_Range As Range Dim firstAddress As Range Dim lastAddress As Range Dim i As Long Dim y As Integer For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Application.ExecuteExcel4Macro("Mod(" & Cells(i, 1) & ", 50)") < 0 Then If Find_Range Is Nothing Then Set Find_Range = Cells(i, 1) Set firstAddress = Find_Range y = 1 Else y = y + 1 If y = 5 Then Set lastAddress = Cells(i, 1) Set Find_Range = Union(Cells(i, 1), Find_Range) End If End If Next i Find_Range.Select 'the 5 data point with non +50 stations) Range(firstAddress.Offset(-2, 0), lastAddress.Offset(2, 0)).Select 'copy data from Column A to Column F Selection.Copy Selection.Offset(0, 5).PasteSpecial Paste:=xlValues 'optional xlPasteAll Application.CutCopyMode = False End Sub HTH, -- Data Hog "cranen" wrote: You are correct. It is roadway stationing. I have already written a formula that allows me to have the 50 foot stationing before, after, and between. I just need to clean up the data so that it can be used elsewhere. I enter into 5 columns (TS, SC, PI, CS, and ST)~(2083.25, 2383.25, 2448.23, 2511.02, and 2811.02). These rows of data are transformed into columns with added stationing. As I said below, I want the unwanted stationing gone, and all of the stationing combined into one column. The reason for the unwanted stationing is because I think I will need it in the future depending on where my entry data lands. Does this make sense? Can you help? Do I need to give more info? Thanks. "Rick Rothstein" wrote: I'm having a little trouble following where your existing data is located and where it is supposed to end up at. I know you are talking about baseline, centerline or survey line stationing (I was a road design engineer for over 30 years), but I am not sure what you have now in your worksheet and where you are trying to go with it. It sounds like you have your existing data (five columns worth of it per station) listed row by row and I'm thinking you are looking to insert the even 50-foot station in between and around them... is that correct? Also, which columns is your data in (Columns A through E or some other arrangement) and what row is your first piece of data in (Row 2 after a header row)? -- Rick (MVP - Excel) "cranen" wrote in message ... I think I need a macro for the following situation. I have a table (Curve Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
Clarification about one line of code. This code should be on one line.
forum word wrapped this code: If Application.ExecuteExcel4Macro("Mod(" & Cells(i, 1) & ", 50)") < 0 Then should be on one line: If Application.ExecuteExcel4Macro("Mod(" & Cells(i, 1) & ", 50)") < 0 Then -- Data Hog "J_Knowles" wrote: Sub FindCurveData() ' curve data in A2:A40 Dim Find_Range As Range Dim firstAddress As Range Dim lastAddress As Range Dim i As Long Dim y As Integer For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Application.ExecuteExcel4Macro("Mod(" & Cells(i, 1) & ", 50)") < 0 Then If Find_Range Is Nothing Then Set Find_Range = Cells(i, 1) Set firstAddress = Find_Range y = 1 Else y = y + 1 If y = 5 Then Set lastAddress = Cells(i, 1) Set Find_Range = Union(Cells(i, 1), Find_Range) End If End If Next i Find_Range.Select 'the 5 data point with non +50 stations) Range(firstAddress.Offset(-2, 0), lastAddress.Offset(2, 0)).Select 'copy data from Column A to Column F Selection.Copy Selection.Offset(0, 5).PasteSpecial Paste:=xlValues 'optional xlPasteAll Application.CutCopyMode = False End Sub HTH, -- Data Hog "cranen" wrote: You are correct. It is roadway stationing. I have already written a formula that allows me to have the 50 foot stationing before, after, and between. I just need to clean up the data so that it can be used elsewhere. I enter into 5 columns (TS, SC, PI, CS, and ST)~(2083.25, 2383.25, 2448.23, 2511.02, and 2811.02). These rows of data are transformed into columns with added stationing. As I said below, I want the unwanted stationing gone, and all of the stationing combined into one column. The reason for the unwanted stationing is because I think I will need it in the future depending on where my entry data lands. Does this make sense? Can you help? Do I need to give more info? Thanks. "Rick Rothstein" wrote: I'm having a little trouble following where your existing data is located and where it is supposed to end up at. I know you are talking about baseline, centerline or survey line stationing (I was a road design engineer for over 30 years), but I am not sure what you have now in your worksheet and where you are trying to go with it. It sounds like you have your existing data (five columns worth of it per station) listed row by row and I'm thinking you are looking to insert the even 50-foot station in between and around them... is that correct? Also, which columns is your data in (Columns A through E or some other arrangement) and what row is your first piece of data in (Row 2 after a header row)? -- Rick (MVP - Excel) "cranen" wrote in message ... I think I need a macro for the following situation. I have a table (Curve Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
My data is in Column T:AM. I tried just putting in "T" for "A" in the macro,
and running the original. I am getting a "Run-time error '1004'". Is there something I am missing or messing up? Thanks for your help. "J_Knowles" wrote: Clarification about one line of code. This code should be on one line. forum word wrapped this code: If Application.ExecuteExcel4Macro("Mod(" & Cells(i, 1) & ", 50)") < 0 Then should be on one line: If Application.ExecuteExcel4Macro("Mod(" & Cells(i, 1) & ", 50)") < 0 Then -- Data Hog "J_Knowles" wrote: Sub FindCurveData() ' curve data in A2:A40 Dim Find_Range As Range Dim firstAddress As Range Dim lastAddress As Range Dim i As Long Dim y As Integer For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row If Application.ExecuteExcel4Macro("Mod(" & Cells(i, 1) & ", 50)") < 0 Then If Find_Range Is Nothing Then Set Find_Range = Cells(i, 1) Set firstAddress = Find_Range y = 1 Else y = y + 1 If y = 5 Then Set lastAddress = Cells(i, 1) Set Find_Range = Union(Cells(i, 1), Find_Range) End If End If Next i Find_Range.Select 'the 5 data point with non +50 stations) Range(firstAddress.Offset(-2, 0), lastAddress.Offset(2, 0)).Select 'copy data from Column A to Column F Selection.Copy Selection.Offset(0, 5).PasteSpecial Paste:=xlValues 'optional xlPasteAll Application.CutCopyMode = False End Sub HTH, -- Data Hog "cranen" wrote: You are correct. It is roadway stationing. I have already written a formula that allows me to have the 50 foot stationing before, after, and between. I just need to clean up the data so that it can be used elsewhere. I enter into 5 columns (TS, SC, PI, CS, and ST)~(2083.25, 2383.25, 2448.23, 2511.02, and 2811.02). These rows of data are transformed into columns with added stationing. As I said below, I want the unwanted stationing gone, and all of the stationing combined into one column. The reason for the unwanted stationing is because I think I will need it in the future depending on where my entry data lands. Does this make sense? Can you help? Do I need to give more info? Thanks. "Rick Rothstein" wrote: I'm having a little trouble following where your existing data is located and where it is supposed to end up at. I know you are talking about baseline, centerline or survey line stationing (I was a road design engineer for over 30 years), but I am not sure what you have now in your worksheet and where you are trying to go with it. It sounds like you have your existing data (five columns worth of it per station) listed row by row and I'm thinking you are looking to insert the even 50-foot station in between and around them... is that correct? Also, which columns is your data in (Columns A through E or some other arrangement) and what row is your first piece of data in (Row 2 after a header row)? -- Rick (MVP - Excel) "cranen" wrote in message ... I think I need a macro for the following situation. I have a table (Curve Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
My code was written for the curve values to be in one column A2:A40 (39 rows
x 1 column). It is possible to use this scheme if you can use only one row say T2:BF2 (39 columns x 1 row). It appears your data is in a range like T2:AM3 (2 rows x 20 columns). In that case, this code will not work. Let me know if you can modify your curve data to be in one column or one row and the exact cell range where your curve data is located on your revised spreadsheet. -- Data Hog "cranen" wrote: I think I need a macro for the following situation. I have a table (Curve Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
Would you let me send you a file? If not, I will try to explain it here. I
have a table that takes up Columns A:M. This table consists of field data (The first two rows are headings). The only columns that will be used for adding the 50 foot stationing are Columns C, D, E, F, and G (Rows 3 through 16 actually have data but there is a possibility of more in the future). I have written a formula that adds the 50 foot stationing (It places each row of field data with added stationing into a column starting at Q) (Con - I have to drag it out so far because I never know where the field data will fall), but you might be able to use a macro to get the same results without the excess data. The formula I use in the first cell is: =ROUNDDOWN(C3,-2) the formulas in the cells following cell 1: =IF(AND(($C$3-Q3)<50,($C$3-Q3)0),$C$3,IF(AND(Q3=$C$3,(Q3+50)<$D$3),CEILING(Q 3,50),IF(AND(($D$3-Q3)<50,($D$3-Q3)0),$D$3,IF(AND(Q3=$D$3, (Q3+50)<$E$3), CEILING(Q3,50),IF(AND(($E$3-Q3)<50,($E$3-Q3)0),$E$3,IF(AND(Q3=$E$3,(Q3+50)<$F$3),CEILING(Q 3,50),IF(AND(($F$3-Q3)<50,($F$3-Q3)0),$F$3,IF(AND(Q3=$F$3, (Q3+50)<$G$3),CEILING(Q3,50),IF(AND(($G$3-Q3)<50,($G$3-Q3)0),$G$3,IF(Q3=$G$3,CEILING(Q3,50),MROUND(Q3,(Q 3+50)))))))))))) The 39 cells of data is created by the formula. Not all rows of field data will actually produce 39 cells of useful data. Was the code written to do away with the formula? Thanks for all the help and time. Let me know what you think. "J_Knowles" wrote: My code was written for the curve values to be in one column A2:A40 (39 rows x 1 column). It is possible to use this scheme if you can use only one row say T2:BF2 (39 columns x 1 row). It appears your data is in a range like T2:AM3 (2 rows x 20 columns). In that case, this code will not work. Let me know if you can modify your curve data to be in one column or one row and the exact cell range where your curve data is located on your revised spreadsheet. -- Data Hog "cranen" wrote: I think I need a macro for the following situation. I have a table (Curve Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Question
Send me the file. Click on J_Knowles in the header of this message to get my
email address (remove the NO.SPAMs). Send me the spreadsheet with the original post data points (2000.00, 2050.00, 2083.25....) -- Data Hog "cranen" wrote: Would you let me send you a file? If not, I will try to explain it here. I have a table that takes up Columns A:M. This table consists of field data (The first two rows are headings). The only columns that will be used for adding the 50 foot stationing are Columns C, D, E, F, and G (Rows 3 through 16 actually have data but there is a possibility of more in the future). I have written a formula that adds the 50 foot stationing (It places each row of field data with added stationing into a column starting at Q) (Con - I have to drag it out so far because I never know where the field data will fall), but you might be able to use a macro to get the same results without the excess data. The formula I use in the first cell is: =ROUNDDOWN(C3,-2) the formulas in the cells following cell 1: =IF(AND(($C$3-Q3)<50,($C$3-Q3)0),$C$3,IF(AND(Q3=$C$3,(Q3+50)<$D$3),CEILING(Q 3,50),IF(AND(($D$3-Q3)<50,($D$3-Q3)0),$D$3,IF(AND(Q3=$D$3, (Q3+50)<$E$3), CEILING(Q3,50),IF(AND(($E$3-Q3)<50,($E$3-Q3)0),$E$3,IF(AND(Q3=$E$3,(Q3+50)<$F$3),CEILING(Q 3,50),IF(AND(($F$3-Q3)<50,($F$3-Q3)0),$F$3,IF(AND(Q3=$F$3, (Q3+50)<$G$3),CEILING(Q3,50),IF(AND(($G$3-Q3)<50,($G$3-Q3)0),$G$3,IF(Q3=$G$3,CEILING(Q3,50),MROUND(Q3,(Q 3+50)))))))))))) The 39 cells of data is created by the formula. Not all rows of field data will actually produce 39 cells of useful data. Was the code written to do away with the formula? Thanks for all the help and time. Let me know what you think. "J_Knowles" wrote: My code was written for the curve values to be in one column A2:A40 (39 rows x 1 column). It is possible to use this scheme if you can use only one row say T2:BF2 (39 columns x 1 row). It appears your data is in a range like T2:AM3 (2 rows x 20 columns). In that case, this code will not work. Let me know if you can modify your curve data to be in one column or one row and the exact cell range where your curve data is located on your revised spreadsheet. -- Data Hog "cranen" wrote: I think I need a macro for the following situation. I have a table (Curve Data) where data is entered across 5 columns. The data in the table is from the field, and in order to do further calculations, it needs data added to it. So, I have a formula that rounds down the lowest value to the nearest multiple of 50 (depending on what that value is it might create two values below it) and creates a new low value (ex. 2083.25 ~ 2050.00), adds multiples of 50 from the lowest value to the highest value (ex. 2050.00, 2083.25, 2100.00, 2150.00, .... 2383.25, 2400.00, etc.), and it rounds the highest value to the nearest multiple of 50 and creates a new high value. Well, I end up with a lot of excess data as you can see below (Mainly because I never know where the last field data value will land - I have extended the formula to a row at which I think the last field data value will never cross). 2000.00 2050.00 2083.25 2100.00 2150.00 2200.00 2250.00 2300.00 2350.00 2383.25 2400.00 2448.23 2450.00 2500.00 2511.02 2550.00 2600.00 2650.00 2700.00 2750.00 2800.00 2811.02 2850.00 2900.00 2950.00 3000.00 3050.00 3100.00 3150.00 3200.00 3250.00 3300.00 3350.00 3400.00 3450.00 3500.00 3550.00 3600.00 3650.00 I want to be pointed in the right direction on how to filter this data. I need a range of data that follows the rules below to be copied somewhere else: 1. The value that comes right before the first field data value (2050.00). 2. The value that comes right after the last field data value (2850.00). 3. The values that come between values 2050.00 and 2850.00 Thank you so much for your help. If I am missing some info please let me know |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
Macro question | Excel Programming | |||
Macro Question | Excel Programming | |||
Macro Question | New Users to Excel | |||
Macro Question | Excel Programming |