Macro Question

[cranen]

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

 

[Rick Rothstein]

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)?

 

[cranen]

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.

 

[J_Knowles]

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,

 

[J_Knowles]

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

 

[cranen]

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]

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.

 

[cranen]

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(Q3,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(Q3,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,(Q3+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]

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