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 DotNext: Finicky EssVRetrieve