I need help creating a function.

The goal is to parse formulas from cells A1 to A5 and find the values
of each factor.
Example:
A1 - "A+B=CD"
A2 - "C+D+E=A"

*Notice the double digit in the first formula.
In this example the macro needs to parse these two rows and find the
values for A,B,C,D and E that satisfy the two formulas.

The idea behind this function is to be able to type simple formulas in
several rows and have the macro calculate whatever the different
factors are that satisfy all the formulas.


Any help would be appreciated on how to do this.

On Dec 15, 9:44*pm, " wrote:
I need help creating a function.

The goal is to parse formulas from cells A1 to A5 and find the values
of each factor.
Example:
A1 - "A+B=CD"
A2 - "C+D+E=A"

*Notice the double digit in the first formula.
In this example the macro needs to parse these two rows and find the
values for A,B,C,D and E that satisfy the two formulas.

The idea behind this function is to be able to type simple formulas in
several rows and have the macro calculate whatever the different
factors are that satisfy all the formulas.

Any help would be appreciated on how to do this.

Any help how I could reference the factors into variables?
That is my major hurdle.

... find the values for A,B,C,D and E that satisfy the two formulas.

Hi. I may be wrong, but at the basic level, you will have two
equations, with 6 variables (if 'CD' is a variable.)
There would be an unlimited number of solutions.

= = = = = = = = = =
Dana DeLouis



On 12/15/09 8:44 PM, wrote:
I need help creating a function.

The goal is to parse formulas from cells A1 to A5 and find the values
of each factor.
Example:
A1 - "A+B=CD"
A2 - "C+D+E=A"

*Notice the double digit in the first formula.
In this example the macro needs to parse these two rows and find the
values for A,B,C,D and E that satisfy the two formulas.

The idea behind this function is to be able to type simple formulas in
several rows and have the macro calculate whatever the different
factors are that satisfy all the formulas.


Any help would be appreciated on how to do this.

Possibly,
But I intend to limit the range to 1 to 9 for example.
'CD' would not be a variable per se because it is in fact 'C' and 'D'
combined together.
So if A+B=CD then the 'C' in C+D+E=A will be the same as the 'C' in
'CD'.

So how do I assign variables to the factors and use them?

On Dec 18, 12:01*am, Dana DeLouis wrote:
* ... find the values for A,B,C,D and E that satisfy the two formulas.

Hi. *I may be wrong, but at the basic level, you will have two
equations, with 6 variables (if 'CD' is a variable.)
There would be an unlimited number of solutions.

= = = = = = = = = =
Dana DeLouis

On 12/15/09 8:44 PM, wrote:



I need help creating a function.

The goal is to parse formulas from cells A1 to A5 and find the values
of each factor.
Example:
A1 - "A+B=CD"
A2 - "C+D+E=A"

*Notice the double digit in the first formula.
In this example the macro needs to parse these two rows and find the
values for A,B,C,D and E that satisfy the two formulas.

The idea behind this function is to be able to type simple formulas in
several rows and have the macro calculate whatever the different
factors are that satisfy all the formulas.

Any help would be appreciated on how to do this.

So how do I assign variables to the factors and use them?

Hi. If I understand the question correctly, you extract a string, and
want to assign a value to this string.
One way that comes to mind might be to use a Dictionary Object.
The 'Key' would be the string variable, and the item the desired value.
It could work for simple things, but it might be messy with 9 equations,
and 9 unknowns. Hopefully, someone will have a better idea.

= = = = =
Dana DeLouis


On 12/21/09 8:14 PM, wrote:
Possibly,
But I intend to limit the range to 1 to 9 for example.
'CD' would not be a variable per se because it is in fact 'C' and 'D'
combined together.
So if A+B=CD then the 'C' in C+D+E=A will be the same as the 'C' in
'CD'.

So how do I assign variables to the factors and use them?

On Dec 18, 12:01 am, Dana wrote:
... find the values for A,B,C,D and E that satisfy the two formulas.

Hi. I may be wrong, but at the basic level, you will have two
equations, with 6 variables (if 'CD' is a variable.)
There would be an unlimited number of solutions.

