View Full Version : Solved: Variables in String Issue
Mcygee
02-23-2010, 12:06 PM
Hello everybody. Thanks for taking the time to look at my issue.
Going to use just examples to get my general problem across. The problem is as such...
I have a public variable lets call "PublicVariable" that I'm using across two subs.
Dim PublicVariable as String
In Sub1 I have...
PublicVariable = """=(35*(5-H"" & LastRow2 & ""))-H"" & LastRow1"
LastRow2 and LastRow1 being variables from a previous calculation for the last row and the last row + 1. But that doesn't really matter. They are just variables.
In Sub2 I have...
Cells(2, 10).Formula = PublicVariable
What happens is, instead of Cells(2,10) getting this for a formula....
=(35*(5-H2))-H1
it gets this for a formula...
"=(35*(5-H" & LastRow2 & "))-H" & LastRow1
The exact text without the variables and such calculated.
I've also tried this...
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
but then all I get in Cells(2,10) is...
=(35*(5-H
Now if I manually plugged in the string into Sub2 like this...
Cells(2, 10).Formula = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
then it works.
So I need to find a way to put my string into a variable form that allows it to be used later and properly come together to create the PROPER final result in Cells(2,10) like this...
=(35*(5-H2))-H1
Do I need to dim PublicVariable as something besides string?
Am I being clear? Let me know if I need to clarify anything.
Thanks!
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
First part: =(35*(5-H
Second Part: LastRow2
Third part: ))-H
Fourth Part: LastRow1
If LastRow 2 = 5
If LastRow1 = 10
Equivalent to (35*(5-H5)) - H10
It is sometimes helpful to format your code like this
Cells(2, 10).Formula = "=(35*(5-H" _
& LastRow2 _
& "))-H" _
& LastRow1
So you can quickly see that only the strings are enclosed in quotes.
This sample code worked on my system:
Sub sample()
Dim LastRow1 As Integer, LastRow2 As Integer, PublicVariable As String
LastRow2 = 10
LastRow1 = 2
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
Cells(2, 10).Formula = PublicVariable
End Sub
Mcygee
02-23-2010, 03:54 PM
Thanks for the replies!
I found the underlying issue after seeing what you guys posted. JTL's example works because everything is in one Sub. I've attached an example of mine. It is basically just an edited version of JTL's example but changed to how my real worksheet is set up. The problem with mine this...
In Sub1 I have...
Public PublicVariable As String
Sub sample1()
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
sample2
End Sub
In Sub2 I have...
Sub sample2()
Dim LastRow2 As Integer
Dim LastRow1 As Integer
LastRow2 = 10
LastRow1 = 2
Cells(2, 10).Formula = PublicVariable
End Sub
This doesn't work because it tries to find what LastRow1 and LastRow2 are in Sub1 when I specified PublicVariable =
Now if I change Sub1 to this...
Sub sample1()
Dim LastRow2 As Integer
Dim LastRow1 As Integer
LastRow2 = 10
LastRow1 = 2
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
sample2
End Sub
And Sub2 to this...
Sub sample2()
Cells(2, 10).Formula = PublicVariable
End Sub
It all works fine.
So I guess what I need to do is find out if there is a way to move PublicVariable into Sub2 exactly as it is, and not try to calculate what LastRow1 and LastRow2 is before they are even given a value.
I could work around it by doing a loop inside Sub2 and have it change PublicVariable each time it loops to what I need. But what I was after is somewhat of a more organized approach and have Sub1 control it all like this....
Public PublicVariable As String
Sub sample1()
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
sample2
PublicVariable = "=(10*(3-B" & LastRow2 & "))-B" & LastRow1
sample2
PublicVariable = "=(5*(4-C" & LastRow2 & "))-C" & LastRow1
sample2
End Sub
Basically changing my variable in Sub1 and running Sub2 afterward each time to fill in data in a new location.
So anybody have any ideas on moving the formula...
"=(35*(5-H" & LastRow2 & "))-H" & LastRow1
but make it calculate the whole thing only when I call PublicVariable in Sub2 not in Sub1?
I guess the quickest way to do this is to declare LastRow1 and LastRow2 as public variables. Like this:
Public PublicVariable As String
Public LastRow2 As Integer
Public LastRow1 As Integer
Sub sample1()
LastRow2 = 10
LastRow1 = 2
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
sample2
End Sub
Sub sample2()
Cells(2, 10).Formula = PublicVariable
End Sub
Then you can simple reassign LastRow1 and LastRow2 either in sub1 or sub2.
Mcygee
02-24-2010, 09:04 AM
Well unfortunately using that method you must keep LastRow1 and LastRow2 in Sub 1 for it to work because they have to be given a value before PublicVariable is given it's string.
Here is the workaround I'm going to use for now unless somebody knows a better way. Marking this thread solved, but add more info if you'd like.
I'm going to add another sub that will create the PublicVariable string. In my example it looks pointless because there is only one PublicVariable, but in my real worksheet I'll use an if then/else if to change it to different strings based on what another variable is. Sample is attached but here it is as well...
Sample1 Macro
Public PublicVariable As String
Public LastRow2 As Integer
Public lastrow1 As Integer
Sub sample1()
sample2
End Sub
Sample2 Macro
Sub sample2()
LastRow2 = 10
lastrow1 = 2
sample3
Cells(2, 10).Formula = PublicVariable
End Sub
Sample3 Macro
Sub sample3()
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & lastrow1
End Sub
Also just to be clear I'm not moving LastRow1 or LastRow2 to Sample 1 because in my worksheet it is calculated each time differently in Sample2 and has to remain there for it all to work.
Bob Phillips
02-24-2010, 09:22 AM
Sub sample1()
Dim LastRow1 as long
Dim LastRow2 as long
LastRow2 = 10
LastRow1 = 2
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
call sample2 (PublicVariable)
End Sub
Sub sample2(ByVal MyFormula as string)
Cells(2, 10).Formula = MyFormula
End Sub
Mcygee
02-24-2010, 10:30 AM
That does work XLD, but unfortunately in my real worksheet LastRow1 and LastRow2 must be calculated in Sample2. It's a variable that you can't calculate until some data is pulled in a sorted, so I can't just move it into Sample1.
Only workaround I see right now is to have a separate module or sub that Sample2 can call to find out what PublicVariable is. Not a big deal, but it would be nice just to tell Sample2 what string to use without calculating the LastRow1 and LastRow2 variables right then.
Sounds like a case for Get, Set, and Let procedures.
Bob Phillips
02-24-2010, 02:16 PM
Sub sample1()
Dim LastRow1 As Long
Dim LastRow2 As Long
LastRow2 = 10
LastRow1 = 2
PublicVariable = 15
MsgBox PublicVariable
Call sample2 (PublicVariable)
MsgBox PublicVariable
End Sub
Sub sample2(ByRef myValue As Double)
myValue = 17
End Sub
Mcygee,
I really don't understand.
Do LastRows 1 and 2 have different values in Subs 1 and 2?
Or:
Does PublicVariable need to have different formulas in the two subs?
SamT
I think this is a case of what comes first, the chicken or the egg :)
If I understand correctly, PublicVariable needs LastRow1 and LastRow2 to to complete the string in sub1. However, the value of LastRow1 and LastRow2 is derived from sub2, which comes after the fact. Well, see if this one works for you.
Public PublicVariable1 As String, PublicVariable2 As String
Sub sample1()
PublicVariable1 = "=(35*(5-H"
PublicVariable2 = "))-H"
Call sample2
End Sub
Sub sample2()
Dim LastRow2 As Integer
Dim LastRow1 As Integer
' Get actual value
LastRow2 = 10
LastRow1 = 2
Cells(2, 10).Formula = PublicVariable1 & LastRow2 & PublicVariable2 & LastRow1
End Sub
Mcygee
02-26-2010, 07:50 AM
JTL you're a genius.
That should work fine for all the different equations I need.
Thank you everybody who took at shot at this. You all had good ideas, but unfortunately my issue was a little confusing and hard to explain so I know it was hard to figure out exactly what I was asking for.
Great people on these forums.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.