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.)
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 ?
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 WithThe attached file uses this for both OptionButton_3 and 4.
Hope this helps.