Solved

Lookup Value from another workbook using VBA

Posted on 2010-11-12
4
483 Views
Last Modified: 2012-05-10
Hi, as a VBA newbie I'm struggling with this challenge.

I have two spreadsheets, a source spreadsheet containing a table of data and a destination spreadsheet into which I need to have data placed at the press of a button.

I have attached each sheet. In the destination s/sheet the user will type the store number, then click a button. This will then look up the matching record in the source spreadsheet and then copy the average balance value into the destination s/sheet (into the yellow box).

All help gratefully received.
Destination.xlsx
Source.xlsx
0
Comment
Question by:jonlake
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
broomee9 earned 500 total points
ID: 34122097
Try this:

Open both workbooks
Enter a Store Number
Click the Get Avg Balance button
Option Explicit

Private Sub cmdGetValue_Click()
    Dim storeNum As String
    Dim avgBalance As Double
    
    storeNum = Range("A4").Value
    
    With Workbooks("Source.xlsx").Sheets("Source")
        On Error Resume Next
        avgBalance = WorksheetFunction.VLookup(storeNum, .Range("A1:D10000"), 4, False)
        On Error GoTo 0
    End With
    
    ThisWorkbook.Activate
    Sheets("Destination").Range("B4").Value = avgBalance
End Sub

Open in new window

Destination.xlsm
Source.xlsx
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 34122102
Why do you need VBA? You could use a formula like this:

=VLOOKUP(A4,Source.xlsx!$A$2:$D$1999,4,FALSE)
0
 

Author Closing Comment

by:jonlake
ID: 34122288
Thanks, this works very well. As a bonus is it possible to have the lookup operate if the source s/sheet were closed?
0
 

Author Comment

by:jonlake
ID: 34122302
Hi StephenJR, unfortunately the s/sheet cannot contain formulas as it is loaded into our accoutning package and will freak out if it contains code. My plan is to use the code currently assigned to a button as an add-in, operated from a ribbon button.

Thanks to all.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question