# Modify a cell value if two other cells contain certain value



## zack8576 (Jan 5, 2023)

I need to modify some excel files, if column K contains "AO", "COVER", and column N contains "Tampa"
then any "Riser", "Cone", 'Top slab" (column K) with the same item label (column B) gets the letter J at the end of the value in *column D*
so in the screenshot below, rows 2 and 3 will be: F14632J and F14824J

I have a partially working code that does exactly this if N contains Tampa, how do I modify my code to have it look for AO COVER in column K ?








```
Sub CityOfTampa()
    Dim c As Variant
    For Each c In Range("N2:N" & Cells(Rows.Count, 1).End(3).Row)
        If c Like "*Tampa*" Or _
           c Like "*Tampa*" Then Tampa = c.Offset(, -9)
        For Each d In Range("K2:K" & Cells(Rows.Count, 1).End(3).Row)
            If d Like "*4'*" And d Like "*Riser*" Or _
               d Like "*4'*" And d Like "*Top Slab*" Or _
               d Like "*4'*" And d Like "*Cone*" Or _
               d Like "*5'*" And d Like "*Cone*" Then
                If d.Offset(, 3) Like "*Tampa*" Then
                    If Right(d.Offset(, -7), 1) <> "J" Then
                        d.Offset(, -7) = d.Offset(, -7) & "J"
                    End If
                End If
            End If
        Next d
    Next c
End Sub
```


----------



## Alex Blakenburg (Jan 5, 2023)

Do you have any formulas in column D ?
Also how much data (in rows) do you have ?


----------



## zack8576 (Jan 5, 2023)

Alex Blakenburg said:


> Do you have any formulas in column D ?
> Also how much data (in rows) do you have ?


Nope, there is no formula in column D, or in any other cell
the data range from single digit # of rows up to thousands of rows.

I also want to point out, in my code above, the d is defined As Range
this code works fine, it adds J to the end of the value in column D on the relevant rows. 
However it does this under just one criteria, I need to add one more criteria: column K needs to contain *AO* and *COVER*

Any help is appreciated


----------



## Alex Blakenburg (Jan 5, 2023)

Give the below a try:
Note:
If you can rely on the phrase "AO COVER" then just use Like "*AO COVER*"
equally if you can rely on the order of the numeric being before the other search term your could simplify to
`descr Like "*4'*Riser*"`


```
Sub CityOfTampa()

    Dim ws As Worksheet
    Dim rng As Range, arr As Variant
    Dim lastRow As Long, lastCol As Long, i As Long
    Dim descr As String
   
   
    Set ws = ActiveSheet
    With ws
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(2, "A"), .Cells(lastRow, lastCol))
        arr = rng.Value
    End With
   
    Dim dictItemLbl As Object, dictKey As String

    Set dictItemLbl = CreateObject("Scripting.dictionary")
   
    ' Load details range into Dictionary
    For i = 1 To UBound(arr)
        dictKey = arr(i, 2)
        descr = arr(i, 11)
       
        If arr(i, 14) Like "*Tampa*" And descr Like "*AO*" And descr Like "*COVER*" Then
            If Not dictItemLbl.exists(dictKey) Then
                dictItemLbl(dictKey) = i
            End If
        End If
    Next i
   
    ' Get Other rows for selected Items Labels and test for value
    For i = 1 To UBound(arr)
        dictKey = arr(i, 2)
        descr = arr(i, 11)
        If dictItemLbl.exists(dictKey) Then
            If arr(i, 14) Like "*Tampa*" Then               ' Not required if this is always the same for the Item Label
                If descr Like "*4'*" And descr Like "*Riser*" Or _
                    descr Like "*4'*" And descr Like "*Top Slab*" Or _
                    descr Like "*4'*" And descr Like "*Cone*" Or _
                    descr Like "*5'*" And descr Like "*Cone*" Then
                   
                        arr(i, 4) = arr(i, 4) & "J"
                End If

            End If
        End If

    Next i
   
    rng.Columns(4) = Application.Index(arr, 0, 4)
       
End Sub
```


----------



