basically Instr will search for whatever character or set of characters you
want and will return, reading from the left to right, what number the
character is at.

Left will return the number of characters, reading from the left to right,
that you specify

Right will return the number of characters, reading from right to left, that
you specify

This does not alter the original source, that is why you are required to
assign Right and Left to a variable.

From the code it looks like you can just do
vari=Left(myFormula, InStr(myFormula, "=") - 1)

You shouldn't need to put it back together, since it is stored as myFormula,
also all you will have left is the column and row references. For that you
can just concatenate like

original_was= variable3 & "" & variable4


Brent


"Susan" wrote:

brent - hmmmmmmmmmmm......
the formula isn't in a cell to start with, it's in the input box. i
can't hard code it to be "B2" because it will change per what the user
inputs.
so vari = inputbox string??

and how would you put it all back together once you changed variable4?
boy these strings mess up my head badly - i can't get my mind around
it. but i appreciate your help - i can study it & hope it seeps its
way in there!
:)
susan




On Apr 3, 10:28 am, Brent wrote:
vari = Right(Range("B2").Formula, Len(Range("b2").Formula) - 1)
gets rid of first =

variable = Left(vari, InStr(vari, "=") - 1)
gives you IF('01'!B1

variable2 = Right(variable, Len(variable) - InStr(variable, "!"))
gives you B1

then use
variable3=Left(variable2, 1)
to get B

and you can do variable4=right(variable2, len(variable2)-1)
to get the number, even if it is two digits

hope this helps



"Susan" wrote:
i was trying to help triplex with this solution, but i couldn't figure
out the parsing - can somebody help me with this part? (he/she isn't
going to use it, but i want to learn it anyway).

the formula that somebody would be entering would be
=IF('01'!B1=1444093,"Standard","Turbo")

so what i need to get out of that is the row number as either a
variable or an array variable, that i could make equal to StartRow.
but i need to cover the fact that the row number may at some point be
a double digit.

but then i need to be able to put it back together again:
myFormula = 1st part & StartRow & last part.

can somebody help me please? i have NO idea if i should use RIGHT,
LEFT & MID for this, or InStr, or Split or what?
thanks!
susan
xxxxxxxxxxxxxxxxxxxxxxxxxxx
Option Explicit

Sub expanding_numbers()

Dim StartRow As Long
Dim myFormula As String
Dim myColumn As Range
Dim TargetColumn As Range
Dim ws As Worksheet
Dim rRow As Range
Dim theEnd As String
Dim rRange As Range
Dim sArray() As String

Set ws = ActiveWorkbook.ActiveSheet
StartRow = InputBox("What row would you like this cycle to start on?")
myFormula = InputBox("Please enter the formula you would like" _
& " distributed every 16 rows.")

Set myColumn = ws.Range("d:d")
Set TargetColumn = ws.Range("b:b")

'now you have to pull apart the formula they entered & find
'the first number after ! and change that to startrow's
'value & each time change myformula to equal startrow.

theEnd = "b" & StartRow
Set rRange = ws.Range(theEnd)

Do Until StartRow = 20000 Or rRange = ""
For Each rRow In myColumn
' sArray = Split(myFormula, "!")
' Then sArray(0) = "text up until /+1"
' sArray(1) = "next 2 numbers"
' sArray(2) = "rest of formula after row numbers"
' sarray(1) = startrow
'myformula = sarray(0) & startrow & sarray(2)
'parsed out string with startrow
StartRow = myFormula
StartRow = StartRow + 16
Next rRow
Loop

End Sub
xxxxxxxxxxxxxxxxxxxxxxxx- Hide quoted text -

- Show quoted text -