Selecting a cell in another workbook - 1004: Active method of range class failed

[Frankie_The_Flyer]

Having got to open a workbook from another and scroll to a specific line in the second workbook, I now want to position to a specific cell in the second workbook using a row number found from a formula in a cell in the second workbook.

(The second workbook is from OneDrive so it can't contain VBA stuff and has to rely on being driven from VBA in the workbook on my computer)

The Code I have is as follows and does just about everything but doesn't position to the specific cell number (from a long "IF" formula in BB1) in column A, coming up with "1004: Active method of range class failed"

Please Login or Register  to view this content.

I've searched the Internet but can't find anything that points me in a direction to fix this.

Any ideas please??

Much appreciated

Frankie

[Keebellah]

This

Please Login or Register  to view this content.

is not a row number!!!
Unless of course if the cell BB1 (Column BB row 1 ) actually contains the row number you're looking for.
So it's back to the drawing board

[Frankie_The_Flyer]

Hi Keebellah

The Cell BB1 has an "IF" formula that looks at another cell then searches a list of cells to find the match (i.e. =IF(AX1=E12, Row(E12),IF(AX1=E22,ROW(22),IF(AX1=E31,ROW(E31)))) etc). So while BB1 holds a formula, the result (Value??) is the row number if the number in the E cell matched the number in AX1.

Having found the right row, a Conditional Format (in the second worksheet) then colours in the cell (E12 or what ever), but the focus remains on BB1. I need the focus to go to column A and the Row Number at BB1. i.e. A12 or A22 or A31 etc.
Does that make sense??

[Frankie_The_Flyer]

Whooo Hoooo! Cracked this too!

Had to direct the Activation command directly to the worksheet.

Wb1.Sheets(CorrectSheet).Cells(myRow, "A").Activate
in place of
Cells(myRow, "A").Activate


So pleased!!

[Keebellah]

In that case you have to check first if BB! actually contains a number.
If that's true then, since myRow is a long value which is correct you make the result long too

Please Login or Register  to view this content.

Short quick and dirty explanation. Going off-line now for a long weekend.
Will read what you got after sunday

[Keebellah]

Tops, great,

[AliGW]

Administrative Note:

@Frankie

You have forgotten your code tags again - please go back and add them.