I am new to the site and to VBA. I have some code that loops for each month. I copied and pasted the code for each month making the small adjustments to the Rows/columns. Each Month performs correctly except for December, I get a Runtime '13' type mismatch error. I think it has something to do with the cell type when asking if the cell is blank (""). When I change that to 0, the error doesn't occur. Below is some code for three months. Any help on how to get past this error would be much appreciated! Thanks I have bolded the line that is highlighted when I debug the error.
[VBA] ''Oct
Oct_TX = ActiveSheet.Cells(21 + i, 33).Value ' TX amount for Oct
Oct_CC = ActiveSheet.Cells(21 + i, 14).Value 'Oct CC amount after refresh
If Oct_TX <> 0 And Oct_CC = "" And ActiveSheet.Cells(21 + i, 2).Interior.Color = RGB(197, 217, 241) Then ' if TX is populated and CC is blank
ActiveSheet.Cells(21 + i, 14).Value = Oct_TX
End If
''Nov
Nov_TX = ActiveSheet.Cells(21 + i, 34).Value ' TX amount for Nov
Nov_CC = ActiveSheet.Cells(21 + i, 15).Value 'Nov CC amount after refresh
If Nov_TX <> 0 And Nov_CC = "" And ActiveSheet.Cells(21 + i, 2).Interior.Color = RGB(197, 217, 241) Then ' if TX is populated and CC is blank
ActiveSheet.Cells(21 + i, 15).Value = Nov_TX
End If
''Dec
Dec_TX = ActiveSheet.Cells(21 + i, 35).Value ' TX amount for Nov
Dec_CC = ActiveSheet.Cells(21 + i, 16).Value 'Dec CC amount after refresh If Dec_TX <> 0 And Dec_CC = "" And ActiveSheet.Cells(21 + i, 2).Interior.Color = RGB(197, 217, 241) Then ' if TX is populated and CC is blank
ActiveSheet.Cells(21 + i, 16).Value = Dec_TX
End If[/VBA]
Where do I add the code you recommended? My code loops through rows 21+i so I cannot tell if its the first loop or which row may be causing the issue. As I mentioned, I know the very basics/concepts of VBA, but still learning how to debug my issues.
[VBA]If Not IsEmpty(Dec_TX) And IsEmpty(Dec_CC) And ActiveSheet.Cells(21 + i, 2).Interior.Color = RGB(197, 217, 241) Then ' if TX is populated and CC is blank
[/VBA]
Thank you Kenneth that worked. Do you know why this column was acting different than the others when using blank ("")? I have a follow up question. The next line of code formats the cell if a certain condition is met. One of them is if DEC_CC <> Dec_TX. In my example, they do match and the function should not be called. When I looked at the code, one variable has decimals and the other does not, so it does not think they match. I can see in the cell they match and again its only happening to December, the other months work fine.
[VBA]If Dec_TX <> 0 And Dec_CC <> Dec_TX And Not IsEmpty(Dec_CC) Then
Call CC_Change_Format(21 + i, 16)
End If
[/VBA]
Use F8 to debug line by line. After that line executes, hold the cursor over the variables to see the value. Try using MsgBox() or Debug.Print to view the results in the Immediate window.
When you use "" for a null value, you are saying compare to a string null. Your value is actually a null number. You can use vbNull as a comparison operator. VbNullString can be used for null string value comparisons.
Thank you for the advice. I did use the F8 function to figure out why my next issue is being created. The code itself is working correctly but the Variables are storing the wrong number format. One variable is saving the whole number and the other is saving a decimal, therefor in the code they are not equal, but in excel they are. Do you have any thoughts on this issue? Thanks again.
I have attached an Example File. The file I am working with is attached to a BW database and the code runs after a refresh. I have put a comment with many ;***'s to show the line. Basically it looks at Column 16 Vs Column 35 and if equal, should stay blue, if not equal change to red. Thanks.
Solved: Runtime error '13', Type Mismatch
Reply With Quote
