Trying to Avoid Activate & Select

[PWD]

Good afternoon I'm trying to improve some code:

Code = "Code End" ' Final column of codes in "MyCodeframe" workbook. Need to add word values to the subsequent columns.

Q1Array = Array("Q1a", "Q1b", "Q1c", "Q25")

    For Each Q1 In Q1Array

'********** From here:
Workbooks(MyCodeframe).Activate

Sheets(Q1).Activate
'use cells.rows.count rather than a hard row count value
EndRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

        Rows("1:1").Select
        Selection.Find(What:=Code, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate

    CodeColumn = ActiveCell.Column + 1

        For x = 0 To 7 '8 columns
        Cells(2, CodeColumn + x).Select

        ActiveCell.FormulaR1C1 = _
        "=INDEX('[" & CodeFrameMaster & "]" & Q1 & "'!C3,MATCH(RC[-8],'[" & CodeFrameMaster & "]" & Q1 & "'!C4,0),1)"

        Next x
        
        For x = 0 To 7 '8 columns
        .Cells(2, CodeColumn + x).FormulaR1C1 = _
        "=INDEX('[" & CodeFrameMaster & "]" & Q1 & "'!C3,MATCH(RC" & CodeColumn + x - 8 & ",'[" & CodeFrameMaster & "]" & Q1 & "'!C4,0),1)"

        Next x
        
    Range(Cells(2, CodeColumn), Cells(2, CodeColumn + x - 1)).AutoFill Destination:=Range(Cells(2, CodeColumn), (Cells(EndRow, CodeColumn + x - 1)))
    ActiveSheet.Calculate
    Range(Cells(2, CodeColumn), (Cells(EndRow, CodeColumn + x - 1))).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues

'********
With Workbooks(MyCodeframe).Sheets(Q1)

EndRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row

        With .Rows("1:1")
            CodeColumn = .Find(What:=Code, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Column + 1
        End With

I 'suspect' the After:=ActiveCell may have something to do with my initial stumble. I think the rest of the code shouldn't be a huge problem - it's just Step #1 that has tripped me up.

Many thanks,
D€$

 

[PWD]

OK, sorted out the "rest of the code" it's just the initial "Find" that's still a problem.

Many thanks,
D€$

 

[PWD]

Got it!!!

With Workbooks(MyCodeframe).Sheets(Q1)

EndRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row

CodeColumn = .Rows(1).Find(What:=Code _
                , LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False).Column + 1

Many thanks,
D€$

 

[zelgar]

Could the problem be that you're only looking in Row 1 ( Rows("1:1").Select ) for the text "Code End"? You are doing a Selection.Find so it's only going to be looking in the area selected (Row 1). You might want to change it from Selection.Find to Cells.Find

 

[PWD]

No, that's fine; I know it's only in the first row. It was just the syntax of how to avoid the dreaded "Select" but the above does the job!!

Many thanks,
D€$

 

[Andrzejek]

Just curious...
Do you have [tt]Option Explicit[/tt] at the top of your code?


Have fun.

---- Andy

 

[PWD]

Hi Andy; well, I hadn't but after doing so nothing came up as undeclared. Any reason you asked?

Many thanks,
D€$

 

[Andrzejek]

Based on your OP, it looks to me you that are trying to find the ways to improve your code. Which is great. The best way to do that is to use Option Explicit, IMHO. And since I did not see any Dim statements… That’s why my question.

Have fun.

---- Andy

 

[PWD]

Oh, OK. I was just trying to replace "Activate" & "Select" with "With Workbooks" but couldn't get the syntax correct. In summary; this failed:

With Workbooks(MyCodeframe).Sheets(Q1)

EndRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row

        With .Rows("1:1")
            CodeColumn = .Find(What:=Code, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Column + 1
        End With

and this was a solution I found & adapted that appears to do what I want:

With Workbooks(MyCodeframe).Sheets(Q1)

EndRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row

CodeColumn = .Rows(1).Find(What:=Code _
                , LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False).Column + 1

Many thanks,
D€$