Would anyone have even the slightest idea what might be going wrong with the following code?
Sometimes it works fine, but most of the time I get a 'runtime 1004: application-defined or object-defined error'
NOTES:
-When stepping through application, runtime 1004 error is raised on the 'End Function' of the parseFormula function
Offending Code
------------------
Chunk of offending code from main function
Sheet1.cells(x, y).Value = Sheet1.cells(x, y).Value & parseFormula(ri(i).getPreF
ormula, x) ' first var is string, second is integer
Same as above but I've substituted function calls with the data that's actually being passed
Sheet1.cells(x, y).Value = Sheet1.cells(x, y).Value & parseFormula("1+", 2) ' first var is string, second is integer
Parse Function Formula
--------------------------
-
' Convert $D to D & row Number
' IE: $D+E$ would return D4+E4 for fourth row, D5+E5 for fifth row, etc...
Public Function parseFormula(ByVal formula As String, ByVal row As Integer) As String
Dim a As Integer
Dim char As String
Dim formattedFormula As String
For a = 1 To Len(formula)
char = Mid(formula, a, 1)
If char = "$" Then
If Asc(LCase(Mid(formula, a + 1, 1))) >= 97 And Asc(LCase(Mid(formula, a + 1, 1))) <= 122 Then
formattedFormula = formattedFormula & Mid(formula, a + 1, 1) & row
a = a + 1
End If
Else
formattedFormula = formattedFormula & Mid(formula, a, 1)
End If
Next a
MsgBox "." & formattedFormula & "."
parseFormula = CStr(formattedFormula)
End Function
in debug, parseFormula does indeed return +1, press F8 again and move to End Function line, press F8 again, runtime 1004
The complete application is avaialble if you would like to test it for yourself.
Thanks in advance
Update: I've been working this problem off and on for a few days now and have finally managed to narrow it down a bit.
Seems it doesn't like the
sheet1.cells(x,y).value = sheet1.cells(x,y).value
since msgbox "." & parseFormula(ri(i).getPreF
ormula, x) & "." works beautifully for all lines and all situations...
Start Free Trial
