Copy Formula result as value into find

[SeliM]

Good morning
Thank you for reading my enquiry
Background: I'm working with a local government to simplify their procurement system. The key component is an off the shelf proprietary system that after a range of activities produces an excel workbook with an assessment output that can run to 300 or so lines.

The work undertaken is to produce a template for approval signatures based on the assessment results.
The work relies on perfect match of assessment criterions between the proprietary output and the template workbook.
I have set up a Match WS to test if the criterions match if not the wording that does not match is displayed.
I have a macro to find the criteria in question however the VBA text uses the actual words of the criteria - hence is static. I have tried to find a way to not have static wording but rather a cell reference without success.
Could you advise on script that selects a cell, copies value and uses find in another ws?

Many thanks

Mel

 

[myall_blues]

Hi Mel

You can retrieve the value from a cell by using the .Value property, like this for example. The message box will display whatever you put in A1.

Sub Criteria()
    Dim crit_word As String
    crit_word = Range("A1").Value
    MsgBox "Criteria wording is """ & crit_word & """"
End Sub

 

[SeliM]

Many Thanks,
is there a means by which VBA can refer to the contents of a cell on another WS and use in the find facility to find that value in another WS?
The static script looks like:
Cells.Find(What:="Availability and qualifications of key personnel ", After _
:=ActiveCell, LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows _
, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False). _
Activate

Is it possible to change "Availability and qualifications of key personnel " to a cell reference such as 'Text Match'!$E$4 and find content of $E$4 in WS 'Weighting Table"?

Thanks
Mel

 

[myall_blues]

Something like this:

Sub FindMe()
    Dim SearchString As String, CellsFound As String, FirstHit As String
    Dim c As Range
    SearchString = Worksheets("Text Match").Range("E4").Value
    Application.FindFormat.Clear
    
    With Worksheets("Weighting Table")
        Set c = .Cells.Find(What:=SearchString, After _
            :=ActiveCell, LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows _
        , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            FirstHit = c.Address
            Do
                CellsFound = CellsFound & c.Address & "; "
                Set c = .Cells.FindNext(After:=c)
            Loop Until FirstHit = c.Address
        End If
    End With
    MsgBox "String found at " & CellsFound
End Sub

 

[myall_blues]

Is it possible to change "Availability and qualifications of key personnel " to a cell reference such as 'Text Match'!$E$4 and find content of $E$4 in WS 'Weighting Table"?

The code I just provided looks for the value given in 'Text Match'!$E$4 and finds the cell address of anywhere that value occurs on sheet 'Weighting Table'
If you just want to compare values between cells then find is probably not the best way to go about it. For example, if you wanted to compare all the values in the two sheets over the range B3:S20 then I'd use different code to do that, but we would need more details about what you're comparing.