Filter column and put info into next column for celected cells

Resolved · Low Priority · Version 2003

Thu 7 Mar 2013, 13:06 Delegate Elena said...

Elena has attended:
Excel VBA Intro Intermediate course

Filter column and put info into next column for celected cells

Hi,
I have a spreadsheet. I would like to filter on column 51 and select any rows begins with Dresden. (this part is OK). Then I would like to put text "From Dresden" for all selected cells into column 53. Unfilter.
Then filter on column 52, select begins with Dresden and put text "To Dresden" against selected cells into column 53.

Range "BC:BC" is wrong. Please help.

My MACRO is:

Sub Dresden_Margin()
' Dresden_Margin Macro

Selection.AutoFilter Field:=51, Criteria1:="=*dresden*", Operator:=xlAnd
Range("BC:BC").Select
ActiveCell.FormulaR1C1 = "DR From"
Range("BC:BC").Select
Selection.FillDown
Selection.AutoFilter Field:=51
Selection.AutoFilter Field:=52, Criteria1:="=*dresden*", Operator:=xlAnd
Range("BC").Select
ActiveCell.FormulaR1C1 = "DR To"
Range("BC").Select
Selection.FillDown
Selection.AutoFilter Field:=52
End Sub

Thu 7 Mar 2013, 14:10 Trainer Gary said...

RE: Filter column and put info into next column for celected cel

Hi Elena

Thanks for getting in touch. If your macro is just trying to write a phrase next to Dresden cells then something like this would suffice, doing away with the Autofilter:

Range("AY1").Select ' this relates to field 51, row 1 in your data

Do Until ActiveCell = ""

If InStr(1, ActiveCell, "dresden") Then
ActiveCell.Offset(0, 1) = "DR From"
End If

If InStr(1, ActiveCell.Offset(0, 1), "dresden") Then
ActiveCell.Offset(0, 1) = "DR To"
End If

ActiveCell.Offset(1, 0).Select

Loop

You will need to add Option Compare Text to the very top of your module to catch all variations of case, and you may need to adjust the values depending on the structure of your data, but the key functionality is there.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector