How to apply loop and condition statement in visible cells?

[dmadhup]

hello,

So far I have this code. but not working for visible cells. Any idea, please!

For Each j In Range("A2:FL" & lastRow).SpecialCells(xlCellTypeVisible)
    ii = j.Row
    If Rows(ii).EntireRow.Visible = True Then
        If Range("V") <> "" Or Range("V") <> vbNullString Then _
            If UCase(Range("V" & ii)) Like "*360-*" _
                Or UCase(Range("V" & ii)) Like "*AMG-*" _
                Or UCase(Range("V" & ii)) Like "*BNK-*" _
                Or UCase(Range("V" & ii)) Like "*BRU-*" _
                Or UCase(Range("V" & ii)) Like "*EUG-*" _
                Or UCase(Range("V" & ii)) Like "*FIN-*" _
                Or UCase(Range("V" & ii)) Like "*KLA-*" _
                Or UCase(Range("V" & ii)) Like "*MIA-*" _
                Or UCase(Range("V" & ii)) Like "*VIA-*" _
                Range("FK" & ii) = "EM"


            If UCase(Range("V" & ii)) Like "*G-*" Then _
                Range("FK" & ii) = "FRE"
    End If
Next j

 

[Fluff]

You need to add the row references on this line

If Range("V") <> "" Or Range("V") <> vbNullString Then _

the same way you have in the rest of the code.

 

[dmadhup]

Sorry, I have added like this, still no success

If Range("V" & ii) <> "" Or Range("V" & ii) <> vbNullString Then _

 

[Fluff]

Is this what you want?

For Each j In Range("v2:v" & 783).SpecialCells(xlCellTypeVisible)
   ii = j.Row
   If Range("V" & ii) <> "" Or Range("V" & ii) <> vbNullString Then
      If UCase(Range("V" & ii)) Like "*360-*" _
            Or UCase(Range("V" & ii)) Like "*AMG-*" _
            Or UCase(Range("V" & ii)) Like "*BNK-*" _
            Or UCase(Range("V" & ii)) Like "*BRU-*" _
            Or UCase(Range("V" & ii)) Like "*EUG-*" _
            Or UCase(Range("V" & ii)) Like "*FIN-*" _
            Or UCase(Range("V" & ii)) Like "*KLA-*" _
            Or UCase(Range("V" & ii)) Like "*MIA-*" _
            Or UCase(Range("V" & ii)) Like "*VIA-*" Then
         Range("FK" & ii) = "EM"
      ElseIf UCase(Range("V" & ii)) Like "*G-*" Then
            Range("FK" & ii) = "FRE"
      End If
   End If
Next j

 

[dmadhup]

Fluff:
I am checking the word that contains AMG, BNK, etc. in column V. If found then write EM in column FK.
Similarly, If contains G-45464 or G-958585, etc then write FRE in column FK

 

[Fluff]

Yes, but does the modified code work?

 

[dmadhup]

Nope.
It works if I break the code using two for loop. Don't know why...

For Each j In Range("v2:v" & 783).SpecialCells(xlCellTypeVisible)
ii = j.Row
If Range("V" & ii) <> "" Or Range("V" & ii) <> vbNullString Then
If UCase(Range("V" & ii)) Like "*360-*" _
Or UCase(Range("V" & ii)) Like "*AMG-*" _
Or UCase(Range("V" & ii)) Like "*BNK-*" _
Or UCase(Range("V" & ii)) Like "*BRU-*" _
Or UCase(Range("V" & ii)) Like "*EUG-*" _
Or UCase(Range("V" & ii)) Like "*FIN-*" _
Or UCase(Range("V" & ii)) Like "*KLA-*" _
Or UCase(Range("V" & ii)) Like "*MIA-*" _
Or UCase(Range("V" & ii)) Like "*VIA-*" Then
Range("FK" & ii) = "EM"
End If
Next j


For Each j In Range("v2:v" & 783).SpecialCells(xlCellTypeVisible)
ii = j.Row
If Range("V" & ii) <> "" Or Range("V" & ii) <> vbNullString Then
If UCase(Range("V" & ii)) Like "*G-*" Then _
Range("FK" & ii) = "FRE"


End If
Next j

 

[Fluff]

What should the expected outcome for cases like "*EUG-*" & "*AMG-*"
They fit both patterns

 

[dmadhup]

Hi Fluff,

In that column, the data is like EUG-785463, EUG-3654, AMG-8745, AMG-1234.

For this reason, I just look for initial 3 letters. If matches then tell the program to do this....

 

[Fluff]

Should those values put "EM", or "FRE" in col FK, as they match both "*EUG-*" and "*G-*"