Changing information only when certain conditions met - Running into problems

[Akeel Mohamed]

Hi there,

I have just started working on Macro's where I work and this is my first one. So far its going well except for one issue that I run into. When I run the Macro if the name of the person is not on the list (Ex. "Simpson, Homer" not on the list) it will use my formula and put the name associated in every line below. I'm not sure what I can do to stop this from happening.

What I want the macro to do is filter for the names and change the information only when its there. Would anyone have any suggestions on how to do this? Thanks for all your help! Looking forward to being an active part of the community

Selection.AutoFilter
ActiveSheet.Range("$A$2:$W$42").AutoFilter Field:=8, Criteria1:=Array( _
"Coordinator, Window", "Arun, Master", Frank, Muhat"), Operator:= _
xlFilterValues
Range("B3").Select
Range(Selection, Selection.End(xlDown)).FormulaR1C1 = "Non Store Mgmt"
ActiveSheet.ShowAllData

ActiveSheet.Range("$A$2:$W$42").AutoFilter Field:=8, Criteria1:=Array( _
"Simpson, Homer", "Ramroller, Sabiitters"), Operator:=xlFilterValues
Range("B3").Select
Range(Selection, Selection.End(xlDown)).FormulaR1C1 = "Store Mgmt"
ActiveSheet.ShowAllData

ActiveSheet.Range("$A$2:$W$42").AutoFilter Field:=8, Criteria1:=Array( _
"Halo, See", "Cab, My"), Operator:=xlFilterValues
Range("B3").Select
Range(Selection, Selection.End(xlDown)).FormulaR1C1 = "Pushers"
ActiveSheet.ShowAllData

ActiveSheet.Range("$A$2:$W$42").AutoFilter Field:=8, Criteria1:=Array( _
"Telling, OnYou", "Pax, Raqmy"), Operator:=xlFilterValues
Range("B3").Select
Range(Selection, Selection.End(xlDown)).FormulaR1C1 = "Sales"
ActiveSheet.ShowAllData

 

[Pablo]

Hi,
If you attach the file it will be easier to help you, but based on my own experience it will be better if you test the filter results 1st, something like this: (insert after each Range("B3").Select)

If Not IsEmpty(Range("B3")) Then
Range(Selection, Selection.End(xlDown)).FormulaR1C1 = "Non Store Mgmt"
End If

though you should work with visible cells only after applying the filter.
Range(Selection, SpecialCells.xlCellTypeVisible)

 

[Smallman]

Looking at this problem slightly differently why don’t you just set up a lookup table in another sheet called “List”. Now just use this formula in B2 and drag down.

=VLOOKUP(H2,List!A:B,2,0)

Or if you absolutely have to use vb, not recommended for such a simple procedure then the code is as follows.

Sub goski()
  Range("B2:B" & Cells(Rows.Count, 8).End(xlUp).Row) = "=VLOOKUP(H2,List!A:B,2,0)"
End Sub

You are already writing all those names out in the VBE so you may as well create the same list in the worksheet. File attached to show workings.

Take care

Smallman

 

[Akeel Mohamed]

Hi guys,

Thanks for the response. I have actually tried Pablo's way but I'm trying to figure out where to use:

Range(Selection, SpecialCells.xlCellTypeVisible)

I think this way would probably be the best right now. I also wanted an idea of what it did. I'm running into problems because when I filter "B3" doesn't always stay "B3" and line 3 could be B47 or something. how would I deal with that issue? Thanks a lot for the help so far guys!