S

#### Susan

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