i made a couple of little changes, but i've never worked with parsing
out a string & i can't figure it out today............
maybe somebody else can help with that part??
:)
susan
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 myNewRow As Long
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.
Do Until StartRow = 20000 Or TargetColumn.Value = ""
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
On Apr 2, 3:31 pm, "Susan" wrote:
this is what i've come up with so far.
the only part i have to research is parsing out the formula string
to get the actual row number of the formula entered to equal
startrow............
susan
xxxxxxxxxxxxxxx
Option Explicit
Sub expanding_numbers()
Dim StartRow As Long
Dim myFormula As String
Dim myColumn As Range
Dim ws As Worksheet
Dim rRow As Range
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")
'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.
Do Until StartRow = 20000
For Each rRow In myColumn
'myFormula = parsed string with correct startrow
StartRow = myFormula
StartRow = StartRow + 16
Next rRow
Loop
End Sub
xxxxxxxxxxxxxxxxxxxxx
On Apr 2, 3:03 pm, "Susan" wrote:
i don't think (AFAIK) you can do it with a formula. you'd have to
write a macro to insert that formula (with the correct row number)
every 16 rows for as far down as you needed.
hth
susan
On Apr 2, 2:46 pm, "TripleX" wrote:
I need to expand these cells, (it's always +16), how can i do it?
=IF('01'!B1=1444093;"Standard";"Turbo")
=IF('01'!B17=1444093;"Standard";"Turbo")
=IF('01'!B33=1444093;"Standard";"Turbo")
, when i expand it, it should continue like this:
=IF('01'!B49=1444093;"Standard";"Turbo")
=IF('01'!B65=1444093;"Standard";"Turbo")
and so on..- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -