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!

 

[Marcelo Branco]

@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

This works fine if the texts in A1, B1 and C1 are alphabetically ordered like in your example kk, pp, rr

But try with pp, rr, kk and see what happens.

You need the 3rd argument = 0 (exact match)

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


M.

 

[marc005]

Yes @Noris you are right I forgot to assign the output to a cell. All the three versions below works. Thanx for your help ;.)

Public Sub fff()
Range("A10").Formula = Application.Match(Range("A1").Value, Range("A1:C1").Value)
End Sub

Public Sub ffff()
Worksheets("Sheet3").Range("A5").Formula = Application.Match(Worksheets("Sheet3").Range("A1").Value, Worksheets("Sheet3").Range("A1:C1").Value)
End Sub

Public Sub fffff()
Worksheets("Sheet3").Range("A5").Formula = Application.Match(Sheets("Sheet3").Range("A1").Value, Sheets("Sheet3").Range("A1:C1").Value)
End Sub