Need help into how to change a macro

[shrek]

I have the following macro and I need to amend it to look for EUSTON ETC in columns E and F.

Can anyone help.




ActiveWindow.SmallScroll Down:=-30
Range("R1").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-3]=""Euston"", RC[-2]=""Bletchley""),""North"", IF(OR(RC[-2]=""Euston"",RC[-2]=""Northampton"",RC[-2]=""Bletchley"",RC[-2]=""Milton Keynes""),""South"",""North""))"
Range("R1").Select
Selection.AutoFill Destination:=Range("R1:R571"), Type:=xlFillDefault
Range("R1:R571").Select
Range("K1:R571").Select
Range("K571").Activate
Selection.Sort Key1:=Range("R1"), Order1:=xlAscending, Key2:=Range("N1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveWindow.SmallScroll Down:=-150
ActiveWindow.ScrollRow = 400
ActiveWindow.ScrollRow = 395
ActiveWindow.ScrollRow = 378
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 361
ActiveWindow.ScrollRow = 357
ActiveWindow.ScrollRow = 344
ActiveWindow.ScrollRow = 318
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Range("O12").Select
ActiveWindow.SmallScroll Down:=-21
End Sub

 

[VoG]

Try

Sub test()
Dim Found As Range
Set Found = Columns("E:F").Find(what:="EUSTON ETC")
If Found Is Nothing Then
    MsgBox "Not found"
Else
    MsgBox "EUSTON ETC" & " Found at" & vbCrLf & Found.Address(False, False)
End If
End Sub

 

[shrek]

Thanks for that Vog but ive got the one I pasted because it pastes NORTH or SOUTH in another column do I can sort easier. I just need it to look in columns E and F for Euston etc.

 

[Scott Huish]

All that Select and Scroll code is unnecessary. What is this supposed to do exactly?

 

[Boller]

Here is a tidied-up version of the macro you posted :-

Range("R1:R571").FormulaR1C1 = _
    "=IF(AND(RC[-3]=""Euston"", RC[-2]=""Bletchley""),""North""," _
    & "IF(OR(RC[-2]=""Euston"",RC[-2]=""Northampton"",RC[-2]=""Bletchley""," _
    & "RC[-2]=""Milton Keynes""),""South"",""North""))"
Range("K1:R571").Sort Key1:=Range("R1"), Order1:=xlAscending, Key2:=Range("N1"), _
    Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

This macro creates the following formula :-

=IF(AND(O1="Euston", P1="Bletchley"),"North",IF(OR(P1="Euston",P1="Northampton",P1="Bletchley",P1="Milton Keynes"),"South","North"))

Instead of this, if you need the formula to be :

=IF(AND(E1="Euston", F1="Bletchley"),"North",IF(OR(F1="Euston",F1="Northampton",F1="Bletchley",F1="Milton Keynes"),"South","North"))

Then (obtained via the macro recorder) :-

Range("R1:R571").FormulaR1C1 = "=IF(AND(RC[-13]=""Euston"", RC[-12]=""Bletchley"")," _
    & """North"",IF(OR(RC[-12]=""Euston"",RC[-12]=""Northampton"",RC[-12]=""Bletchley""," _
    & "RC[-12]=""Milton Keynes""),""South"",""North""))"

 

[shrek]

Brill Many Thanks