VBA Goto Syntax

[SeliM]

Good morning

Thanks for taking time to read my enquiry

I have a WB that holds Criteria for assessing tenders, the wording of the tender output from another system need to exactly match the wording of the assessment system.

Where it does not match I need the system to goto the address of the assessment system.

I have found a very simple goto script:

Sub jumpToCell()
Dim aAddress() As String
aAddress = Split([Sheet2!D3].Text, "!")
Application.Goto Reference:=Worksheets(aAddress(0)).Range(aAddress(1))
End Sub

Which on a test WB works perfectly.

However, when I endeavour to change names of WS to a text say Sheet1 to A and Sheet2 to B I get a Run time error 9.

Would you be able to advise on how to remedy pleas.

R
Mel

 

[MARK858]

If you change Sheet2 to A the code will go to the cell referenced in the contents of Sheet A cell D3, what do you have in that cell?

 

[SeliM]

Thanks Mark

Sheet2 (or A) $D$3 holds a formula that gives an address (of mismatched text) -

Sheet1!$c$3

(formula - =IF(C3<>Sheet1!C3,"Sheet1!$c$3",""))

Sheet1 & Sheet2 works fine
changing name of WS and varying formula & script draws run time error

Hoe this clarifies.
Mel

 

[MARK858]

You'll only get that error if the result in D3 does not match a valid sheet name/cell.

With

Book1
	D	E	F	G	H
1
2
3	Sheet1!$c$3		Sheet1!$c$3		1
4
5
A

Cell Formulas
Range	Formula
D3	D3	=IF(H3=1,F3,"")

in sheet A

the code

Sub jumpToCell()
    Dim aAddress() As String
    aAddress = Split([A!D3].Text, "!")
    Application.Goto Reference:=Worksheets(aAddress(0)).Range(aAddress(1))
End Sub

Goes to Sheet1 cell C3 fine for me running the code from Sheet4

 

[MARK858]

With

Book1
	C	D
3	999	Sheet1!$c$3
A

Cell Formulas
Range	Formula
D3	D3	=IF(C3<>Sheet1!C3,"Sheet1!$c$3","")

and

Book1
	B	C	D
2
3		888
Sheet1

I get the same result, the code goes to Sheet1 cell C3 fine

 

[SeliM]

Thanks very much Mark
using default Sheet1 Sheet2 etc. - perfect
Changing sheet2 to Match Text is fine.
Changing the go to ws is the challenge - t doesnt like 2 word WS names apparently

I have through experimenting found the error 9 relates to changing Sheet1 to "Weighting Table"; I used 'Weighting Table' which it doesn't like, Weighting is fine.

Curious to know what the answer would be for 2 word WS names- maybe simply changing the ws name might fix it on the actual WB - I'll see if it works ok

Thanks for your time

Mel

 

[MARK858]

Formula as per the below, note the single quotation marks

Book1
	C	D
3	999	weighting Table!$c$3
A

Cell Formulas
Range	Formula
D3	D3	=IF(C3<>'weighting Table'!C3,"weighting Table!$c$3","")

Do the same in the sub if that is two words, in the below the sheet with the formula is called "And again"

Sub jumpToCell()
    Dim aAddress() As String
    aAddress = Split(['And again'!D3].Text, "!")
    Application.Goto Reference:=Worksheets(aAddress(0)).Range(aAddress(1))
End Sub