Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

type mismatch despite using the same procedures


 Hi  experts,

I'm so  confused , I can't   extermely  find  what  the  reason causes mismatch  error  despite of  using  the  same  procedures . the  error  shows  when select optionbutton4 

in this line 

 MySum = MySum + .List(r, 4)

it  should  show  data  in listbox  like  optionbutton3  but  I'm still confused  for  that.

by  the  way  I  used on error  resume  next  and  works  , but  I'm fraid  return error  again . I  think  the  right  way is  finding  exactly  where  is  the  error.

any  help  to  fix  that  experts ?



Selected Answer

Hi Alaa

Your problem arises since worksheet RECEIVABLE has blank cells in column E and F (whereas CUSTOMERS has 0.00 where there is no value) and your lines like:

a(i, 5) = Format$(a(i, 5), "#,##0.00")

convert values to strings. This doesn't seem to matter if you add a string like 1,000.00 to the (Double) variant MySum in the line:

With ListBox1
            For r = 0 To .ListCount - 1
                MySum = MySum + .List(r, 4)

but if the latter is "" then the error occurs. Converting it to Double (to match MySum) doesn't help so I suggest you use this instead:

        With ListBox1
            For r = 0 To .ListCount - 1
                If .List(r, 4) <> vbNullString Then MySum = MySum + .List(r, 4)
                If .List(r, 5) <> vbNullString Then MySum1 = MySum1 + .List(r, 5)
            Next r
        End With

The attached file uses this for both OptionButton_3 and 4.

Hope this helps.



Hi John,
umm, simple error  but  I can't find out  . this  means I  have  to  make the  columns  contains 0.00 also  textbox  to  avoid  this  problem but  your  solution treated  this  problem .
thanks  very much 
Alaa (rep: 18) Nov 9, '22 at 3:56 am
Glad that worked for you, Alaa. Note that you can leave blanks in your cells with this solution. 

Thanks for selecting my answer.
John_Ru (rep: 4312) Nov 9, '22 at 4:42 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login