Error 438 when trying to run a macro from a button. Code then no longer work.

[katten]

Hi!

This will be my first post to bear with me. I've ran into an odd problem that I hope someone has a solution to.

I have a module that call several other moduels. The module works without errors. However, once i assign an Excel button to the macro and click it I get the following error:
runtime error 438 : Object doesn't support this property or method
and the following line inside one of the modules is highlighted:
FTH = CInt(Evaluate(FTH))

If i try to execute the macro again, but not by using the button, I get the same error. In other words, what worked fine before adding the button doesn't work any longer.

I'm just learning vba and I know that the macro isn't pretty so i'm not surprised that I get an error but I'm surprised it seems to work just fine before adding the button. If someone could explain why this is, or make my code a bit more elegant to bypass the issue, that would be much appreciated.



This is the macro that's giving me an error:

Sub fulltimefixer_fisk()

Dim Result As Range
Dim HTH As String
Dim HTA As String
Dim FTH As String
Dim FTA As String


For Each Result In Range("E2: E271")
HTH = Mid(Format(Result, "0"), 6, 1)
HTH = CInt(Evaluate(HTH))
HTA = Mid(Format(Result, "0"), 8, 1)
HTA = CInt(Evaluate(HTA))
FTH = Left(Result, 1)
FTH = CInt(Evaluate(FTH))
FTA = Mid(Result, 3, 1)
FTA = CInt(Evaluate(FTA))


Result.Value = Left(Result, 8) & "," & (FTH - HTH) & ":" & (FTA - HTA) & ")"
 
Next


End Sub

Short explanation: I'm gathering football fixtures but I want to present goals in first half and second half. Input looks like this (one cell):
2:3 (1:1)
And output should look like this:
2:3 (1:1,1:2)
I'm aware this could be done with a formula inside Excel but in my case, a vba solution would be better.

Thank you!

 

[NdNoviceHlp]

Not sure if this helps but HTH is dimmed as a string and CInt outputs an integer. Also, strings and Evaluate seem to require square brackets. HTH. Dave

 

[katten]

Not sure if this helps but HTH is dimmed as a string and CInt outputs an integer. Also, strings and Evaluate seem to require square brackets. HTH. Dave

Hi and sorry for my late reply.
Thank you, Issue has been solved, the problem was within my data set!

Initially I thought that the cell had to be made a string because when I changed it to an integer it would give me an error. Noticed I had one game which was postponed and so the data input was different from rest and that's why things didnt work as intended. I made an if statement to fix this, and with that, I could clean up the code.

Code now looks like this:

Sub fulltimefixer_fisk()

Dim Result As Range
Dim HTH As Integer
Dim HTA As Integer
Dim FTH As Integer
Dim FTA As Integer
For Each Result In Range("E2: E241")
    If Len(Result) = 9 Then
        HTH = Mid(Result, 6, 1)
        HTA = Mid(Result, 8, 1)
        FTH = Left(Result, 1)
        FTA = Mid(Result, 3, 1)
        Result.Value = Left(Result, 8) & "," & (FTH - HTH) & ":" & (FTA - HTA) & ")"
    Else
        Result.Value = Null
    End If
Next


End Sub

Still odd that the old version worked until adding a button though.

 

[NdNoviceHlp]

Glad that you got it sorted out. Thanks for posting your outcome. Dave