CStr and then writing to a cell is not working

[randomNumberGenerator2211]

Hi,

I have a 2 sets of keys in 2 columns. The keys can be both strings, integers and integer-stored-as-strings.
I want the macro to run a vlookup on the keys. To make the format of the key same I have written a small code to convert all keys to string first as shown below.

If I include watches I see that code is converting integer to string but then its not writing that string back to the cell. e.g,

if I want to convert number:1 to a string in the below code the variable ConvertedValue becomes "1" but when I write it to the cell it becomes the integer 1 again.
Can someone please suggest what I could do to solve this? TIA!


Below is my code

Sub test()
Dim k As Integer
k = RangeofIntegertoRangeofString("Sheet1", 1, 1, 8, 1)
End Sub

Function RangeofIntegertoRangeofString(SheetName As String, FirstRow As Integer, FirstColumn As Integer, LastRow As Integer, LastColumn As Integer) As Integer
Dim i As Integer, j As Integer
Dim initialValue As Variant
Dim convertedValue As Variant
Set ConversionSheet = ActiveWorkbook.Sheets(SheetName)

For i = FirstRow To LastRow
    For j = FirstColumn To LastColumn
        initialValue = ConversionSheet.Cells(i, j).Value
        convertedValue = CStr(initialValue)
        ConversionSheet.Cells(i, j).Value = convertedValue
    Next j
Next i
RangeofIntegertoRangeofString = 1
End Function

 

[steve the fish]

You could either format the target cell/s to text before you write the number to them (you still need to use the CStr also) or if you are using a vlookup then you could use two vlookups and an iferror eg =IFERROR(VLOOKUP(1,rng,2,0),VLOOKUP(TRIM(1),rng,2,0))

 

[randomNumberGenerator2211]

awesome thanks! I tried a different method and it seemed to work.

instead of using Cstr I just concatenated an " ' " in both the key and lookvalue row.

Do you think it could have any draw backs?

 

[steve the fish]

Not really if you just need it for a vlookup they just need to be seen to be the same.