Type Mismatch and can't figure out why

[ndjustin20]

Hey Guys,

I am getting a type mismatch on the line in red below and for the life of me I can't figure out why. Any help is very much appreciated

Sub showAllColumns()
 
 

'-----------------------------First Quarter Numbers-----------------------------------------------------------
'This is where the CIS Table Starts ************************************************************************'
Dim firstMyNum
Dim firstCisNum
'If there is a CIS in each month of the quarter then add all cis totals and add all my monthly numbers'
If Range("CisJan").Value > 0 And Range("CisFeb").Value > 0 And Range("CisMar").Value > 0 Then
firstCisNum = Format(Range("CisJan").Value + Range("CisFeb").Value + Range("CisMar").Value, "$###,###")
firstMyNum = Format(Range("JanAct").Value + Range("FebAct").Value + Range("MarAct").Value, "$###,###")
End If
'*****************************************************************************************************
'If there is a Cis total in the first month of the quarter then calculate using only the first month'
If Range("CisJan").Value > 0 And Range("CisFeb").Value <= 0 Then
firstCisNum = Format(Range("CisJan").Value, "$###,###")
firstMyNum = Format(Range("JanAct").Value, "$###,###")
End If
'****************************************************************************************************
'If there is a Cis total in the first two months of the quarter then calcalutae using the first two months'
If Range("CisJan").Value > 0 And Range("CisFeb").Value > 0 And Range("CisMar").Value <= 0 Then
firstCisNum = Format(Range("CisJan").Value + Range("CisFeb").Value, "$###,###")
firstMyNum = Format(Range("JanAct").Value + Range("FebAct").Value, "$###,###")
End If
'****************************************************************************************************
'If there are no values in the cis information then do nothing at all'
If Range("CisJan").Value <= 0 And Range("CisFeb").Value <= 0 And Range("CisMar").Value <= 0 Then
firstMyNum = 0
firstCisNum = 0
End If
'This is where the CIS Table Ends ****************************************
'------------------------------FIRST QUATER TABLE ENDS----------------------------------------------------
 
'**************************************************************************************************************'

'----------------------------------------------Second Quarter Numbers-------------------------------------------

'This is where the CIS Table Starts ************************************************************************'
Dim secondMyNum
Dim secondCisNum
'If there is a CIS in each month of the quarter then add all cis totals and add all my monthly numbers'
If Range("CisApr").Value > 0 And Range("CisMay").Value > 0 And Range("CisJun").Value > 0 Then
secondCisNum = Format(Range("CisApr").Value + Range("CisMay").Value + Range("CisJun").Value, "$###,###")
secondMyNum = Format(Range("AprAct").Value + Range("MayAct").Value + Range("JunAct").Value, "$###,###")
End If
'*****************************************************************************************************
'If there is a Cis total in the first month of the quarter then calculate using only the first month'
If Range("CisApr").Value > 0 And Range("CisMay").Value <= 0 Then
secondCisNum = Format(Range("CisApr").Value, "$###,###")
secondMyNum = Format(Range("AprAct").Value, "$###,###")
End If
'****************************************************************************************************
'If there is a Cis total in the first two months of the quarter then calcalutae using the first two months'
If Range("CisApr").Value > 0 And Range("CisMay").Value > 0 And Range("CisJun").Value <= 0 Then
secondCisNum = Format(Range("CisApr").Value + Range("CisMay").Value, "$###,###")
secondMyNum = Format(Range("AprAct").Value + Range("MayAct").Value, "$###,###")
End If

'If there are no values in the cis information then do nothing at all'
If Range("CisApr").Value <= 0 And Range("CisMay").Value <= 0 And Range("CisJun").Value <= 0 Then
secondMyNum = 0
secondCisNum = 0
End If
 

'---------------------------------------------SECOND QUARTER NUMBERS END-----------------------------------------

'****************************************************************************************************************

'---------------------------------------------------Third Quarter Numbers-----------------------------------------
'This is where the CIS Table Starts ************************************************************************'
Dim thirdMyNum
Dim thirdCisNum
'If there is a CIS in each month of the quarter then add all cis totals and add all my monthly numbers'
If Range("CisJul").Value > 0 And Range("CisAug").Value > 0 And Range("CisSep").Value > 0 Then
thirdCisNum = Format(Range("CisJul").Value + Range("CisAug").Value + Range("CisSep").Value, "$###,###")
thirdMyNum = Format(Range("JulAct").Value + Range("AugAct").Value + Range("SepAct").Value, "$###,###")
End If
'*****************************************************************************************************
'If there is a Cis total in the first month of the quarter then calculate using only the first month'
If Range("CisJul").Value > 0 And Range("CisAug").Value <= 0 Then
thirdCisNum = Format(Range("CisJul").Value, "$###,###")
thirdMyNum = Format(Range("JulAct").Value, "$###,###")
End If
'****************************************************************************************************
'If there is a Cis total in the first two months of the quarter then calcalutae using the first two months'
If Range("CisJul").Value > 0 And Range("CisAug").Value > 0 And Range("CisSep").Value <= 0 Then
thirdCisNum = Format(Range("CisJul").Value + Range("CisAug").Value, "$###,###")
thirdMyNum = Format(Range("JulAct").Value + Range("AugAct").Value, "$###,###")
End If

'If there are no values in the cis information then do nothing at all'
If Range("CisJul").Value <= 0 And Range("CisAug").Value <= 0 And Range("CisSep").Value <= 0 Then
thirdMyNum = 0
thirdCisNum = 0
End If
'This is where the CIS Table Ends ****************************************
 
