How to do check for factors in formulas?

[HammerJoe]

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.

 

[HammerJoe]

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.

 

[Dana DeLouis]

... 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

 

[HammerJoe]

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?

 

[Dana DeLouis]

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

 

[HammerJoe]

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 ' countfactor>9
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

 

[HammerJoe]

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