Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 24th, 2005, 12:47 PM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query data and exporting data from one sheet to an

Hi,

I have a workbook that contains 10 worksheets. Each sheet contains the same type of data in the same format (but each represents a different organization that submits the data). I have set up a sheet called “query sheet” to query the data contain in all 10 worksheets. On this sheet I have combo boxes that let the user determine what data they want to query. For instance, the user can select “item A” of “inventory A” of “organization A” and the code should go to organization A worksheet and look up the inventory A column. Once it finds the matching item A row in inventory A column, then it should offset two columns to the right to find the value and return the value to the query sheet at a specified “result” cell. I am struggling with this since I am new to VBA, can anyone help? Thank you very much.


  #2 (permalink)  
Old April 28th, 2005, 05:37 AM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You are going to need a macro. This is a fiddly (although not difficult) job at best and lack of sufficient information here is a problem. No-one is going to write it for you (unless yo want to pay :D

To start you off, here are som "bare bones"
Code:
Sub PSEUDOCODE_UNTESTED()
    Dim ToSheet As Worksheet
    Dim ToRow As Long
    '-
    Dim FromSheet As Worksheet
    Dim FromRow As Long
    Dim FromCol As Integer
    '-
    Dim OrgName As String
    Dim Inventory As String
    Dim MyItem As String
    '-
    Dim FoundCell As Object
    '---------------------------
    '- query sheet
    Set ToSheet = ThisWorkbook.Worksheets("query")
    ToRow = 1
    '---------------------------
    '- data from combo boxes
    OrgName = "1"
    Inventory = "A"
    MyItem = "1234"
    '---------------------------
    '- Set 'From' Column Number
    Select Case Inventory
        Case "A"
            FromCol = 10
        Case "B"
            FromCol = 11
    End Select
    '---------------------------
    '- get data
    Set FromSheet = _
        Workbooks("Book1.xls").Worksheets("Organisation" & OrgName)
    '---------------------------
    '- find (need to set column to Item column)
        Set FoundCell = FromSheet.Columns(1).Find(what:=MyItem, _
            LookIn:=xlValues, LookAt:=xlPart)
    If FoundCell Is Nothing Then
        MsgBox ("Could not find " & MyItem)
    Else
        FromRow = FoundCell.Row
        ToSheet.Cells(ToRow, 1).Value = _
                FromSheet.Cells(FromRow, FromCol).Value
        ' etc.
    End If
End Sub

-----------------------
Regards BrianB
Most problems occur from starting at the wrong place.
Use a cup of coffee to make Windows run faster.
It is easy until you know how.
  #3 (permalink)  
Old April 28th, 2005, 02:40 PM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, BrianB. This is my first exposure to VBA and I'm trying to work on a project and learning it at the same time. Frustrating because everything I write came back riddled with errors. I appreciate the help.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting Query Data From Access to Excel JimInSouthernCal Access VBA 3 December 21st, 2007 08:34 AM
Exporting data to excel sheet x_ray VB.NET 2002/2003 Basics 0 January 6th, 2006 03:14 PM
Using ADO to query data in Excel sheet Fails with maaron Excel VBA 4 June 9th, 2005 01:56 PM
Exporting data from a repeater to an Excel sheet see07 ASP.NET 1.x and 2.0 Application Design 7 January 17th, 2005 03:46 PM
Exporting data from MS Excel sheet to Ms Access ajindal General .NET 1 January 17th, 2005 03:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.