Runtime Error 13 Type Mismatch from Macro

[Foodo]

Hi everyone,

I am quite stuck in troubleshooting my macro to fix a runtime error 13 Type mismatch problem. Below are pertinent excerpts from my code:

Dim HHYear As Variant
Dim HAccount As Variant
Dim HAmpersan As Variance

HAmpersan = Chr(38)

Cells.Find(What:="HPVAL", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
HScenario = HComma & HQuote & "Scenario" & HHash & Worksheets("Sheet6").Range("A2").Value & HQuote
HHYear = HComma & HQuote & "Year" & HHash & HAmpersan & "D22" & HAmpersan & HQuote & HQuote
HHPeriod = HComma & HQuote & "Period" & HHash & Worksheets("Sheet6").Range("D21").Value & HQuote
HView = HComma & HQuote & "View" & HHash & Worksheets("Sheet6").Range("D2").Value & HQuote
HEntity = HComma & HQuote & "Entity" & HHash & Worksheets("Sheet6").Range("E2").Value & HQuote
HValue = HComma & HQuote & "Value" & HHash & Worksheets("Sheet6").Range("F2").Value & HQuote
HICP = HComma & HQuote & "ICP" & HHash & Worksheets("Sheet6").Range("G2").Value & HQuote
HCustom1 = HComma & HQuote & "Custom1" & HHash & Worksheets("Sheet6").Range("H2").Value & HQuote
HCustom2 = HComma & HQuote & "Custom2" & HHash & Worksheets("Sheet6").Range("I2").Value & HQuote
HActivityType = HComma & HQuote & "ActivityType" & HHash & Worksheets("Sheet6").Range("J2").Value & HQuote
HProduct = HComma & HQuote & "Product" & HHash & Worksheets("Sheet6").Range("K2").Value & HQuote
HLedgerType = HComma & HQuote & "LedgerType" & HHash & Worksheets("Sheet6").Range("L2").Value & HQuote
HAccount = HComma & HQuote & "Account" & HHash & HQuote & HAmpersan & Cells(ActiveCell.Row, 3).Address & HAmpersan & HQuote & HQuote
HPartA = HEqual & "HsGetValue(" & HConnect & HScenario & HYear & HPeriod & HView & HEntity & HValue & HAccount & HICP & HCustom1 & HCustom2 & HActivityType & HLedgerType & HProduct & ")"


Essentially, I am trying to replace where cell value is "HPVAL" with another formula with a different syntax. The bold lines above are giving me grief. The syntax of the formula requires that it generate an output like "&D2&"". Interestingly, the error only throws up for HHYear and not HAccount even though the output is the same.

I have also tried on two different Excel 2010 patch levels and still get the same result. In reading other postings, it seems that "&D2&"" could be misinterpreted as a "range" or another data type? Originally I had set HHYear as String but changed to Variant in hopes to troubleshoot.

Suggestions?

 

[Foodo]

What is especially frustrating is that if I MsgBox (HPartA), the content seems perfect.

If I manually re-type the output into a cell, no error message.

I even thought it may be related to the word "Year" or "Period" so added another H.

Why would it work for one but not the other?

 

[Mohammad Basem]

Hi Foodo,

…Interestingly, the error only throws up for HHYear and not HAccount even though the output is the same.

Actually, the output is not the same as there is a missing HQuote in HHYear statement.

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]
HHYear   = HComma & HQuote & "Year"    & HHash          & HAmpersan & "D22"                            & HAmpersan & HQuote & HQuote

HAccount = HComma & HQuote & "Account" & HHash [B][COLOR="Red"]& HQuote [/COLOR][/B]& HAmpersan & Cells(ActiveCell.Row, 3).Address & HAmpersan & HQuote & HQuote[/COLOR][/SIZE][/FONT]

Please wrap your code with code tag, same as above.

 

[Foodo]

Sorry Mohammad - you are right. That said, even inserting back the missing HQuote I still get the same error. I was playing around different combinations to see if it would result in a different behaviour.

Any other suggestions?

 

[NoSparks]

My Excel won't compile that code due to the declaration type of HAmpersan.

Use Option Explicit and from Debug click Compile VBA project.

 

[Foodo]

Sorry - mis post on previous post.

I was able to isolate the issue further. It seems the actual code throwing the error is the below:

ActiveCell.Replace HReplace, HPartA

If I changed the above to "ActiveCell.Formula = HPartA", then the macro resolves itself. Is there something in the Replace syntax I am missing?

 

[Mohammad Basem]

Couple of things,

Are you using Option Explicit?

Change

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim HAmpersan As [COLOR="Red"]Variance[/COLOR][/COLOR][/SIZE][/FONT]

to

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Dim HAmpersan As [B]Variant[/B][/COLOR][/SIZE][/FONT]

…if I MsgBox (HPartA), the content seems perfect

In HPartA, you are using HYear not HHYear (the statement) and HPeriod not HHPeriod (the statement).

 

[Foodo]

@ Mohammed - I set it to Variant and still does not work. Any other suggestions?

 

[Mohammad Basem]

Hi Foodo,

I suggest you first use Option Explicit, compile the project and fix returned errors, if any, then post the complete macro with declarations of public variables and constants used by the code, if any.

The snippet you have provided either contains double definition of variables or similar name variables

In HPartA, you are using HYear not HHYear (the statement) and HPeriod not HHPeriod (the statement).

The complete code and sample data are necessary to track the error.