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
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
