how to parse string into array or variable?

[Susan]

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

 

[Brent]

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]

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

 

[Brent]

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]

ok thank you! i will work on it.
thanks for explaining

susan

 

[Susan]

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

 

[Brent]

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]

not your fault at all! i wasn't asking the right thing, because i
didn't understand what i was trying to do! once i started working
with your code & figuring out what that was doing, then i could more
understandably ask for what i needed.
going to go try it out!
susan

 

[Susan]

thanks a lot, brent! it works exactly as i wanted it to!
final code:

Option Explicit

Sub expanding_numbers()

Dim StartRow As Long
Dim myFormula As String
Dim myColumn As Range
Dim TargetColumn As Range
Dim ws2 As Worksheet
Dim ws1 As Worksheet
Dim rRow As Range
Dim theEnd As String
Dim rRange As Range
Dim myRange As Range
Dim vari As Variant
Dim variable As Variant
Dim variable2 As Variant
Dim variable3 As Variant

Set ws2 = ActiveWorkbook.Worksheets(2)
Set ws1 = ActiveWorkbook.Worksheets(1)
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 = ws2.Range("d:d")
Set TargetColumn = ws1.Range("b:b")

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

Do
For Each rRow In myColumn
variable3 = "=" & variable & "" & StartRow & "=" & variable2
Set myRange = ws2.Range("d" & StartRow) 'where it's going
theEnd = "b" & StartRow
Set rRange = ws1.Range(theEnd) 'what you're looking at
If IsEmpty(rRange) Then
MsgBox "All done!"
Exit Sub
End If
myRange = variable3
StartRow = StartRow + 16
Next rRow
Loop

End Sub

it seems a little convoluted & perhaps could have been handled more
efficiently (as usual, for me!), but it works.
thanks a lot for helping me figure this out.
susan