Code does not work

[js2756]

I am trying to cycle through a range with cells that contain values: "A","B","C", " ". I want to use Lookup(Cell.value,{"A","B","C"},{52,46,40}) to change the cell values to their numerical equivalents as follows:

Sub ConvertGtoPts()
Dim Cell As Range
For Each Cell In Range("B35:O42")

If IsEmpty(Cell.Value) Then
Cell.Value = " " 'i.e. don't do anything
Else
Cell.Formula = "=lookup(Cell.value, {""A"",""B"",""C""},_{52,46,40})"
End If
Next Cell

End Sub

But I wind up with zeros in the range. Where am I going wrong?


Thanks

js

 

[Andrew Poulsom]

Try:

Sub ConvertGtoPts()
    Dim Cell As Range
    For Each Cell In Range("B35:O42")
        If Not IsEmpty(Cell.Value) Then
            Cell.Value = WorksheetFunction.Lookup(Cell.Value, Array("A", "B", "C"), Array(52, 46, 40))
        End If
    Next Cell
End Sub

 

[js2756]

Andrew that gets me what I'm after. Thanks for the help

js