'--------------------------------------------------THIRD QUARTER NUMBERS END--------------------------------------
'*****************************************************************************************************************
'-------------------------------------------------Fourth Quarter Numbers------------------------------------------
'This is where the CIS Table Starts ************************************************************************'
Dim fourthMyNum
Dim fourthCisNum
'If there is a CIS in each month of the quarter then add all cis totals and add all my monthly numbers'
If Range("CisOct").Value > 0 And Range("CisNov").Value > 0 And Range("CisDec").Value > 0 Then
fourthCisNum = Format(Range("CisOct").Value + Range("CisNov").Value + Range("CisDec").Value, "$###,###")
fourthMyNum = Format(Range("OctAct").Value + Range("NovAct").Value + Range("DecAct").Value, "$###,###")
End If
'*****************************************************************************************************
'If there is a Cis total in the first month of the quarter then calculate using only the first month'
If Range("CisOct").Value > 0 And Range("CisNov").Value <= 0 Then
fourthCisNum = Format(Range("CisOct").Value, "$###,###")
fourthMyNum = Format(Range("OctAct").Value, "$###,###")
End If
'****************************************************************************************************
'If there is a Cis total in the first two months of the quarter then calcalutae using the first two months'
If Range("CisOct").Value > 0 And Range("CisNov").Value > 0 And Range("CisDec").Value <= 0 Then
fourthCisNum = Format(Range("CisOct").Value + Range("CisNov").Value, "$###,###")
fourthMyNum = Format(Range("OctAct").Value + Range("NovAct").Value, "$###,###")
End If

'If there are no values in the cis information then do nothing at all'
If Range("CisOct").Value <= 0 And Range("CisNov").Value <= 0 And Range("CisDec").Value <= 0 Then
fourthMyNum = 0
fourthCisNum = 0
End If
'This is where the CIS Table Ends ****************************************
'-------------------------------------------------FOURTH QUARTER NUMBERS END--------------------------------------
'*****************************************************************************************************************
monthOneBudTxt.Value = 0
monthTwoBudTxt.Value = 0
monthThreeBudTxt.Value = 0
monthOneActTxt.Value = 0
monthTwoActTxt.Value = 0
monthThreeActTxt.Value = 0
monthOneIncDecTxt.Value = 0
monthTwoIncDecTxt.Value = 0
monthThreeIncDecTxt.Value = 0

quarterlyBreakDownHeader.Value = "Annual Analysis"
cisCompBreakDownHeader.Value = "Annual CIS Comparison"
 
'This is where the math for all quarters will take place'
'Start of Quarterly annual math****************************************************************************'
Dim budTotalVar
Dim actTotalVar
budTotalVar = Format( _
  Range("JanBud").Value + Range("FebBud").Value + Range("MarBud").Value _
+ Range("AprBud").Value + Range("MayBud").Value + Range("JunBud").Value _
+ Range("JulBud").Value + Range("AugBud").Value + Range("SepBud").Value _
+ Range("OctBud").Value + Range("NovBud").Value + Range("DecBud").Value, "$###,###")
actTotalVar = Format( _
  Range("JanAct").Value + Range("FebAct").Value + Range("MarAct").Value _
+ Range("AprAct").Value + Range("MayAct").Value + Range("JunAct").Value _
+ Range("JulAct").Value + Range("AugAct").Value + Range("SepAct").Value _
+ Range("OctAct").Value + Range("NovAct").Value + Range("DecAct").Value, "$###,###")
QtrBudTotal.Value = budTotalVar
QtrActTotal.Value = actTotalVar
diffBudAct.Value = Format(actTotalVar - budTotalVar, "$###,###")
qtrIncDec.Value = Format(100 * (actTotalVar / budTotalVar), "%#")

'Start Annual CIS Table**************************************************************************************'
Dim myAnnualTotal
Dim cisAnnualTotal
[COLOR=red]myAnnualTotal = Format(firstMyNum + secondMyNum + thirdMyNum + fourthMyNum, "$###,###")
[/COLOR]cisMyNum.Value = myAnnualTotal
cisAnnualTotal = Format(firstCisNum + secondCisNum + thirdCisNum + fourthCisNum, "$###,###")
cisNumTxt.Value = cisAnnualTotal
diffCisMyNum.Value = Format(myAnnualTotal - cisAnnualTotal, "$###,###")

End Sub

Justin

 

[VoG]

You are formatting firstMyNum etc. as strings then trying to add them.

 

[ndjustin20]

I didn't declare it as a string and have done simple math functions using some like:

Dim number1
Dim number2
Dim total

number1 = 1
number2 = 2
total = number1 + number2

TextBox1.Value = total



For some reason though it isn't working in this case though. I'm obvisouly missing something Can maybe elaborate or tell me how you would fix the issue if you have a few minutes please.


Justin

 

[VoG]

You didn't declare the data type so they are treated as Variants. With this

firstMyNum = Format(Range("JanAct").Value, "$###,###")

you are creating a text string. You cannot add up text strings.

 

[ndjustin20]

So is it the Format method that is actually converting it to a string then?

 

[VoG]

Yes, that is what Format does.

 

[ndjustin20]

Ok so Format is the culprit!!!!!!!!!!!!!!!!!!!!! LOL thank you very much...I stopped banging my head against the wall now Well until the next error LOL

 

[ndjustin20]

Thank you for your help today I appreciate it