How do I convert text ie apple to string "apple" in excel or VBA?

[marc005]

How do I convert text ie apple to string "apple" in excel or VBA?
I need the text as a string to use MATCH. I have looked everywhere but cant find a way.

for example: if you put kk in cell A1, pp in cell B1 and rr in cell C1 and then in some
other cell you put =MATCH(kk,A1:C1) it will not work you need to
put =MATCH("kk",A1:C1)

Also if you want to convert text to string it will not work to put for
example ="a1". if you manually type =MATCH("kk",A1:C1) it is ok but since
the code run in VBA I cant type this manually hence I need to convert all
text elements in a range to strings. Complicated!

 

[Norie]

Why not just A1?

=MATCH(A1,A1:C1)

Works for me.

 

[marc005]

Thanx for your reply. Yes that works in excel but how would I do that in VBA?

 

[Norie]

Like this.

Dim Res As Variant

Res = Application.Match(Range("A1").Value, Range("A1:C1").Value)

 

[ZAX]

Is That What You Want (Replacing The Text Between Brackets As The Following EX: range("d1")

Range(Type Any Address Here).Formula = "=MATCH(""kk"",A1:C1)"

ZAX

 

[marc005]

@Nori,

Public Sub ff()
Application.Match(Sheets("Sheet3").Range("A1").Value, Sheets("Sheet3").Range("A1:C1").Value)
End Sub

does not work! It gives an error "syntax error".

@ZAX I will get back to you. I will try that

 

[Marcelo Branco]

Hi,

I think that in all formulas suggested is missing the 3rd argument = 0 (exact match).

M.

 

[Marcelo Branco]

Maybe...

Sub aTest()
    Dim myVar As Long, lookupValue As String
    
    With Sheets("Sheet1")
        lookupValue = .Range("M1").Value ' Assumes M1 houses "kk"
        myVar = Application.Match(lookupValue, .Range("A1:C1"), 0)
        MsgBox myVar
    End With
End Sub

Assumes data in Sheet1

M.

 

[marc005]

@ZAX that works great I have been looking for this for several days ha ha
One thing to note though:

Public Sub ff()
Range("A5").Formula = "=MATCH(""kk"",A1:C1)"
End Sub

works fine however the below does not work at all:

Public Sub ff()
Range(Sheets("Sheet3").Range("A5")).Formula = "=MATCH(""kk"",Sheets("Sheet3").Range("A1;C1"))"
End Sub

I have to check if that is also the case for @Noris answer.

 

[Norie]

The code I posted does work, you just haven't assigned the result to anything.

This will only work if A5 on Sheet3 contains a valid address.

Public Sub ff()
 Range(Sheets("Sheet3").Range("A5")).Formula = "=MATCH(""kk"",Sheets("Sheet3").Range("A1;C1"))"
 End Sub