vba help - Dictionary Key - avoid #N/A and Blank cells

[Mallesh23]

Hi Team,

How to avoid #N/A and Blanks cells in Dictionary Keys.

Column A and B cells are keys after joining both the cells.
Column C are dictionary Items Date Columns.

Genuine Dictionary keys and Items shown in Column E and F.

Below is a Sample data expected output is in Column E and F.

Book6
	A	B	C	D	E	F
1	Invoice	Receipt	Date		Genuine Dictionary Key	Genuine Dictionary Items
2	1000	R_1	30/04/2019		1000|R_1	30/04/2019
3	2000	R_2	07/05/2019		2000|R_2	07/05/2019
4	Not available	Not available	11/05/2019		5000|300	11/01/2020
5	Not available	Not available	15/06/2019		6000|R_4	11/01/2020
6	3000	#N/A	05/08/2019
7	#N/A	#N/A	05/08/2019
8	#N/A	80000	05/08/2019
9	4000		27/09/2019
10			14/12/2019
11	5000	300	11/01/2020
12	6000	R_4	11/01/2020
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=A2&"|"&B2
E4:E5	E4	=A11&"|"&B11

Below is my attempted Code.

Option Explicit

Sub Dictionary_Key_issue()

    Dim arr As Variant
    Dim dict As New Scripting.Dictionary
    
    arr = Range("A2:C12").Value
    
    Dim i As Long
    Dim skey As String
    
    'Store into Dictionary
    
    With dict
    For i = 2 To 12
        skey = arr(i, 1) & "!" & arr(i, 2)  'avoid #N/A , Blanks here
        
        If Not .Exists(skey) Then
            .Add skey, arr(i, 3)
                       
        End If
    Next i
End With


End Sub

Thanks
mg

 

[nemmi69]

Does this help?

Sub Dictionary_Key()
Dim Rg As Range
Dim Cell As Range

Set Rg = Range("A2:A12")

For Each Cell In Rg
 If Evaluate("ISBLANK(" & Cell.Address & ")") = False And Application.WorksheetFunction.IsNA(Cell) = False Then
        Range("C" & Cell.Row).Value = Range("A" & Cell.Row).Value & "!" & Range("B" & Cell.Row).Value
End If
Next

End Sub

 

[Domenic]

To ignore blank cells and cells that contain either "Not available" or #N/A, you can use the following code...

            If Not IsError(arr(i, 1)) And Not IsError(arr(i, 2)) Then
                If Len(arr(i, 1)) > 0 And Len(arr(i, 2)) > 0 Then
                    If LCase(arr(i, 1)) <> "not available" And LCase(arr(i, 2)) <> "not available" Then
                        skey = arr(i, 1) & "!" & arr(i, 2)  'avoid #N/A , Blanks here
                        
                        If Not .Exists(skey) Then
                            .Add skey, arr(i, 3)
                        End If
                    End If
                End If
            End If

By the way, you'll get a subscript of range error in your For/Next loop since the lower and upper bounds are actually 1 and 11, respectively. To avoid such errors, use LBound and Ubound to automatically calculate these bounds...

For i = LBound(arr) To UBound(arr)

And, of course, to transfer the result to your worksheet...

    With dict
        Range("E2").Resize(.Count).Value = Application.Transpose(.Keys)
        Range("F2").Resize(.Count).Value = Application.Transpose(.Items)
    End With

Here's the complete code...

Option Explicit

Sub Dictionary_Key_issue()

    Dim arr As Variant
    Dim dict As New Scripting.Dictionary
    
    arr = Range("A2:C12").Value
    
    Dim i As Long
    Dim skey As String
    
    'Store into Dictionary
    
    With dict
        For i = LBound(arr) To UBound(arr)
            If Not IsError(arr(i, 1)) And Not IsError(arr(i, 2)) Then
                If Len(arr(i, 1)) > 0 And Len(arr(i, 2)) > 0 Then
                    If LCase(arr(i, 1)) <> "not available" And LCase(arr(i, 2)) <> "not available" Then
                        skey = arr(i, 1) & "!" & arr(i, 2)  'avoid #N/A , Blanks here
                        
                        If Not .Exists(skey) Then
                            .Add skey, arr(i, 3)
                        End If
                    End If
                End If
            End If
        Next i
    End With
    
    With dict
        Range("E2").Resize(.Count).Value = Application.Transpose(.Keys)
        Range("F2").Resize(.Count).Value = Application.Transpose(.Items)
    End With


End Sub

Hope this helps!

 

[Mallesh23]

Hi Domenic,

Superb !! This is really very nice piece of code. I was struggling a lot in this.

Millions of thanks for your help. Thanks ?


one more query ,

if I want avoid #N/A , via cells/range method.

If Not IsError(arr(i, 1)) And Not IsError(arr(i, 2)) Then .... what will be code here


Thanks
mg

 

[Domenic]

If you were to reference the cells directly...

    If Not IsError(Cells(2, "a").Value) And Not IsError(Cells(2, "b").Value) Then

Is this what you mean?

 

[Mallesh23]

Hi Domenic,

Right! this is what I was expecting, thanks once again for your help !


Thanks
mg

 

[Domenic]

You're very welcome, glad I could help.

Cheers!