How does the VLOOKUP and VALUE syntax work in VBA?

Question

Suppose that I have a range of data in Column A that is in text format, first I want to change the format of data to number/value.

The code I came up with is: Method 1:

Sub Test123()
    Dim data As String
    Dim count As Integer
    Dim rowNo As Integer
    Dim answer As String
    
    rowNo = Range("A1").End(xlDown).Row
        
    For count = 2 To rowNo
        data = Range("A" & count).Value

        Range("B" & count).Formula = "=VLOOKUP(VALUE(data),Table1,2,FALSE)"

    Next count

Method 2:

        answer = Application.WorksheetFunction.VLookup(data2, Sheet1!Table1, 2, False)
        Range("B" & count).Value = answer

The correct answer given by my tutor is

Range("B" & count).Formula = "=VLOOKUP(VALUE(A" & count & "),Table1,2,FALSE)"

Question 1: Why do my methods not working?

Question 2: I can't seem to figure out the underlying syntax VALUE(A" & count & ") here. Could anyone please help me to understand? Also, is there a repository where I can read more about these syntax?

Answer 1

Please, test the next way. The way of VLookup using will generate an array which will be dropped in the necessary range:

Sub Test123()
    Dim sh As Worksheet, rowNo As Long, rngA As Range, rngB As Range
    Dim rngTbl As Range, tbl As ListObject
  
    Set sh = ActiveSheet
    Set tbl = sh.ListObjects("Table25") 'my tested table...
                                        'Please, use here your real one name!
    Set rngTbl = tbl.DataBodyRange
    rowNo = sh.Range("A" & sh.rows.count).End(xlUp).row
     Set rngA = sh.Range("A2:A" & rowNo)
     Set rngB = sh.Range("B2:B" & rowNo)
     rngB.value = Application.VLookup(rngA, rngTbl, 2, False)
     'If liking a more fancy way, to return what you want in case of 
     ' no match (instead of `#N/A`), please uncomment the next line:
     'rngB.value = Application.IfNa(Application.VLookup(rngA, rngTbl, 2, False), "Ups...")
End Sub

The above code should be considered as an improved version...

As mentioned in my comment, data is a simple string in the way you try it. It should be used in the next way: "=VLOOKUP(VALUE(" & data & "),Table1,2,FALSE)", to make the code considering data a variable...