1

I have applied vlookup to last available empty column in sheet "Open" its working fine, vlookup gives values with errors and i need to count them and the no. of errors should be sent to cell"A1" same sheet.

To count the no. of errors in last column with values, currently i have defined range manually GY2:GY200 I am not able to give auto range to count errors.

Sub GET_BHAV()

    Dim VlookUpVal As Variant
    Dim OpenWs As Worksheet, , bhavWs As Worksheet
    Dim OpenLastRow As Long, bhavLastRow As Long, x As Long
    Dim bhavRng As Range
    Dim lcol As Long


    Set OpenWs = ThisWorkbook.Worksheets("Open")

    Workbooks.Open "C:\Users\playt\Desktop\STACK\VANGU\cm07JAN2020bhav.csv"
    Set bhavWs = Workbooks("cm07JAN2020bhav.csv").Worksheets("cm07JAN2020bhav")
    bhavLastRow = bhavWs.Range("A" & Rows.Count).End(xlUp).Row
    OpenLastRow = OpenWs.Range("A" & Rows.Count).End(xlUp).Row

    Set bhavRng = bhavWs.Range("A1:G" & bhavLastRow)


    With OpenWs
        For x = 2 To OpenLastRow
            'On Error Resume Next
            VlookUpVal = Application.VLookup(.Range("A" & x).Value, bhavRng, 3, False)

            'gives #NA value to cell with error
            If IsError(VlookUpVal) Then
                .Cells(x, .Columns.Count).End(xlToLeft).Offset(0, 1).Value = "#NA"
                ' highlights cell color on error
                .Cells(x, .Columns.Count).End(xlToLeft).Offset(0, 0).Interior.ColorIndex = 3

                OpenWs.Range("A1").Value = WorksheetFunction.CountIf(OpenWs.Range("GY2:GY200"), "#NA")

                w = OpenWs.Range("A1").Value


            Else
                .Cells(x, .Columns.Count).End(xlToLeft).Offset(0, 1).Value = VlookUpVal

            End If
        Next x
    End With




End Sub
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
nitin thakur
  • 23
  • 4
  • No it doesnt work , I dont need to write GY2:GY as on new dates after new data the last column will be different so there must be an auto range – nitin thakur Feb 06 '20 at 16:07
  • `.Columns.Count).End(xlToLeft)` - use this to find the last column (as you are already doing). – BigBen Feb 06 '20 at 17:24
  • didn't work, My worksheet name is Open can you just simply tell me what codes i should apply to range entire last column with values i tried Set myRange = OpenWs.Range(Columns.Count).End(xlToLeft) – nitin thakur Feb 07 '20 at 08:25
  • `Dim lastColumn as Long`, then `lastColumn = .Cells(x, .Columns.Count).End(xlToLeft).Offset(,1).Column` – BigBen Feb 07 '20 at 13:20
  • Well the code you wrote is giving the single last empty cell. I modified and I am now able to to have semi automatic range. Yes semi automatic i am compelled to define the last row of that column as "GY21" how to give auto Range?? OpenWs.Range("A1").Value = WorksheetFunction.CountIf(OpenWs.Range(.Cells(2, .Columns.Count).End(xlToLeft).Offset(0, 0), "GY21") – nitin thakur Feb 08 '20 at 13:36
  • [This](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) is the question that shows you how to find the last row. – BigBen Feb 08 '20 at 15:58
  • bmr = .Cells(2, .Columns.Count).End(xlDown).Row rmc = .Cells(2, .Columns.Count).End(xlToLeft).Column Set myRange = .Range((.Cells(2, .Columns.Count).End(xlToLeft)), .Cells(bmr, rmc)) it worked for me thanks – nitin thakur Feb 08 '20 at 17:01

0 Answers0