+ Reply to Thread
Results 1 to 7 of 7

VLOOKUP in code not formula

  1. #1
    Registered User
    Join Date
    02-17-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    15

    VLOOKUP in code not formula

    I have a sheet that uses vlookup formulas to get data from canned reports exported from a database. The lookup formula finds data based on a project#. I want to be able to record information in a spreadsheet before a database record is created and a project# assigned. This would be done by manually entering information into the spreadsheet, leaving the project# field blank, and then using code to overwrite the manual data once the project# field was populated. I can't think of a way to do this using formulas, as they must be embedded in the cells. Is there a way to write it in code? If I could write it in a formula, the logic would be, =IF(ISBLANK(B1),"leave the data typed",vlookup)

    thanks
    Last edited by wgog; 02-28-2011 at 10:38 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: VLOOKUP in code not formula

    The best way to get a formula in VBA (at least for me) is to record it.

    On your spreadsheet work out the formula, that is, get it working.

    1) turn on the macro recorder
    2) select the cell with the formula
    3) select F2 and hit tab
    4) Turn off the macro recorder
    5) select Alt + F11 to view the recorded code in the VBE.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    02-17-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: VLOOKUP in code not formula

    Thanks Jeff. That might work, but I was hoping to write something in VBA so that typing the project# in would trigger an event that would run the code and fill in the missing data. I'm not sure a macro would work but I can certainly give it a shot.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: VLOOKUP in code not formula

    Not sure I understand your comment.

    Quote Originally Posted by wgog View Post
    That might work, but I was hoping to write something in VBA
    Then you say

    Quote Originally Posted by wgog View Post
    I'm not sure a macro would work but I can certainly give it a shot.
    Here is a little example Vlookup in code.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-17-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: VLOOKUP in code not formula

    When I record a macro, it looks like this. I'm pretty sure that isn't going to work on the whole sheet. What I need to do (I think) is name a range for each column and then write some code that will reference the various vlookup formula's below. The code you wrote may work, but I'd need to modify it. Do you have a reference where I could look up what each part of the code is doing so I could understand how to modify it?

    Range("B21").Select
    ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC1,Report10!R1:R1048576,4,FALSE),"""")"
    Range("C21").Select
    ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC1,Report10!R1:R1048576,37,FALSE),"""")"
    Range("D21").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Report21!R1:R1048576,4,FALSE)"
    Range("E21").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Report21!R1:R1048576,9,FALSE)"
    Range("F21").Select
    ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC1,Report10!R1:R1048576,6,FALSE),"""")"
    Range("G21").Select
    ActiveCell.FormulaR1C1 = _
    "=IFERROR(VLOOKUP(RC1,Report10!R1:R1048576,42,FALSE),"""")"
    Range("H21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report10!R1:R1048576,12,FALSE)),"""",VLOOKUP(RC1,Report10!R1:R1048576,12,FALSE))"
    Range("I21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report10!R1:R1048576,27,FALSE)),"""",VLOOKUP(RC1,Report10!R1:R1048576,27,FALSE))"
    Range("J21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report10!R1:R1048576,29,FALSE)),"""",VLOOKUP(RC1,Report10!R1:R1048576,29,FALSE))"
    Range("K21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report21!R1:R1048576,14,FALSE)),"""",VLOOKUP(RC1,Report21!R1:R1048576,14,FALSE))"
    Range("L21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report21!R1:R1048576,15,FALSE)),"""",VLOOKUP(RC1,Report21!R1:R1048576,15,FALSE))"
    Range("M21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report21!R1:R1048576,16,FALSE)),"""",VLOOKUP(RC1,Report21!R1:R1048576,16,FALSE))"
    Range("N21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report21!R1:R1048576,17,FALSE)),"""",VLOOKUP(RC1,Report21!R1:R1048576,17,FALSE))"
    Range("O21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report21!R1:R1048576,18,FALSE)),"""",VLOOKUP(RC1,Report21!R1:R1048576,18,FALSE))"
    Range("P21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Summary!R1:R1048576,14,FALSE)),"""",VLOOKUP(RC1,Summary!R1:R1048576,14,FALSE))"
    Range("Q21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Summary!R1:R1048576,15,FALSE)),"""",VLOOKUP(RC1,Summary!R1:R1048576,15,FALSE))"
    Range("R21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Summary!R1:R1048576,10,FALSE)),"""",VLOOKUP(RC1,Summary!R1:R1048576,10,FALSE))"
    Range("S21").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Report21!R1:R1048576,10,FALSE)"
    Range("T21").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,Report21!R1:R1048576,11,FALSE)"
    Range("U21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report10!R1:R1048576,3,FALSE)),"""",VLOOKUP(RC1,Report10!R1:R1048576,3,FALSE))"
    Range("V21").Select
    ActiveCell.FormulaR1C1 = ""
    Range("W21").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(VLOOKUP(RC1,Report10!R1:R1048576,3,FALSE)),"""",VLOOKUP(RC1,Report10!R1:R1048576,3,FALSE))"
    Range("A22").Select
    End Sub

  6. #6
    Registered User
    Join Date
    02-17-2011
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: VLOOKUP in code not formula

    Okay, using Jeff's code example I have taken the data from the recorded macro (code pasted below). The only problem is that it wipes out data I have typed and doesn't seem to copy data except in the first row (18) ie it erases anything that's there, but the vlookup doesn't function properly.

    Sub VlookupVBA()
    Dim LR As String ' allows any value to be copied
    Application.ScreenUpdating = False
    With Sheets("In Development")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("B18:W" & LR)
    .Columns(1).Formula = "=IFERROR(VLOOKUP(A18,Report10!1:1048576,4,FALSE),"""")"
    .Columns(2).Formula = "=IFERROR(VLOOKUP(A18,Report10!1:1048576,37,FALSE),"""")"
    .Columns(3).Formula = "=IFERROR(VLOOKUP(A18,Report21!1:1048576,4,FALSE),"""")"
    .Columns(4).Formula = "=IFERROR(VLOOKUP(A18,Report21!R1:R1048576,9,FALSE),"""")"
    .Value = .Value
    End With
    End With
    Application.ScreenUpdating = True
    End Sub

    Adopting some code written by MarvinP (see below), I can get the basic functionality I want regarding not overwriting the data, but the VLOOKUP formula returns a #NAME? error.

    Private Sub Worksheet_Change(ByVal Target As Range) ' Every Change on this sheet do this code
    If Not Intersect(Target, Columns(1)) Is Nothing Then 'If the Change wasn't in Column E
    If Target.Value <> "" Then 'If myproject range has something in it then do the following.
    Cells(Target.Row, "B").Formula = "=IF(ISBLANK ($A" & Target.Row & "),"""",VLOOKUP($A18,Report10!1:1048576,4,FALSE))"
    Cells(Target.Row, "C").Formula = "=IF(ISBLANK ($A" & Target.Row & "),"""",VLOOKUP($A18,Report10!1:1048576,37,FALSE))"
    End If
    End If
    End Sub
    Last edited by wgog; 02-28-2011 at 04:45 PM.

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: VLOOKUP in code not formula

    In order to ensure you get the most accurate response, please attach a small sample workbook with one tab showing what you have and the other with the results you desire.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1