Prev: Number Format. Switch Comma to Dot
Next: Finicky EssVRetrieve
From: HammerJoe on 5 Jan 2010 21:37
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

From: HammerJoe on 12 Jan 2010 23:52
Any help would be appreciated.
This is an interesting challenge.
Cheers
First  |  Prev  | 
Pages: 1 2
Prev: Number Format. Switch Comma to Dot
Next: Finicky EssVRetrieve