## zack8576 (Jan 5, 2023)

Alex Blakenburg said:


> Give the below a try:
> Note:
> If you can rely on the phrase "AO COVER" then just use Like "*AO COVER*"
> equally if you can rely on the order of the numeric being before the other search term your could simplify to
> ...


Alex, I've tested this a couple of times, in one of the files, it is adding one too many J to the end of the value in only one of the rows.

Before




After




Test file link below








						2022120031 CITY OF TAMPA.csv
					

Shared with Dropbox




					www.dropbox.com
				




Sorry your code is too complicated for me, I dont even know where to start when it comes to debugging it.
Right now I am just trying to understand bits of it....


----------



## zack8576 (Jan 5, 2023)

Alex Blakenburg said:


> Give the below a try:
> Note:
> If you can rely on the phrase "AO COVER" then just use Like "*AO COVER*"
> equally if you can rely on the order of the numeric being before the other search term your could simplify to
> ...


If you dont mind me asking, why did you choose to use dictionary ? Is this the best approach when it comes to solving this type of problems ?


----------



## Alex Blakenburg (Jan 5, 2023)

Ahh if would have been so much quicker if I had your sample data initially.


zack8576 said:


> it is adding one too many J to the end of the value in only one of the rows.


The update loop is only handling each row once, so the double J means there was already a J in your data. We can add a test to only add a J if there isn't already one there.

```
' Change this
'arr(i, 4) = arr(i, 4) & "J"
' To this
If Right(arr(i, 4), 1) <> "J" Then arr(i, 4) = arr(i, 4) & "J"
```



> If you dont mind me asking, why did you choose to use dictionary ? Is this the best approach when it comes to solving this type of problems ?


If your data is sorted so that all the same column B values are together and your AO COVER was either the first or last item for the same item type then we could get away without using something like a dictionary.
Without that the code has to loop through every single line every time it finds a line with AO COVER and Tampa in it. Manageable for a small number of rows but very slow if you have thousands.

The dictionary creates a lookup table that you can access using and index, so the code only needs to loop through the range twice.
Once to select the AO COVER / Tampa lines and create and index of Item Labels.
A second time to check each for to see if it has been selected using the dictionary index and update the record if it meets the additional criteria.


----------



## zack8576 (Jan 5, 2023)

Alex Blakenburg said:


> If your data is sorted so that all the same column B values are together and your AO COVER was either the first or last item for the same item type then we could get away without using something like a dictionary


yes Alex all the same values in column B are grouped together, however the cell that contains AO COVER is not the first nor the last one


----------



## zack8576 (Jan 5, 2023)

Alex Blakenburg said:


> The dictionary creates a lookup table that you can access using and index, so the code only needs to loop through the range twice.
> Once to select the AO COVER / Tampa lines and create and index of Item Labels.
> A second time to check each for to see if it has been selected using the dictionary index and update the record if it meets the additional criteria.


Thanks for explaining this


----------



## zack8576 (Jan 5, 2023)

Alex Blakenburg said:


> The update loop is only handling each row once, so the double J means there was already a J in your data. We can add a test to only add a J if there isn't already one there.


I went back and double checked, you are absolutely right, another subroutine within the same macro is adding a J to this value, before your code is executed.
I think once I figured out the problem with that subroutine, then this problem will go away. I will mark this as the solution. thanks a bunch !


----------



## zack8576 (Jan 5, 2023)

I need to modify some excel files, if column K contains "AO", "COVER", and column N contains "Tampa"
then any "Riser", "Cone", 'Top slab" (column K) with the same item label (column B) gets the letter J at the end of the value in *column D*
so in the screenshot below, rows 2 and 3 will be: F14632J and F14824J

I have a partially working code that does exactly this if N contains Tampa, how do I modify my code to have it look for AO COVER in column K ?