= = = = = = = = = =
Dana DeLouis

On 12/15/09 8:44 PM, wrote:



I need help creating a function.

The goal is to parse formulas from cells A1 to A5 and find the values
of each factor.
Example:
A1 - "A+B=CD"
A2 - "C+D+E=A"

*Notice the double digit in the first formula.
In this example the macro needs to parse these two rows and find the
values for A,B,C,D and E that satisfy the two formulas.

The idea behind this function is to be able to type simple formulas in
several rows and have the macro calculate whatever the different
factors are that satisfy all the formulas.

Any help would be appreciated on how to do this.

Heres what I got so far:
I cant use the dictionary object, excel gives me some kind of error
and I cant make modifications so its out of the question,
I thought of maybe using arrays, made some spaghetti code to extract
the factors from formulas but now I dont know how to use it. :)

The simple formulas are entered on Column "F" starting on cell 2.
Use something like "ab*c=ghc" as test formula.
The sub SOLVE is empty right now and thats what needs to be filled. :)
Heres the code:

Dim factor() As String 'Factor letters
Dim FactorN() As Integer 'number for the factor letter
Dim FormulasN() As Integer ' how many factors per formula
Dim FactorUN() As Integer 'how many unique factors per formula per
line
Dim NFormula As Integer 'number of formulas to deal with
Dim TotalFactors As Integer 'Total number of factors


Sub start()

Call ParseFormulas
FactorCount = 1
startfactors = 1
For a = 1 To NFormula
If a = 1 Then
startfactors = 1
Else
startfactors = startfactors + FactorUN(a - 1)
End If ' a=1
Call solve(startfactors, FactorUN(a))

Next a

End Sub

Sub ParseFormulas()
TotalFactors = 0
checkrow = 2
countfactor = 1
startcount = Sheets("sheet1").Range("g2").Value
ReDim factor(9)
ReDim FactorN(9)
ReDim FormulasN(1)
ReDim FactorUN(1)
FormulaCount = 1

Do While Sheets("sheet1").Range(CStr("f" & checkrow)).Value < ""
ReDim Preserve FormulasN(checkrow - 1)
tempstring = UCase(Sheets("sheet1").Range(CStr("f" & checkrow)).Value)

lentempstring = Len(tempstring)
For a = 1 To lentempstring
aa = Mid(tempstring, a, 1)
If Asc(aa) 64 Then
FormulasN(checkrow - 1) = FormulasN(checkrow - 1) + 1
If countfactor = 1 Then
factor(countfactor) = aa
FactorN(countfactor) = startcount
FactorUN(checkrow - 1) = 1
countfactor = countfactor + 1

Else
b = countfactor - 1
flag = True
Do While b 0
If factor(b) < aa Then
b = b - 1
Else
flag = False
Exit Do
End If 'factor(b)<aa
Loop
If flag = True Then
factor(countfactor) = aa
FactorN(countfactor) = startcount
countfactor = countfactor + 1
FactorUN(checkrow - 1) = FactorUN(checkrow - 1) + 1
If countfactor 9 Then
ReDim Preserve factor(countfactor)
ReDim Preserve FactorN(countfactor)
End If ' countfactor9
End If 'flag=true
End If 'countfactor=1
End If 'Asc(aa) 64
Next a
ReDim Preserve FactorUN(checkrow)
checkrow = checkrow + 1
Loop
NFormula = checkrow - 2
TotalFactors = countfactor - 1

For a = 1 To checkrow - 2
Sheets("sheet1").Range(CStr("i" & a)).Value = FormulasN(a)
Sheets("sheet1").Range(CStr("k" & a)).Value = FactorUN(a)
Next a
Sheets("sheet1").Range("j1").Value = NFormulas
For a = 1 To countfactor
Sheets("sheet1").Range(CStr("j" & a)).Value = factor(a)
Next a
Sheets("sheet1").Range("l1").Value = NFormula
End Sub

Any help would be appreciated.
This is an interesting challenge.
Cheers