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




.

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




.