```
Sub CityOfTampa()
    Dim c As Variant
    For Each c In Range("N2:N" & Cells(Rows.Count, 1).End(3).Row)
        If c Like "*Tampa*" Or _
           c Like "*Tampa*" Then Tampa = c.Offset(, -9)
        For Each d In Range("K2:K" & Cells(Rows.Count, 1).End(3).Row)
            If d Like "*4'*" And d Like "*Riser*" Or _
               d Like "*4'*" And d Like "*Top Slab*" Or _
               d Like "*4'*" And d Like "*Cone*" Or _
               d Like "*5'*" And d Like "*Cone*" Then
                If d.Offset(, 3) Like "*Tampa*" Then
                    If Right(d.Offset(, -7), 1) <> "J" Then
                        d.Offset(, -7) = d.Offset(, -7) & "J"
                    End If
                End If
            End If
        Next d
    Next c
End Sub
```


----------



## bebo021999 (Jan 5, 2023)

Dictionary may be  higher level for VBA beginners.
Another approach, without dic, but using variant array with 3 columns
Temp(1 to last row, 1 to 3)
with column 1 for string found, column 2 for AO COVER found, and column 3 for original column D
Then loop the Temp array, combine column 3 with "J" where col 1 was found in col 2
I believe this solution is not slower MUCH than dictionary

```
Option Explicit
Sub Tampa()
Dim lr&, i&, j&, data, temp()
lr = Cells(Rows.Count, "A").End(xlUp).Row ' define last row
data = Range("B2:O" & lr).Value ' data array from column B to last column
ReDim temp(1 To UBound(data), 1 To 3) ' this array to store temporary data
For i = 1 To UBound(data)
    temp(i, 3) = data(i, 3) ' store original Str#
    If data(i, 10) & data(i, 13) Like "* AO COVER* Tampa" Then temp(i, 2) = data(i, 1) ' store Str# where "AO COVER" found
    If data(i, 3) Like "*J" Then
    ElseIf data(i, 10) Like "*4'*Riser*" Or data(i, 10) Like "*4'*Top Slap*" Or _
        data(i, 10) Like "*4'*Cone*" Or data(i, 10) Like "*5'*Cone*" Then temp(i, 1) = data(i, 1) ' store Str# where string found
    End If
Next
For i = 1 To UBound(temp)
    If Not IsEmpty(temp(i, 1)) Then
        For j = 1 To UBound(temp)
            If temp(i, 1) = temp(j, 2) Then
                temp(i, 3) = temp(i, 3) & "J"
                Exit For
            End If
        Next
    End If
Next
Range("D2").Resize(UBound(data), 1).Value = Application.Index(temp, 0, 3)
End Sub
```









						2022120031 CITY OF TAMPA.xlsm
					






					drive.google.com


----------



## zack8576 (Jan 5, 2023)

bebo021999 said:


> Dictionary may be  higher level for VBA beginners.
> Another approach, without dic, but using variant array with 3 columns
> Temp(1 to last row, 1 to 3)
> with column 1 for string found, column 2 for AO COVER found, and column 3 for original column D
> ...


Bebo, thank you. I will run a few tests with this


----------



## Alex Blakenburg (Jan 6, 2023)

I understand that the dictionary is perceived as being not for beginners but if you spend just a little time coming to grips with it I think you will find that it is fact much easier to understand the code than keeping track of multiple arrays and looping through the entire array repeatedly.
Happy to walk you through any bits you get stuck on.
Your example is actually an ideal starting point test case since although a dictionary consists of an index / key AND a value (like a 2 columns array) for this exercise you don't even need the value and all you want is a single column list (aka dictionary key) that you can then access directly using that key to see if it exists.


----------



## zack8576 (Jan 6, 2023)

Alex Blakenburg said:


> I understand that the dictionary is perceived as being not for beginners but if you spend just a little time coming to grips with it I think you will find that it is fact much easier to understand the code than keeping track of multiple arrays and looping through the entire array repeatedly.
> Happy to walk you through any bits you get stuck on.
> Your example is actually an ideal starting point test case since although a dictionary consists of an index / key AND a value (like a 2 columns array) for this exercise you don't even need the value and all you want is a single column list (aka dictionary key) that you can then access directly using that key to see if it exists.


Alex, allow me some time to absorb the dictionary concept, I will get some practice this weekend, if I have any questions I will ask


