Results 1 to 8 of 8

how to return value not formula

This is a discussion on how to return value not formula within the Excel Questions forums, part of the Question Forums category; Hi all I would like this code to return the value into the d7, not the formula Range("d7") = "=(VLookup(0, ...

  1. #1
    Board Regular
    Join Date
    Jul 2002
    Location
    melbourne australia
    Posts
    258

    Default

    Hi all

    I would like this code to return the value into the d7, not the formula

    Range("d7") = "=(VLookup(0, myrange, 3, False))"


    currently I am add the following but it is cumbersome

    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False

    tia

  2. #2
    Board Regular
    Join Date
    Nov 2002
    Posts
    83

    Default

    Try

    Range("d7").Formula = "=(VLookup(0, myrange, 3, False))"

  3. #3
    Board Regular
    Join Date
    Jul 2002
    Location
    melbourne australia
    Posts
    258

    Default

    Hi expel

    no when I try it
    Range("d7").Formula = "=(VLookup(0, myrange, 3, False))"
    I still get the formula in the cell d7 not the value from the table

    I originally thought range("d7").value but this also only returns formula.

    Is it possible I have made some setting wrong?

    tia


  4. #4
    Board Regular
    Join Date
    Nov 2002
    Posts
    83

    Default

    Sorry, I couldn't test your formula, as i didn't have that data. I tested it substituting NOW(), and it worked fine.

    It is possible to alter the sheet to display formulae in the cells instead of the values.

    Try Press CTRL + ` (grave accent), to toggle this setting, or go to Tools | Options | View tab, and see if formulas is checked in the Window Options at the bottom.

    Hope this helps



  5. #5
    Board Regular
    Join Date
    Nov 2002
    Posts
    143

    Default

    Kade,

    See if this code helps your understanding:


    Sub UseVLookup()

    Set rngVL = Workbooks("Book1.xls").Worksheets("Sheet1").Range("A:B")

    For i = 2 To 10
    LUvalue = Cells(i, 1).Value
    Cells(i, 2).Value = Application.WorksheetFunction.VLookup(LUvalue, rngVL, 2, False)
    Next i


    End Sub


    Make changes as appropriate to accommodate your situation.

    enjoy

    [ This Message was edited by: Bariloche on 2002-12-12 08:48 ]

  6. #6
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default

    Yes, follow Bariloche's advice, modify your code to :
    Range("D7").Value = Application.WorksheetFunction.VLookup(0, myrange, 3, False)

    Merry Christmas

  7. #7
    Board Regular
    Join Date
    Dec 2002
    Posts
    1,179

    Default

    Yes, follow Bariloche's advice, modify your code to :
    Range("D7").Value = Application.WorksheetFunction.VLookup(0, myrange, 3, False)

    Merry Christmas

  8. #8
    Board Regular
    Join Date
    Jul 2002
    Location
    melbourne australia
    Posts
    258

    Default

    Thank you all, another note in for the diary

    kd

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com