Advertisement

01.24.2008 at 06:47AM PST, ID: 23107761
[x]
Attachment Details

VLOOKUP function using Excel Macro or VBA

Asked by ivyflora in Microsoft Excel Spreadsheet Software, Spreadsheet Software, VB Script

Tags: , , ,

This project is very urgent, so I would appreciate it if some of you experts out there can figure it out quickly,  thanks!

I would like to create a control button that would have the similar functions as the vlookup formula.  

In one source file, I have three columns of information.  The first (column A) has a list of IDs called SKU.  Column B holds the values of net sales correponding to the set of IDs.  And Column C holds the values of gross sales corresponding to the set of IDs.  

In another workbook, called "master excel.xls", I have in column A, with a list of SKUs HOWEVER, they are not in the same order as the source file.  I would like to look up the SKUs in the source file and match up the SKUs in the master file, then copy the corresponding data of net sales and gross sales in the correct column in the master excel.  Note: there are labels in the master excel, so I would like to match the correct label titles and then print the values into the column.  

Attached is what I have done so far.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Private Sub cmdNetShip_Click()
 
'Opens one of the source files
Workbooks.Open "SKU Rationalization_gross & net sales for 2007.xls", False, True
 
With ActiveWorkbook
    With .Worksheets("gross & net sales")
        'array that holds the list of id #s from the source file
        sourcesku = .Range("A5:" & .Range("A5").End(xlDown).Address).Value
        
        'array that holds the data for the net sales column (adjacent to the id # column)
        netsales = .Range("b5:" & .Range("b5").End(xlDown).Address).Value
    End With
End With
 
'Open the master excel file
 
Workbooks.Open "master excel.xls", False, False
With ActiveWorkbook
    With .Worksheets("ALL")
        mastersku = .Range("a3:" & .Range("a3").End(x1Down).Address).Value
        
        'use the SKUs (in the 2 arrays) from both sheets to do a vlookup
        'print corresponding values from net sales and gross sales into the correct box in the master excel
         
    End With
End With
 
End Sub
[+][-]01.24.2008 at 07:06AM PST, ID: 20733612

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 07:09AM PST, ID: 20733644

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft Excel Spreadsheet Software, Spreadsheet Software, VB Script
Tags: Microsoft, Excel, 2003, Macro, VBA
Sign Up Now!
Solution Provided By: MrBullwinkle
Participating Experts: 2
Solution Grade: A
 
 
[+][-]01.24.2008 at 07:19AM PST, ID: 20733746

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 07:31AM PST, ID: 20733864

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 07:43AM PST, ID: 20733996

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 08:11AM PST, ID: 20734248

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 08:31AM PST, ID: 20734480

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 08:34AM PST, ID: 20734519

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 08:36AM PST, ID: 20734534

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 08:44AM PST, ID: 20734619

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 08:45AM PST, ID: 20734625

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 08:52AM PST, ID: 20734700

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 08:58AM PST, ID: 20734765

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 09:18AM PST, ID: 20734982

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 10:51AM PST, ID: 20736014

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 11:15AM PST, ID: 20736259

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 11:51AM PST, ID: 20736630

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 12:15PM PST, ID: 20736906

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 12:46PM PST, ID: 20737217

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]01.24.2008 at 01:09PM PST, ID: 20737434

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628