none
Check whether the cell in column is empty RRS feed

  • Question

  • Hi team,

    Below I have a macro which honestly works great! However I would like to have an additional condtion to check whether the cell in column is empty and then perfrom vlookup. So it would goes like this:

    Check if cell in E3 is empty, then perfrom vlookup below, then go to next cell - E4 and do the same, check if cell is empty and then perfrom vlookup.

    I want to avoid to performing vlookup if there will be already some data from previous vlookup.

    Thank you!

    Best regards

    Jan Lechnýř

    Sub ADDCLM()
    On Error Resume Next
    Dim Dept_Row As Long
    Dim Dept_Clm As Long
    Table1 = Sheet1.Range("A3:A13")
    Table2 = Sheet1.Range("H3:I13") 
    Dept_Row = Sheet1.Range("E3").Row
    Dept_Clm = Sheet1.Range("E3").Column
    For Each cl In Table1
      Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
      Dept_Row = Dept_Row + 1
    Next cl
    MsgBox "Done"
    End Sub


    • Edited by Jan Lechnýř Sunday, January 5, 2014 11:43 AM editing the pasted code
    Sunday, January 5, 2014 11:42 AM

Answers

  • Try this version:

    Sub ADDCLM()
      On Error Resume Next
      Dim Dept_Row As Long
      Dim Dept_Clm As Long
      Dim Table1
      Dim Table2
      Dim cl
      Table1 = Sheet1.Range("A3:A13")
      Table2 = Sheet1.Range("H3:I13")
      Dept_Row = Sheet1.Range("E3").Row
      Dept_Clm = Sheet1.Range("E3").Column
      For Each cl In Table1
        If Sheet1.Cells(Dept_Row, Dept_Clm).Value = "" Then
          Sheet1.Cells(Dept_Row, Dept_Clm).Value = _
            Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
        End If
        Dept_Row = Dept_Row + 1
      Next cl
      MsgBox "Done"
    End Sub

    Alternatively, define Table1 and Table2 as ranges, and use:

    Sub ADDCLM()
      On Error Resume Next
      Dim Table1 As Range
      Dim Table2 As Range
      Dim cl As Range
      Set Table1 = Sheet1.Range("A3:A13")
      Set Table2 = Sheet1.Range("H3:I13")
      For Each cl In Table1
        If cl.Offset(0, 4).Value = "" Then
          cl.Offset(0, 4).Value = _
            Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
        End If
      Next cl
      MsgBox "Done"
    End Sub

    This version doesn't use the variables Dept_Row and Dept_Clm.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Jan Lechnýř Sunday, January 5, 2014 2:49 PM
    Sunday, January 5, 2014 2:00 PM

All replies

  • Change the line

      Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)

    to

      If cl.Offset(0, 4) = "" Then
        Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
      End If


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, January 5, 2014 12:00 PM
  • Hi Hans,

    Thank you for the reply - I've changed the line and there seems to be no change, the macro still overwrites the old data in column E.

    Thank you.

    Best regards

    Jan Lechnýř

    Sunday, January 5, 2014 12:44 PM
  • Try this version:

    Sub ADDCLM()
      On Error Resume Next
      Dim Dept_Row As Long
      Dim Dept_Clm As Long
      Dim Table1
      Dim Table2
      Dim cl
      Table1 = Sheet1.Range("A3:A13")
      Table2 = Sheet1.Range("H3:I13")
      Dept_Row = Sheet1.Range("E3").Row
      Dept_Clm = Sheet1.Range("E3").Column
      For Each cl In Table1
        If Sheet1.Cells(Dept_Row, Dept_Clm).Value = "" Then
          Sheet1.Cells(Dept_Row, Dept_Clm).Value = _
            Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
        End If
        Dept_Row = Dept_Row + 1
      Next cl
      MsgBox "Done"
    End Sub

    Alternatively, define Table1 and Table2 as ranges, and use:

    Sub ADDCLM()
      On Error Resume Next
      Dim Table1 As Range
      Dim Table2 As Range
      Dim cl As Range
      Set Table1 = Sheet1.Range("A3:A13")
      Set Table2 = Sheet1.Range("H3:I13")
      For Each cl In Table1
        If cl.Offset(0, 4).Value = "" Then
          cl.Offset(0, 4).Value = _
            Application.WorksheetFunction.VLookup(cl, Table2, 2, False)
        End If
      Next cl
      MsgBox "Done"
    End Sub

    This version doesn't use the variables Dept_Row and Dept_Clm.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Jan Lechnýř Sunday, January 5, 2014 2:49 PM
    Sunday, January 5, 2014 2:00 PM
  • Hi Hans,

    Works great, that is what I'm looked for!

    Thank you!

    Regards

    Jan Lechnýř

    Sunday, January 5, 2014 2:49 PM