VBA to "Goto" WS and locate the referenced cell

[SeliM]

Good morning,
Thank you for taking time to read my enquiry.

I have a formula using address and match to form an address - e.g. 'Weighting Table'!$B$8 on the Check Text worksheet
I would like a macro that uses the address 'Weighting Table'!$B$8 in Check Text WS to go cell B8 on the Weighting Table WS

I haven't found any posts and replies that has been of value on this site or the web.

Being a novice this challenge is beyond my skill!

Thanks in advance

Mel

 

[Logit]

Option Explicit

Sub gittoCell()
    Application.Goto Reference:=Worksheets("Weighting Table WS").Range("B8")
End Sub

 

[Peter_SSs]

I have a formula using address and match to form an address - e.g. 'Weighting Table'!$B$8 on the Check Text worksheet

.. but you have not told us what cell on 'Check Text' the formula is in. For a demo of how I understand your question, I have assumed the formula is in cell C4

Sub Test()
  Application.Goto Reference:=Range(Sheets("Check Text").Range("C4").Value), Scroll:=True
End Sub

 

[SeliM]

my apologies for missing out important steps.
1. On Text Match WS there is a range that holds the criteria for assessment across 3 columns and 8 rows, columns are Category, Search Criteria Category, Search Criteria Text
2. There is a checking process (using Exact formula) on worksheet Text Match comparing the text in all of the 3 columns in the row - say Row 8, against a sheet called Weighting Table
3. Where a mismatch is found the address of the mismatch is given for example - 'Weighting Table'!$B$8 (or it could be $A$8, or $C$8)
4. The check process is carried out against the 8 rows - a mismatch could occur in any of the 24 cells

What I was wondering is if there is VBA that could read the address, (which in this case would be in $D$8, being 'Weighting Table'!$B$8) and take the user to that address perhaps via an icon button on Text Match at address $E$8?

I have seen MsgBox options which could work by asking the question "do you wish to go to 'Weighting Table'!$B$8 and edit criteria text?" however this may be overengineering

Thanks for your time.

Mel

 

[bebo021999]

Suppose in cell D8 is the address in text string, i.e,
'Weighting Table'!$B$8

try below code:

Dim wsName As String, cellAdd As String
wsName = Range("D8").Value ' return "'Weighting Table'!$B$8 "
cellAdd = Mid(wsName, InStr(3, wsName, "!") + 1, 255) ' returns "$B$8" 
wsName = Left(wsName, InStr(3, wsName, "'") - 1)' returns "'Weighting Table'"
wsName = Replace(wsName, "'", "") ' return "Weighting Table"
Sheets(wsName).Activate
Range(cellAdd).Select