1

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")
maliebina
  • 205
  • 6
  • 2
    Did you need to use VBA & Arrays? This can be done with an in cell formula `=IF(CONCAT(A2:C2) = B2, ROW(B2),"")` Basically if the concatenation of all the cells = what's in B, then all columns in the row are blank. if true get the row number, if false don't output anything. The same logic can be done with VBA & arrays. – JosephC Nov 24 '22 at 19:40
  • @JosephC I just realized I picked bad values for the example. In reality the values are nothing like chars and ints that correspond to the row numbers. And I must use VBA, and I guess arrays are preferable since the sheet can reach the 2k rows – maliebina Nov 24 '22 at 19:43
  • 1
    "is given a certain value" does not mean anything. Your pseudocode, not related to the above example does not clarify anything, too. So, if you really need help you should edit your question and try clearly explain **in words** WHAT YOU TRY ACCOMPLISHING. – FaneDuru Nov 24 '22 at 19:57
  • @FaneDuru the values are included in the array. – maliebina Nov 24 '22 at 20:01
  • Please [edit your question](https://stackoverflow.com/posts/74565379/edit) to clarify it – cybernetic.nomad Nov 24 '22 at 20:03
  • What "values" are included? You show a 2D array, you are talking about tree columns (A, B, C) and show a piece of code with 1D arrays, each of 5 and 3 elements. Where from did you extract `Array(1,2,3)`, `Array( "a", "b", "c", "d", "e")` and `Array( "1", "2", "3", "4", "5")`? Do you really understand what an array is? – FaneDuru Nov 24 '22 at 20:09

1 Answers1

1

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28