Solved: VBA code to turn the formula into value

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]

Quote:

---

Originally Posted by alexxgalaxy

1) For unknown reason, this works on all the selected worksheet except the last one.

---

Can you clarify in what way doesn't it work?

Hi Brian

What I was trying to do was to turn the lookup result in multiple worksheets into value.

For my first code above, somehow, it works on all the selected worksheet except the last one.

For the second code, when I included code ".Value = .Value" (highlighted in read above), the return value is empty in A7. When I removed it, the result can be displayed but this couldn't meet my requirement (i.e. turn the lookup result in multiple worksheets into value.)

Your second code is using with on the sheet not the range. That is why .value = .value is failing.

Sorry, I'm an elementary user. Someone taught me the code one year ago but didn't really explain how it worked. It usually worked but not this time. I don't think I understand what you mean.

Could you advise what I could do with the code then? Thanks!

[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
.Range("A7").Value = .Range("A7").Value

End With

End Sub
[/vba]

Quote:

---

Originally Posted by BrianMH

[vba]Sub Cname2()

[/vba]

---

It didn't work. It became empty instead of the result of the formula.

I finally figured out what's the problem. There was nothing wrong with my original code. What was wrong was the order of the other codes in my marco. The R7C7 in my vlookup formula came before the code that I inserted the value for R7C7. That's why my code could produce the result when it wasn't converted into value (i.e. there was nothing in the cell G7 when the formula was converted into value. So, it returned empty when the .value = .value was added).

I felt so stupid for wasting so much time on working on different ways to do the code instead of reviewing the whole marco. I'm so embarrassed and sorry for wasting your time, Brain. I apologies.