----------



## zack8576 (Saturday at 8:55 AM)

Alex Blakenburg said:


> I understand that the dictionary is perceived as being not for beginners but if you spend just a little time coming to grips with it I think you will find that it is fact much easier to understand the code than keeping track of multiple arrays and looping through the entire array repeatedly.
> Happy to walk you through any bits you get stuck on.
> Your example is actually an ideal starting point test case since although a dictionary consists of an index / key AND a value (like a 2 columns array) for this exercise you don't even need the value and all you want is a single column list (aka dictionary key) that you can then access directly using that key to see if it exists.


Alex, here are a few questions, see comments in the code


```
Sub CityOfTampa()

    Dim ws As Worksheet
    Dim rng As Range, arr As Variant
    Dim lastRow As Long, lastCol As Long, i As Long
    Dim descr As String 'dictionary always have key and item, descr is the item ?
   
   
    Set ws = ActiveSheet
    With ws
        lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column 'What is the purpose of defining the last column?
        Set rng = .Range(.Cells(2, "A"), .Cells(lastRow, lastCol))
        arr = rng.Value
    End With
   
    Dim dictItemLbl As Object, dictKey As String 'is it a good practice to always define dictionary item as Object ?

    Set dictItemLbl = CreateObject("Scripting.dictionary")
   
    ' Load details range into Dictionary
    For i = 1 To UBound(arr) 'I was googling Ubound, but I am still not 100% clear on what the Ubound does in this subroutine.
        dictKey = arr(i, 2)
        descr = arr(i, 11)
       
        If arr(i, 14) Like "*Tampa*" And descr Like "*AO*" And descr Like "*COVER*" Then
            If Not dictItemLbl.exists(dictKey) Then
                dictItemLbl(dictKey) = i
            End If
        End If
    Next i
   
    ' Get Other rows for selected Items Labels and test for value
    For i = 1 To UBound(arr)
        dictKey = arr(i, 2)
        descr = arr(i, 11)
        If dictItemLbl.exists(dictKey) Then
            If arr(i, 14) Like "*Tampa*" Then               ' Not required if this is always the same for the Item Label
                If descr Like "*4'*" And descr Like "*Riser*" Or _
                    descr Like "*4'*" And descr Like "*Top Slab*" Or _
                    descr Like "*4'*" And descr Like "*Cone*" Or _
                    descr Like "*5'*" And descr Like "*Cone*" Then
                   
                        arr(i, 4) = arr(i, 4) & "J"
                End If

            End If
        End If

    Next i
   
    rng.Columns(4) = Application.Index(arr, 0, 4)
       
End Sub
```


----------



## zack8576 (Saturday at 9:00 AM)

Alex Blakenburg said:


> I understand that the dictionary is perceived as being not for beginners but if you spend just a little time coming to grips with it I think you will find that it is fact much easier to understand the code than keeping track of multiple arrays and looping through the entire array repeatedly.
> Happy to walk you through any bits you get stuck on.
> Your example is actually an ideal starting point test case since although a dictionary consists of an index / key AND a value (like a 2 columns array) for this exercise you don't even need the value and all you want is a single column list (aka dictionary key) that you can then access directly using that key to see if it exists.


I want to practice using dictionary a couple of times, and I think I can nail the concept pretty well. 
the purpose of this block of code is simple: for anything with these values in column D, I need to make sure column E always says "No"
you think this would be a good dictionary practice ?

