how to apply vlookup only on blank cell by vba

[m_vishal_c]

hi, i want to apply vlookup only on blank cell through VBA. I am using below code, but it gives me all column value instead of only blank cell.

Private Sub CommandButton2_Click()
Dim FileName2 As String

FileName2 = "D:\1.0 Projects\[First.xlsx]"

ActiveWorkbook.Worksheets("Sheet1").Range("AZ3:AZ4000") = "=VLOOKUP(A3,'" & FileName2 & "main'!$A$3:$CC$4000,81,FALSE)"
Sheets("Sheet1").Columns(53).Copy
Sheets("Sheet1").Columns(53).PasteSpecial xlPasteValuesc

End sub


is anyone who can help me.
heaps thanks in advance

thanks

 

[Ells_]

seeing as you end up doing a pastespecial for the values at the end, I'm guessing you just want the value and not the formulas?

If so, try this:

Private Sub CommandButton2_Click()




Dim FileName2 As String: FileName2 = "[COLOR=#574123]D:\1.0 Projects\First.xlsx[/COLOR]"
Dim wb2 As Workbook: Set wb2 = Workbooks.Open(FileName:=FileName2)
Dim rng As Range: Set rng = ThisWorkbook[COLOR=#574123].Worksheets("Sheet1").Range("AZ3:AZ4000")[/COLOR]
Dim lookupRange As Range: Set lookupRange = wb2.Sheets("[COLOR=#574123]main[/COLOR]").Range("A3:CC4000")
Dim c As Range


On Error Resume Next ' skips over cells in case the vlookup results in an error


For Each c In rng
If c.Value = "" Then
c = WorksheetFunction.VLookup(ThisWorkbook.Sheets("Sheet1").Range("A" & c.Row), lookupRange, 81, False)
MsgBox c.Row
End If
    
Next c


wb2.Close




End Sub

 

[MARK858]

Just another possible option if the cells are truly blank and not empty strings caused by formula results...

Private Sub CommandButton2_Click()

    Dim FileName2 As String

    FileName2 = "D:\1.0 Projects\[First.xlsx]"

    With ActiveWorkbook.Worksheets("Sheet1").Range("AZ3:AZ4000").SpecialCells(4)
        .Formula = "=VLOOKUP(A3,'" & FileName2 & "main'!$A$3:$CC$4000,81,FALSE)"
        .Value = .Value
    End With

End Sub

Please note that the code will work out the usedrange with the range and so won't put in the formula past the last cell with data.

 

[m_vishal_c]

seeing as you end up doing a pastespecial for the values at the end, I'm guessing you just want the value and not the formulas?

If so, try this:

Private Sub CommandButton2_Click()




Dim FileName2 As String: FileName2 = "[COLOR=#574123]D:\1.0 Projects\First.xlsx[/COLOR]"
Dim wb2 As Workbook: Set wb2 = Workbooks.Open(FileName:=FileName2)
Dim rng As Range: Set rng = ThisWorkbook[COLOR=#574123].Worksheets("Sheet1").Range("AZ3:AZ4000")[/COLOR]
Dim lookupRange As Range: Set lookupRange = wb2.Sheets("[COLOR=#574123]main[/COLOR]").Range("A3:CC4000")
Dim c As Range


On Error Resume Next ' skips over cells in case the vlookup results in an error


For Each c In rng
If c.Value = "" Then
c = WorksheetFunction.VLookup(ThisWorkbook.Sheets("Sheet1").Range("A" & c.Row), lookupRange, 81, False)
MsgBox c.Row
End If
    
Next c


wb2.Close




End Sub

hi Heaps thanks, I need to ask in this code, you are going to open that workbook but if someone has already open (FileName2) then this code will not work. is it possible that without opening "FileName2" code works or it can work even though "Read only mode"

This code does give me error in Ready Only file mode

"Run time error '1004'
Sorry, Excel can't open two workbooks with the same name at the same time"

Please guide me. Much appreciated

Thanks

 

[Ells_]

It'll try to open the file just as you would any other time. If someone else has the file open, you'll get the typical prompt and can choose to open it as read only.
You can set it to this to always open as read only:

Dim wb2 As Workbook: Set wb2 = Workbooks.Open(FileName:=FileName2, ReadOnly:=True)

You get the 1004 error because you are trying to have 2 workbooks open with the same name (ex: 2 workbooks both named "First")

 

[m_vishal_c]

It'll try to open the file just as you would any other time. If someone else has the file open, you'll get the typical prompt and can choose to open it as read only.
You can set it to this to always open as read only:

Dim wb2 As Workbook: Set wb2 = Workbooks.Open(FileName:=FileName2, ReadOnly:=True)

You get the 1004 error because you are trying to have 2 workbooks open with the same name (ex: 2 workbooks both named "First")

hi Thanks a lot, much appreciated. it works

 

[m_vishal_c]

hi Thanks a lot, much appreciated. it works

hi Sorry to disturb you again

you mentioned below

Dim FileName2 As String: FileName2 = "D:\1.0 Projects\First.xlsx"

Dim wb2 As Workbook: Set wb2 = Workbooks.Open(FileName:=FileName2, ReadOnly:=True)

is there any way without open workbook. Reason it asks " you want to save file ? " when this code hits " wb2.close " (Please see previous conversation).

so please guide me. heaps thanks

 

[MARK858]

What happens if you change

wb2.close

to

wb2.close False

 

[m_vishal_c]

What happens if you change

wb2.close

to

wb2.close False

Thanks , it works . much much appreciated .
I am very stress now, sometimes this formula works and sometimes it does not. I am so wondering . big headache. can you please help me . please see below code.

*** this Excel sheet is in Shared mode*****

Private Sub CommandButton3_Click()
Dim FileName3 As String
FileName3 = "D:\users\data.xlsx" '

Dim wb2 As Workbook: Set wb2 =Workbooks.Open(Filename:=FileName3, ReadOnly:=True)
Dim Rng As Range: Set Rng = ThisWorkbook.Worksheets("Destination").Range("AX3:AX4000")
Dim lookupRange As Range: Set lookupRange =wb2.Sheets("Designs Delivered").Range("D2:F4000")
Dim c As Range
On Error Resume Next ' skips over cells in case the vlookupresults in an error
''''''' update only blank cell
'///////// Actual Design Delivery Date
For Each c In Rng
If c.Value = "" Then
c = WorksheetFunction.VLookup(ThisWorkbook.Sheets("Destination").Range("A"& c.Row), lookupRange, 3, False)
'MsgBox c.Row
End If

Next c
‘/////// paste by value

Sheets("Destination").Columns(52).Copy
Sheets("Destination ").Columns(52).PasteSpecialxlPasteValues
wb2.Close False
End Sub

sometime this code works and sometime does not .
I will be much thankful to you.