none
Run time error '-2147352571 (80020005)' : Type Mismatch RRS feed

  • Question

  • Dear experts,

    I have developed user form and in back end i have setup one sheet for "Data entry database" and another sheet for "Reference details", from this sheet only combo box will get details. This reference sheet will be updated every month. Update process would be done by manually, just copy the entire sheet and paste the details in "Reference details". When i was developing this user form, i did not face any error. But after completing everything, every first month when i do this copy and paste process. I face the "Run time error '-2147352571 (80020005)' - Type mismatch". When i debug (Press F8) the yellow color line highlights in the following code. 

    Kindly do the needful.

    Private Sub btnuserform_Click()
    SVForm.Show (starting debug here)
    End Sub
    
    Private Sub UserForm_Initialize()
    
    Dim i As Long, LastRow As Long, ws As Worksheet
    Set ws = Worksheets("FacilityDetails")
    LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    Me.cbfacilityname.AddItem ws.Cells(i, "B").Value (finally debug end here)
    Next i

    Wednesday, June 27, 2018 9:10 AM

All replies

  • ws.Cells(i, "B").Value -> you should use numeric values.

    the arguments for cell are row number and column number

    https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-cells-property-excel

    change it to: ws.Cells(i, 2).Value .

    Hope it helps

    Wednesday, June 27, 2018 9:24 AM
  • Thanks for your quick response. i have changed as you suggested. Still the same error pop up. Could you please guide me on this. 

    Note: When i copy and paste large database only, i used to get this error. Other wise. Its works fine. 

    Regards

    Wednesday, June 27, 2018 9:50 AM
  • I tried to simulate, but I don't get an error.

    Try to set a breakpoint on your line that generates the error and check for the value you are adding to the combobox: what's in the cell? 

    Wednesday, June 27, 2018 12:45 PM