```
Sub ChangeYesToNo()
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i2 = 2 To lastRow
        'TS 58
        If Range("D" & i2).Value Like "*F14102*" Or _
           Range("D" & i2).Value Like "*F14102M*" Or _
           Range("D" & i2).Value Like "*F14102X*" Or _
           Range("D" & i2).Value Like "*F14103*" Or _
           Range("D" & i2).Value Like "*F14103G*" Or _
           Range("D" & i2).Value Like "*F14103MX*" Or _
           Range("D" & i2).Value Like "*F14103X*" Or _
           Range("D" & i2).Value Like "*F14106*" Or _
           Range("D" & i2).Value Like "*F14103M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 64
        If Range("D" & i2).Value Like "*F14502*" Or _
           Range("D" & i2).Value Like "*F14502C*" Or _
           Range("D" & i2).Value Like "*F14502M*" Or _
           Range("D" & i2).Value Like "*F14503*" Or _
           Range("D" & i2).Value Like "*F14503G*" Or _
           Range("D" & i2).Value Like "*F14503M*" Or _
           Range("D" & i2).Value Like "*F14503MX*" Or _
           Range("D" & i2).Value Like "*F14503X*" Or _
           Range("D" & i2).Value Like "*F14503X36*" Or _
           Range("D" & i2).Value Like "*F12350*" Or _
           Range("D" & i2).Value Like "*F14507*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 64X64
        If Range("D" & i2).Value Like "*F12450*" Or _
           Range("D" & i2).Value Like "*F12450C*" Or _
           Range("D" & i2).Value Like "*F12450F*" Or _
           Range("D" & i2).Value Like "*F12450SVJ*" Or _
           Range("D" & i2).Value Like "*F12450BWI*" Or _
           Range("D" & i2).Value Like "*F12450M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 64X94
        If Range("D" & i2).Value Like "*F13140*" Or _
           Range("D" & i2).Value Like "*F13145*" Or _
           Range("D" & i2).Value Like "*F13145R*" Or _
           Range("D" & i2).Value Like "*F13145B*" Or _
           Range("D" & i2).Value Like "*F13145C*" Or _
           Range("D" & i2).Value Like "*F13145D*" Or _
           Range("D" & i2).Value Like "*F13145E*" Or _
           Range("D" & i2).Value Like "*F13145F*" Or _
           Range("D" & i2).Value Like "*F13145SVJ*" Or _
           Range("D" & i2).Value Like "*F13145BWI*" Or _
           Range("D" & i2).Value Like "*F13145M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 64X112
        If Range("D" & i2).Value Like "*F13240*" Or _
           Range("D" & i2).Value Like "*F13245*" Or _
           Range("D" & i2).Value Like "*F13245R*" Or _
           Range("D" & i2).Value Like "*F13245C*" Or _
           Range("D" & i2).Value Like "*F13245D*" Or _
           Range("D" & i2).Value Like "*F13245E*" Or _
           Range("D" & i2).Value Like "*F13245F*" Or _
           Range("D" & i2).Value Like "*F13245G*" Or _
           Range("D" & i2).Value Like "*F13245SVJ*" Or _
           Range("D" & i2).Value Like "*F13245BWI*" Or _
           Range("D" & i2).Value Like "*F13245M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 76X76
        If Range("D" & i2).Value Like "*F13340*" Or _
           Range("D" & i2).Value Like "*F13345*" Or _
           Range("D" & i2).Value Like "*F13345R*" Or _
           Range("D" & i2).Value Like "*F13345C*" Or _
           Range("D" & i2).Value Like "*F13345D*" Or _
           Range("D" & i2).Value Like "*F13345E*" Or _
           Range("D" & i2).Value Like "*F13345F*" Or _
           Range("D" & i2).Value Like "*F13345SVJ*" Or _
           Range("D" & i2).Value Like "*F13345BWI*" Or _
           Range("D" & i2).Value Like "*F13345M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 88X88
        If Range("D" & i2).Value Like "*F13540*" Or _
           Range("D" & i2).Value Like "*F13545*" Or _
           Range("D" & i2).Value Like "*F13545R*" Or _
           Range("D" & i2).Value Like "*F13545C*" Or _
           Range("D" & i2).Value Like "*F13545D*" Or _
           Range("D" & i2).Value Like "*F13545E*" Or _
           Range("D" & i2).Value Like "*F13545F*" Or _
           Range("D" & i2).Value Like "*F13545G*" Or _
           Range("D" & i2).Value Like "*F13545SVJ*" Or _
           Range("D" & i2).Value Like "*F13545BWI*" Or _
           Range("D" & i2).Value Like "*F13545M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 88X112
        If Range("D" & i2).Value Like "*F13640*" Or _
           Range("D" & i2).Value Like "*F13645*" Or _
           Range("D" & i2).Value Like "*F13645R*" Or _
           Range("D" & i2).Value Like "*F13645B*" Or _
           Range("D" & i2).Value Like "*F13645C*" Or _
           Range("D" & i2).Value Like "*F13645D*" Or _
           Range("D" & i2).Value Like "*F13645E*" Or _
           Range("D" & i2).Value Like "*F13645F*" Or _
           Range("D" & i2).Value Like "*F13645G*" Or _
           Range("D" & i2).Value Like "*F13645H*" Or _
           Range("D" & i2).Value Like "*F13645SVJ*" Or _
           Range("D" & i2).Value Like "*F13645BWI*" Or _
           Range("D" & i2).Value Like "*F13645M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 112X112
        If Range("D" & i2).Value Like "*F13740*" Or _
           Range("D" & i2).Value Like "*F13745*" Or _
           Range("D" & i2).Value Like "*F13745R*" Or _
           Range("D" & i2).Value Like "*F13745C*" Or _
           Range("D" & i2).Value Like "*F13745D*" Or _
           Range("D" & i2).Value Like "*F13745E*" Or _
           Range("D" & i2).Value Like "*F13745F*" Or _
           Range("D" & i2).Value Like "*F13745G*" Or _
           Range("D" & i2).Value Like "*F13745H*" Or _
           Range("D" & i2).Value Like "*F13745SVJ*" Or _
           Range("D" & i2).Value Like "*F13745BWI*" Or _
           Range("D" & i2).Value Like "*F13745M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 76 DIA
        If Range("D" & i2).Value Like "*F15910*" Or _
           Range("D" & i2).Value Like "*F15920*" Or _
           Range("D" & i2).Value Like "*F15920R*" Or _
           Range("D" & i2).Value Like "*F15920C*" Or _
           Range("D" & i2).Value Like "*F15920F*" Or _
           Range("D" & i2).Value Like "*F15920M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 88 DIA
        If Range("D" & i2).Value Like "*F16910*" Or _
           Range("D" & i2).Value Like "*F16920*" Or _
           Range("D" & i2).Value Like "*F16920R*" Or _
           Range("D" & i2).Value Like "*F16920C*" Or _
           Range("D" & i2).Value Like "*F16920D*" Or _
           Range("D" & i2).Value Like "*F16920E*" Or _
           Range("D" & i2).Value Like "*F16920F*" Or _
           Range("D" & i2).Value Like "*F16920SVJ*" Or _
           Range("D" & i2).Value Like "*F16920BWI*" Or _
           Range("D" & i2).Value Like "*F16920X*" Or _
           Range("D" & i2).Value Like "*F16920M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 116 DIA
        If Range("D" & i2).Value Like "*F18910*" Or _
           Range("D" & i2).Value Like "*F18920*" Or _
           Range("D" & i2).Value Like "*F18920R*" Or _
           Range("D" & i2).Value Like "*F18920C*" Or _
           Range("D" & i2).Value Like "*F18920D*" Or _
           Range("D" & i2).Value Like "*F18920E*" Or _
           Range("D" & i2).Value Like "*F18920F*" Or _
           Range("D" & i2).Value Like "*F18920SVJ*" Or _
           Range("D" & i2).Value Like "*F18920BWI*" Or _
           Range("D" & i2).Value Like "*F18920M*" Then
            Range("E" & i2).Value = "No"
        End If
        'TS 144 DIA
        If Range("D" & i2).Value Like "*F19910*" Or _
           Range("D" & i2).Value Like "*F19920*" Or _
           Range("D" & i2).Value Like "*F19920R*" Or _
           Range("D" & i2).Value Like "*F19920C*" Or _
           Range("D" & i2).Value Like "*F19920D*" Or _
           Range("D" & i2).Value Like "*F19920E*" Or _
           Range("D" & i2).Value Like "*F19920F*" Or _
           Range("D" & i2).Value Like "*F19920SVJ*" Or _
           Range("D" & i2).Value Like "*F19920BWI*" Or _
           Range("D" & i2).Value Like "*F19920M*" Then
            Range("E" & i2).Value = "No"
        End If
          'P TOPS
        If Range("D" & i2).Value Like "*F14016*" Or _
           Range("D" & i2).Value Like "*F14017*" Or _
           Range("D" & i2).Value Like "*F14015*" Or _
           Range("D" & i2).Value Like "*F14009*" Or _
           Range("D" & i2).Value Like "*F14011*" Or _
           Range("D" & i2).Value Like "*F14013*" Or _
           Range("D" & i2).Value Like "*F14012*" Or _
           Range("D" & i2).Value Like "*F14010*" Or _
           Range("D" & i2).Value Like "*F14020*" Then
            Range("E" & i2).Value = "No"
        End If
          'CONCENTRIC CONES
        If Range("D" & i2).Value Like "*F14318*" Or _
           Range("D" & i2).Value Like "*F14318G*" Or _
           Range("D" & i2).Value Like "*F14318X*" Or _
           Range("D" & i2).Value Like "*F14319*" Or _
           Range("D" & i2).Value Like "*F14324*" Or _
           Range("D" & i2).Value Like "*F14324G*" Or _
           Range("D" & i2).Value Like "*F14324X*" Or _
           Range("D" & i2).Value Like "*F14324Y*" Or _
           Range("D" & i2).Value Like "*F14325*" Or _
           Range("D" & i2).Value Like "*F14332*" Or _
           Range("D" & i2).Value Like "*F14332X*" Or _
           Range("D" & i2).Value Like "*F14333*" Or _
           Range("D" & i2).Value Like "*F14336*" Or _
           Range("D" & i2).Value Like "*F14336G*" Or _
           Range("D" & i2).Value Like "*F14336X*" Or _
           Range("D" & i2).Value Like "*F14337*" Or _
           Range("D" & i2).Value Like "*F14348*" Or _
           Range("D" & i2).Value Like "*F14348G*" Or _
           Range("D" & i2).Value Like "*F14348X*" Then
            Range("E" & i2).Value = "No"
        End If
          'ECCENTRIC CONES
        If Range("D" & i2).Value Like "*F14418*" Or _
           Range("D" & i2).Value Like "*F14418G*" Or _
           Range("D" & i2).Value Like "*F14418X*" Or _
           Range("D" & i2).Value Like "*F14418Y*" Or _
           Range("D" & i2).Value Like "*F14418Z*" Or _
           Range("D" & i2).Value Like "*F14419*" Or _
           Range("D" & i2).Value Like "*F14424*" Or _
           Range("D" & i2).Value Like "*F14424G*" Or _
           Range("D" & i2).Value Like "*F14424X*" Or _
           Range("D" & i2).Value Like "*F14424Y*" Or _
           Range("D" & i2).Value Like "*F14425*" Or _
           Range("D" & i2).Value Like "*F14436*" Or _
           Range("D" & i2).Value Like "*F14436G*" Or _
           Range("D" & i2).Value Like "*F14436X*" Or _
           Range("D" & i2).Value Like "*F14436Y*" Or _
           Range("D" & i2).Value Like "*F14437*" Or _
           Range("D" & i2).Value Like "*F14448*" Or _
           Range("D" & i2).Value Like "*F14448G*" Or _
           Range("D" & i2).Value Like "*F14448X*" Then
            Range("E" & i2).Value = "No"
        End If
     Next i2
End Sub
```


