VBA Lookup Returning Formula Instead of Value

[Altonfree]

Trying to complete a macro to pull data from one worksheet to update erroneous data on another. There are three columns to work with, and for the first two columns, the code works just fine. For the final column, the macro cycles through the first If statement with no trouble, but returns the vlookup formula, not the value, for the following two Else If statements. The format of all columns appears to be text, though I have formatted it within the macro as well to make sure. But with or without the formatting, I get the same result:

Range("K:K").NumberFormat = "@"
Range("K2").Select
 Do While Not IsEmpty(ActiveCell.Offset(0, 3))
    If ActiveCell.Offset(0, 3) = "A" Then
    ElseIf ActiveCell.Offset(0, 3) = "I" Then
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],DFLT,8,0)"
    ElseIf ActiveCell.Offset(0, 3) = "C" Then
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-10],DFLT,8,0)"
    End If
    ActiveCell.Offset(1, 0).Select
   Loop

Any assistance would be welcome.

 

[Altonfree]

Anyone?

 

[Joe4]

So, you just want to appear the value of the VLOOKUP and not the VLOOKUP formula itself?
Try something like this (note that I tried to improve the speed and efficiency of the code by getting rid of all the ActiveCell and Select statements - that slows your code down and it is not necessary to select the ranges to work with them):

    Dim r As Long

    Range("K:K").NumberFormat = "@"

    r = 2
    Do While Not IsEmpty(Cells(r, "N"))
        Select Case Cells(r, "N").Value
            Case "I", "C"
                Cells(r, "K").FormulaR1C1 = "=VLOOKUP(RC[-10],DFLT,8,0)"
                Cells(r, "K").Value = Cells(r, "K").Value
        End Select
        r = r + 1
   Loop

 

[Altonfree]

Thanks for the response, and sorry for the late followup. I tried your code, but got the same result: the macro returned the formula instead of the value.

 

[Joe4]

It shouldn't.

Can you post a data example, so we can make sure that we are working off the same data?

 

[RoryA]

The format of all columns appears to be text, though I have formatted it within the macro as well to make sure.

That is causing the problem. The format of a formula cell should never be Text. Use General.

 

[Joe4]

That is causing the problem. The format of a formula cell should never be Text. Use General.

Good catch Rory! I overlooked that.

You can ensure that by placing this line at the top of your code:

    Columns("K:K").NumberFormat = "General"

 

[Altonfree]

Thanks, fellas...that did the trick. I was sure I had it formatted as general the first time I tried it, so I switched to text. You'd think after years of working with VLookups I'd have the general vs text issue ingrained by now.

 

[Joe4]

You are welcome.
Glad we could help!