Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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
 
Old March 20th, 2004, 08:06 AM
Authorized User
 
Join Date: Mar 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multi column search and compare

Hi

Hopefully someone can help me with this problem.

I have a 3 page workbook (report,data1,data2).
data1 and data2 are loaded as External Data from 2 csv files and are updated at 10 minute intervals. Both sheets have the same layout -:
Col A = date (i.e. 12-DEC-04) (as string)
Col B = reference (i.e. ABC1234) (as string)
Col C = value1 (as integer)
Col D = value2 (as integer)
Col E = value3 (as integer)

The report page has 3 columns.
Col A = date (as Date)
Col B = reference (as string)
Cols C - F = various values
Col G = valtotal (as integer)

I've written a function that takes in 4 parameters (ref,date,page,reqcol).
"page" sets which of the sheets is used for the info (data1 or data2).
"Reqcol" sets which column from the data sheet is reqd.

(e.g. Cell G4 on the report might be =vallookup(G1,A1,"data2","E"))

This has to loop over the data checking all references, and if it finds a match then check the date. If both match, it returns the value in column (reqcol).

The data size is not known until the data is refreshed (cell A1 contains the number of rows).
A variable, 'lastrow', is used as the number of rows and a number of loops is performed.

The function appears to work sometimes, but other times won't work - on the same data!
Also, I need to have an easy way to refresh the values returned by the funcion on the report page without having to hit 'Enter' on each cell.

Here is the function as it stands......

Public Function vallookup(ref As String, dateref As Date, page As String, reqcol As String)
    'Activate correct page sheet
    Worksheets(page).Activate
    'Get size of dataset
    lastrow = Worksheets(page).Cells(2, "A").Value
    'Loop data
    For a = 1 To lastrow
        If (UCase(ref) = UCase(Worksheets(page).Cells(a, "B").Value)) Then
            If (dateref = DateValue(Worksheets(page).Cells(a, "A").Value)) Then
                vallookup = Worksheets(page).Cells(a, reqcol).Value
            End If
        End If
    Next a
End Function


Sub Refresh()
    'Attempt at a Refresh function
        Worksheets("report").Calculate
End Sub
 
Old November 16th, 2006, 02:45 PM
Authorized User
 
Join Date: Jul 2006
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just a wuick look would seem to indicate that the statement to set "lastrow" should reference Cells(1,"A") is the value is at cell A1. Additionally, it seems there should be an Exit Function at the end of the innermost If statement unless you want the return of the last matching data. Hope this helps.

Terry





Similar Threads
Thread Thread Starter Forum Replies Last Post
Multi-Column Array Search RollingWoodFarm Excel VBA 5 August 1st, 2006 06:45 PM
DataGrid Multi-Column pbyrum Classic ASP Professional 4 October 5th, 2005 06:02 PM
how to do the random search and compare? hsfoo82 PHP How-To 1 April 1st, 2005 11:36 AM
multi-column combo myth12345 VB How-To 1 January 3rd, 2005 04:29 PM
Compare two Items of data(in column A and column B ever Excel VBA 6 February 13th, 2004 02:19 PM





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