----------



## Alex Blakenburg (Saturday at 9:19 AM)

zack8576 said:


> Dim descr As String 'dictionary always have key and item, descr is the item ?


No

```
dictKey = arr(i, 2)   ' Column 2 of your data "Type" is being used as the dictionary key -
        descr = arr(i, 11)    ' Column 11 is the description and since this is being tested in If statements multiple times it was clearer and shorter to identify the field
        dictItemLbl(dictKey) = i  ' "i" the row counter is being put in the item / value field of the dictionary but we are not using this for this application
```



zack8576 said:


> Dim dictItemLbl As Object, dictKey As String 'is it a good practice to always define dictionary item as Object ?
> Set dictItemLbl = CreateObject("Scripting.dictionary")


On the forum most people will do it this way because it requires less explanation.
You can define the dict variable as Dictionary but you would need to select the Scripting Reference under Tools > References
This will make it easier to write your code and run faster but can cause issues if you distribute the code to someone with a their DLLs stored in a different location or a different version (not usually a problem since your IT dept instals software in a standard manner).
Terminology Dim as Dictionary using the Reference library is called Early Binding, the CreateObject method is called Late Binding







zack8576 said:


> For i = 1 To UBound(arr) 'I was googling Ubound, but I am still not 100% clear on what the Ubound does in this subroutine.


