vari = Right(myFormula, Len(myFormula) - 1)
variable = Left(vari, InStr(vari, "!") + 1)
variable2 = Right(vari, Len(vari) - InStr(vari, "="))
variable3 = "=" & variable & "" & StartRow & "=" & variable2

variable3 will be the new formula

try this, sorry, wasn't picking up what you were putting down....

Brent


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

"Susan" wrote:

the macro is working with brent's great help!

now, how do i make myFormula (entered in input box),

=IF('01'!B1=1444093,"Standard","Turbo")

start on the row of StartRow and then increment +16 the same as
StartRow?
that's what i meant by "putting the string back together" - which i
now understand is still intact in the original variable. so i guess
the question becomes how do i change that string's row # to match
StartRow?

so if the user chose row 5 to start on, then the first formula should
be

=IF('01'!B5=1444093;"Standard";"Turbo")

and the 2nd formula, 16 rows later, should be

=IF('01'!B21=1444093;"Standard";"Turbo")

etc.

any ideas?
thanks!
susan