I have three columns A B and C. In some cases only B has a value. In the example below only the rows 2 and 4 should be considered, and then depending on their value their corresponding cell in the D column is given a certain value (corresponding to the two arrays below).

     A      B       C       D
1    x      y       z
2           b               2
3    t              v
4           e               5
5    g      h       i

I'm not certain what's the best approach for this. I declared the necessary arrays, but I don't know how to meet the "if cell in A and cell in C is empty..." condition.

    Dim Kol() As Variant: Kol = VBA.Array(1,2,3) ' I don't know if it makes sense to include columns A and C here
    Dim Faelle() As Variant: Faelle = VBA.Array( _
        "a", "b", "c", "d", "e")
    Dim Werte() As Variant: Werte = VBA.Array( _
        "1", "2", "3", "4", "5")

CodePudding user response：

Lookup Data: Variant Arrays with Application.Match

Sub LookupData()
    
    Dim Faelle() As Variant: Faelle = VBA.Array("a", "b", "c", "d", "e")
    Dim Werte() As Variant: Werte = VBA.Array(1, 2, 3, 4, 5)

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    
    Dim rCount As Long: rCount = rg.Rows.Count - 1 ' exclude headers
    Dim srg As Range: Set srg = rg.Resize(rCount).Offset(1) ' no headers
    Dim sData() As Variant: sData = srg.Resize(, 3).Value ' first 3 columns
    
    Dim dData() As Variant: ReDim dData(1 To rCount, 1 To 1)
    
    Dim nIndex As Variant, r As Long, rString As String
    
    For r = 1 To rCount
        rString = CStr(sData(r, 1))
        If Len(rString) = 0 Then
            rString = CStr(sData(r, 3))
            If Len(rString) = 0 Then
                rString = CStr(sData(r, 2))
                If Len(rString) > 0 Then
                    nIndex = Application.Match(rString, Faelle, 0)
                    If IsNumeric(nIndex) Then dData(r, 1) = Werte(nIndex - 1)
                End If
            End If
        End If
    Next r
    
    Dim drg As Range: Set drg = srg.Columns(4)
    drg.Value = dData

End Sub

Of course, you can shorten the loop by losing the rString variable and using the And operator in the following way:

For r = 1 To rCount
    If Len(CStr(sData(r, 1))) = 0 And Len(CStr(sData(r, 3))) = 0 _
            And Len(CStr(sData(r, 2))) > 0 Then
        nIndex = Application.Match(CStr(sData(r, 2)), Faelle, 0)
        If IsNumeric(nIndex) Then dData(r, 1) = Werte(nIndex - 1)
    End If
Next r