We are loading all the rows into an array. We then loop through the array from the first record aka "1" to the last.
We can get the last index number of the array using Ubound(array_name).


----------



## Alex Blakenburg (Saturday at 9:28 AM)

zack8576 said:


> you think this would be a good dictionary practice ?


No.
Dictionary's only add value if there is an exact match and your example is all based on if statements using partial matches (wildcards).
In the original problem we were finding a description with AO COVER. The dictionary did not help with finding AO COVER.
What it helped with was the next stage. The row with AO COVER had a Type in Column B. We wanted to capture that type in the dictionary so that we could then loop through the rows a second time and further process any row that had that same type by checking the type for each row to see if we held it in the dictionary.


----------



## zack8576 (Saturday at 9:33 AM)

Alex Blakenburg said:


> We are loading all the rows into an array. We then loop through the array from the first record aka "1" to the last.
> We can get the last index number of the array using Ubound(array_name)


this makes sense, thank you


----------



## Alex Blakenburg (Saturday at 9:34 AM)

Paul Kelly (Excel Macro Mastery) has a series of 4 videos if you wanted to try them.


----------



## zack8576 (Jan 5, 2023)

I need to modify some excel files, if column K contains "AO", "COVER", and column N contains "Tampa"
then any "Riser", "Cone", 'Top slab" (column K) with the same item label (column B) gets the letter J at the end of the value in *column D*
so in the screenshot below, rows 2 and 3 will be: F14632J and F14824J

