How to apply vlookup only for empty cells using vba and another workbook

Question

I want to apply vlookup only on the blank cells through VBA. I am using the below code, but it gives me a Run-time error 13 "Type mismatch".When I run the code step by step via F8, I also get an error 2042 at position If i = "" Then, which also indicates "#N/A".

Dim FileName3 As String

FileName3 = "C:xxxxxx.xlsx"

Dim wb As Workbook: Set wb = ThisWorkbook
Dim lastrow As Long
Dim ws As Worksheet: Set ws = wb.Sheets("Data")
lastrow = ws.cells(Rows.Count, 1).End(xlUp).Row
Dim wb2 As Workbook: Set wb2 = Workbooks.Open(Filename:=FileName3, ReadOnly:=True)
Dim rng As Range: Set rng = ThisWorkbook.Worksheets("Data").Range("S2" & lastrow)
Dim lookupRange As Range: Set lookupRange = wb2.Sheets("Page 1").Range("B2" & lastrow)
Dim i As Range

For Each i In rng
If i = "" Then
i = Application.VLookup(ThisWorkbook.Sheets("Data").Range("A" & i.Row), lookupRange, 2, False)

'MsgBox c.Row
End If
'
Next i
'/////// paste by value

Sheets("Data").Columns(52).Copy
Sheets("Data ").Columns(52).PasteSpecialxlPasteValues
wb2.Close False

ThisWorkbook.Save

I had tried it before with WorksheetFunction.VlookUp, but the same error comes up. The VlookUp should be executed in the datasheet ("Data") in column "S" for all empty cells. The LookUp Values are located in another workbook file. I would appreciate it very much if someone could help me.

VBasic2008 · Accepted Answer · 2022-03-18 16:10:10Z

VBA VLookup For Blank Cells

Option Explicit

Sub VLookupBlanks()

    Const sFilePath As String = "C:\xxxxxx.xlsx"

    Application.ScreenUpdating = False
    
    Dim swb As Workbook
    Set swb = Workbooks.Open(Filename:=sFilePath, ReadOnly:=True)
    Dim sws As Worksheet: Set sws = swb.Worksheets("Page 1")
    Dim slRow As Long: slRow = sws.Cells(sws.Rows.Count, "B").End(xlUp).Row
    Dim srg As Range: Set srg = sws.Range("B2:C" & slRow)

    Dim dwb As Workbook: Set dwb = ThisWorkbook
    Dim dws As Worksheet: Set dws = dwb.Worksheets("Data")
    Dim dlRow As Long: dlRow = dws.Cells(dws.Rows.Count, "A").End(xlUp).Row
    Dim drg As Range: Set drg = dws.Range("S2:S" & dlRow)
    
    Dim dCell As Range
    Dim dValue As Variant
    
    For Each dCell In drg.Cells
        If Len(CStr(dCell.Value)) = 0 Then
            dValue = Application.VLookup( _
                dCell.EntireRow.Columns("A").Value, srg, 2, False)
            If Not IsError(dValue) Then dCell.Value = dValue
        End If
    Next dCell
 
    swb.Close SaveChanges:=False
    
    With drg.EntireRow.Columns("AZ")
        .Value = .Value
    End With
    
    dwb.Save

    Application.ScreenUpdating = True

    MsgBox "Columns updated.", vbInformation

End Sub

FaneDuru · Accepted Answer · 2022-03-18 16:39:05Z

0

Please, try removing of:

Dim rng As Range: Set rng = ThisWorkbook.Worksheets("Data").Range("S2" & lastrow)
Dim lookupRange As Range: Set lookupRange = wb2.Sheets("Page 1").Range("B2" & lastrow)
Dim i As Range

For Each i In rng
If i = "" Then
i = Application.VLookup(ThisWorkbook.Sheets("Data").Range("A" & i.Row), lookupRange, 2, False)

'MsgBox c.Row
End If
'
Next i

with:

  Dim rngV As Range
  Dim rng As Range: Set rng = ws.Range("S2:S" & lastrow)
  Dim lookupRange As Range: Set lookupRange = wb2.Sheets("Page 1").Range("B2:C" & lastrow)
  On Error Resume Next 'only to avoid an error if no any empty cell exists in rng
   Set rngV = rng.SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0
  If rngV Is Nothing Then Exit Sub 'no any empty cell...
  rngV.Formula = "=Vlookup(A" & rngV.cells(1).row & ", " & lookupRange.Address(external:=True) & ", 2, False)"

edited Mar 18, 2022 at 16:39

answered Mar 18, 2022 at 15:20

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

6 Comments

Gogi Over a year ago

Thanks FaneDuro. Itr works but for all empty cell in the sheet "Data" and not only for column S.

Gogi Over a year ago

And the VlookUp Value is not working well: ]Page 1'!$B$2459; 2;false). The range is B:C and the value is on C which should shown in column S into Sheet Data. Is it possible to get only the values without formula?

FaneDuru Over a year ago

@Gogi I took rng as it was built... So, do you want it to be all cells of S:S column? Now I can see that your code creates a range of only one cell. Also, lookupRange looks wrongly built, too. Please, explain in words what do you expect from lookupRange to be? Try writing its address...

Gogi Over a year ago

Sorry, the lookUpRange is my fault. It should be B.:C. And the VlookUp should be executed in the datasheet ("Data") in column "S" for all empty cells. In my first code: rng = ThisWorkbook.Worksheets("Data").Range("S2" & lastrow)

FaneDuru Over a year ago

@Gogi Your construction is wrong. If, let us say that lastrow = 100, then your range will have a single cell, respectively: Range("S2100"). I will edit my answer and show you how to build them. I did not imagine that you built them in that way... Please, try the updated code.

|

Collectives™ on Stack Overflow

How to apply vlookup only for empty cells using vba and another workbook

2 Answers 2

VBA VLookup For Blank Cells

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

VBA VLookup For Blank Cells

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related