type mismatch and generate numbers

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
188
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
The code is expecting a number in B2, anything else will cause that error.
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
188
Office Version
  1. 2016
Platform
  1. Windows
yes this is a number . it doesn't contain any symbols or letters and start from B2 .
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
What is the number in B2?

The code should work either way but try changing that line to
VBA Code:
StartV = Range("B2").Value
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
188
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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:
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
188
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,843
Messages
5,766,740
Members
425,377
Latest member
MohdTareq007

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
Top