I have a partially working code that does exactly this if N contains Tampa, how do I modify my code to have it look for AO COVER in column K ?








```
Sub CityOfTampa()
    Dim c As Variant
    For Each c In Range("N2:N" & Cells(Rows.Count, 1).End(3).Row)
        If c Like "*Tampa*" Or _
           c Like "*Tampa*" Then Tampa = c.Offset(, -9)
        For Each d In Range("K2:K" & Cells(Rows.Count, 1).End(3).Row)
            If d Like "*4'*" And d Like "*Riser*" Or _
               d Like "*4'*" And d Like "*Top Slab*" Or _
               d Like "*4'*" And d Like "*Cone*" Or _
               d Like "*5'*" And d Like "*Cone*" Then
                If d.Offset(, 3) Like "*Tampa*" Then
                    If Right(d.Offset(, -7), 1) <> "J" Then
                        d.Offset(, -7) = d.Offset(, -7) & "J"
                    End If
                End If
            End If
        Next d
    Next c
End Sub
```


----------



## zack8576 (Saturday at 9:36 AM)

Alex Blakenburg said:


> No.
> Dictionary's only add value if there is an exact match and your example is all based on if statements using partial matches (wildcards).
> In the original problem we were finding a description with AO COVER. The dictionary did not help with finding AO COVER.
> What it helped with was the next stage. The row with AO COVER had a Type in Column B. We wanted to capture that type in the dictionary so that we could then loop through the rows a second time and further process any row that had that same type by checking the type for each row to see if we held it in the dictionary.


Okay, thank you. The long block of code is working just fine right. I just wanted to see if there is a way to code it shorter. I will leave it the way it is for now .


----------



## zack8576 (Saturday at 9:36 AM)

Alex Blakenburg said:


> Paul Kelly (Excel Macro Mastery) has a series of 4 videos if you wanted to try them.


This is gold, thanks again


----------

