VBA Vlookup only if cell in blank

[VbaHell]

Hello all

I am trying to do the following

If the cell range "K" is blank complete a Vlookup
If the cell range "K" has data in do not perform a Vlookup so that the data remains in the cell, any help on this would be great please.

With Range("K1:K" & LastRow)
        .FormulaR1C1 = "=VLOOKUP(RC[-10],Data!C[-10]:C[3],14,FALSE)"
        .Value = .Value
    End With

This is some code that two of our Excel forum members helped me with for another issue which is similar but works on the cell in the sheet called "Data" being blank and I have tried to modify this

With Sheets("Data")
        arr = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 14).Value
    End With
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        dic(arr(x, 1)) = arr(x, 14)
    Next x
    
    With Sheets("Comments")
        arr = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 11).Value
        For x = LBound(arr, 1) To UBound(arr, 1)
            If dic.exists(arr(x, 1)) Then arr(x, 11) = dic(arr(x, 1))
        Next x
        
        .Cells(1, 11).Resize(UBound(arr)).Value = Application.Index(arr, 0, 11)
    End With

 

[Fluff]

Is this what you want?

With Sheets("Comments")
    arr = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 11).Value
    For x = LBound(arr, 1) To UBound(arr, 1)
        If arr(x, 11) = "" Then
           If dic.exists(arr(x, 1)) Then arr(x, 11) = dic(arr(x, 1))
        End If
    Next x
    
    .Cells(1, 11).Resize(UBound(arr)).Value = Application.Index(arr, 0, 11)
End With

 

[VbaHell]

Hi Fluff

The vlookup bring in a date value, first run of this macro is good, when i run it again it reverses the dd/mm/yyyy to mm/dd/yyyy
I need it that if there is already a date then do not complete the vlookup

 

[Fluff]

If it pulls the dates correctly first time round I don't know why it's swapping to US dates when you re-run it.

 

[VbaHell]

I only need the data could the code bring it over as text instead, this way I would not have an issue with a date

 

[Fluff]

Try

arr = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 11)[COLOR=#ff0000].Text[/COLOR]

 

[VbaHell]

Sorry Fluff

I get a runtime error 13 "Type Mismatch" on

[LEFT][COLOR=#333333][FONT=monospace]arr = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 11)[/FONT][/COLOR][COLOR=#ff0000][COLOR=#ff0000][FONT=monospace].Text[/FONT][/COLOR][/COLOR][/LEFT]

 

[Fluff]

Must admit I wasn;t sure if that would work & couldn't test as Xl is a bit busy running something.

I'll have a look later

 

[Fluff]

How about

With Sheets("Index")
   arr = .Cells(1, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 11).Value
   For x = LBound(arr, 1) To UBound(arr, 1)
      If dic.exists(arr(x, 1)) Then arr(x, 11) = CLng(dic(arr(x, 1)))
   Next x
   
  .Cells(1, 11).Resize(UBound(arr)).Value = Application.Index(arr, 0, 11)
   
End With

 

[VbaHell]

Wow, Fluff your a genius, works great
Thank you so much