Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
ok thank you! i will work on it.
thanks for explaining :) susan On Apr 3, 11:28 am, Brent wrote: 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 -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
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 On Apr 3, 2:26 pm, Brent wrote: 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- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to parse string into array or variable?
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 On Apr 3, 2:31 pm, "Susan" wrote: 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 On Apr 3, 2:26 pm, Brent wrote: 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parse this string | Excel Discussion (Misc queries) | |||
Passing string as array variable (Pivot VBA) | Excel Discussion (Misc queries) | |||
Parse string | Excel Programming | |||
Q: parse string | Excel Discussion (Misc queries) | |||
VBA - Convert my variable range array to single cell string | Excel Programming |