I have data sets like 1-2, 2-3, 3-5, 6-10. I want to extract 1-7 step
interval, this should be (1-2)=1+(2-3)=1+(3-5)=2+(6-7)=1 will be a
total of 5. This way, I have bulk data and want to extract many step
intervals. Any short cut method of macro or good formula will be very
useful to me. Thanks in advance.
- Panneer

I assummed the input was a sttring. Here is the code

Sub getsteps()

Dim inputstring As String
Dim step As String
Dim Maxstep As Integer
Maxstep = 7

inputstring = "1-2, 2-3, 3-5, 6-10"

inputstring = Trim(inputstring)

step = 0
Do While InStr(inputstring, ",") 0

step = Left(inputstring, InStr(inputstring, ",") - 1)
inputstring = Mid(inputstring, InStr(inputstring, ",") + 1)
pulse = pulse + getpulse(step, Maxstep)
Loop
pulse = pulse + getpulse(inputstring, Maxstep)

End Sub
Function getpulse(step As String, Maxstep As Integer)

getpulse = 0
minusposition = InStr(step, "-")
getlowernumber = Val(Left(step, minusposition - 1))
getuppernumber = Val(Val(Mid(step, minusposition + 1)))

If getlowernumber < Maxstep Then

If getuppernumber Maxstep Then

getuppernumber = Maxstep
End If

getpulse = getuppernumber - getlowernumber
End If

End Function


"Tamil" wrote:

I have data sets like 1-2, 2-3, 3-5, 6-10. I want to extract 1-7 step
interval, this should be (1-2)=1+(2-3)=1+(3-5)=2+(6-7)=1 will be a
total of 5. This way, I have bulk data and want to extract many step
intervals. Any short cut method of macro or good formula will be very
useful to me. Thanks in advance.
- Panneer

Thanks Joel.
The data is contained in excel as numbers in A1:B4 (A1=1, B1=2, A2=2,
B2=3, etc).
The step 1-7, I put in A5:B5 (A5=1, B5=7).

I dont have knowledge on macro coding. Can you please modify the code?

Use function below. call with

=GetStep(A1:B5)
Will work with any range of cells 5 rows x 2 columns

If you have variable size ranges I would recommend doing the following and
then I would modify the code again.

A B C D E
Row 1 1 1 2 3 6

Row 2 7 2 3 5 10


Where column A is the min and max limits, and data is in columns rather than
rows. You can continue adding more steps in columns f and up.



This code works with the way you asked for the code to be written.

Function GetStep(Target As Range)

Dim MinStep As Integer
Dim MaxStep As Integer

MinStep = Target(5, 1)
MaxStep = Target(5, 2)

GetStep = 0
For RowCount = 1 To 4

LowerNumber = Target(RowCount, 1)
UpperNumber = Target(RowCount, 2)


If LowerNumber < MinStep Then

LowerNumber = MinStep

End If

If UpperNumber MaxStep Then

UpperNumber = MaxStep

End If

GetStep = GetStep + (UpperNumber - LowerNumber)

Next RowCount

End Function

"Joel" wrote:

I assummed the input was a sttring. Here is the code

Sub getsteps()

Dim inputstring As String
Dim step As String
Dim Maxstep As Integer
Maxstep = 7

inputstring = "1-2, 2-3, 3-5, 6-10"

inputstring = Trim(inputstring)

step = 0
Do While InStr(inputstring, ",") 0

step = Left(inputstring, InStr(inputstring, ",") - 1)
inputstring = Mid(inputstring, InStr(inputstring, ",") + 1)
pulse = pulse + getpulse(step, Maxstep)
Loop
pulse = pulse + getpulse(inputstring, Maxstep)

End Sub
Function getpulse(step As String, Maxstep As Integer)

getpulse = 0
minusposition = InStr(step, "-")
getlowernumber = Val(Left(step, minusposition - 1))
getuppernumber = Val(Val(Mid(step, minusposition + 1)))

If getlowernumber < Maxstep Then

If getuppernumber Maxstep Then

getuppernumber = Maxstep
End If

getpulse = getuppernumber - getlowernumber
End If

End Function


"Tamil" wrote:

I have data sets like 1-2, 2-3, 3-5, 6-10. I want to extract 1-7 step
interval, this should be (1-2)=1+(2-3)=1+(3-5)=2+(6-7)=1 will be a
total of 5. This way, I have bulk data and want to extract many step
intervals. Any short cut method of macro or good formula will be very
useful to me. Thanks in advance.
- Panneer

Thanks Joel,

It works great.
Now I need to expand the function to cover more work.
I have many numbers from to in A1: B300

I have many steps from to in A302: B310

I have to call the function Getstep manytimes or any shortcut?

Thanks

You don't have to change the function unless you have to add more steps. As
I recommended put the limits of the step as the first entry, then it is easy
for me to change the range of the function to work with variable numbers of
steps.

The fnction works like any other functon that you can copy it to other cells.

"Tamil" wrote:

Thanks Joel,

It works great.
Now I need to expand the function to cover more work.
I have many numbers from to in A1: B300

I have many steps from to in A302: B310

I have to call the function Getstep manytimes or any shortcut?

Thanks

Thanks Joel,
Got the point.
Saved much time involved in manual work.