type mismatch and generate numbers

KalilMe

Active Member
Joined
Mar 5, 2021
Messages
343
Office Version
  1. 2016
Platform
  1. Windows
hi
I got this code from this forum but it gives error type mismatch in this line
VBA Code:
StartV = Range("b2")
my numbers in COL B 2722,2723,2725 the result should be 2721,2724 in COL G when show the result . it should start after 0
Code:
Sub MissingValues()
Dim rng As Range, j As Variant
Dim StartV As Long, EndV As Long, i As Long
Dim k() As Long, Last_Row As Long

Last_Row = Range("B5000").End(xlUp).Row
ReDim k(0)

Set rng = Range("B2:B" & Last_Row)
StartV = Range("B2")
EndV = Range("B" & Last_Row)

    For i = StartV To EndV
        On Error Resume Next
        j = Application.Match(i, rng, 0)
        If IsError(j) Then
            k(UBound(k)) = i
            ReDim Preserve k(UBound(k) + 1)
        End If
    Next i
Range("G1") = "Missing values"
Range("G2:G" & UBound(k) + 1) = Application.Transpose(k)
End Sub

I hope  to  find  help who has  knowledge in vba
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
The code is expecting a number in B2, anything else will cause that error.
 
Upvote 0
yes this is a number . it doesn't contain any symbols or letters and start from B2 .
 
Upvote 0
What is the number in B2?

The code should work either way but try changing that line to
VBA Code:
StartV = Range("B2").Value
 
Upvote 0
it works with another file , but my original file gives error and if I delete the data and rewrite. it gives 0 in G1:confused:
 
Upvote 0
it works with another file
If it works with one file but not with another then that tells us that the problem is with the data, not with the code.

Without the problem data to test, the only reason that comes to mind is that which I have already mentioned (you only said 'Type mismatch' so I'm assuming that it is error 13), that B2 does not contain a valid number. With the example numbers provided in post 1, it would need to be 2720 in order to return the correct results.

Although I don't think it is the case here, things like merged cells or numbers formatted as text are potential problems. Protecting the sheet should not be an issue at the point where the error is occurring.
 
Upvote 0
Solution
numbers formatted as text are potential problems.
yes , you're right . the problem is from numbers formatting.
thank you so much for your assistance (y)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top