Solved: VBA code to turn the formula into value

[alexxgalaxy]

I'm working on a workbook with multiple worksheets. I tried with the following codes to look up the value from cell G7 in cell A7 in all selected worksheets but came across with different issues for each of them as follows. I attached a sample file which contains extracted data in my original workbook (and also some made-up data). Could anyone help to amend the codes to make either one work?


1) For unknown reason, this works on all the selected worksheet except the last one.
[VBA]
Sub Cname1()

Dim i As Integer

For i = 4 To Sheets.Count
With Sheets(i)

With .Range("A7")
.Formula = "=IF(ISNA(VLOOKUP(R7C7,Customer_List,2,0)),"""", VLOOKUP(R7C7,Customer_List,2,0))"
.Value = .Value
End With

End With

End Sub
[/VBA]


2) When I included code ".Value = .Value", it returned empty in A7. When I removed it, the result can be displayed. The problem is, I need to turn the result of the formula into value.
[VBA]
Sub Cname2()

Dim i As Integer

For i = 4 To Sheets.Count
With Sheets(i)

Dim MyCname As String
MyCname = "=IF(ISNA(VLOOKUP(R7C7,Customer_List,2,0)),"""", VLOOKUP(R7C7,Customer_List,2,0))")"

.Range("A7") = MyCname
.Value = .Value

End With

End Sub

[/VBA]