We help IT Professionals succeed at work.

Excel VBA - Function to look up row & return cell contents

wileecoy
wileecoy asked
on
Medium Priority
991 Views
Last Modified: 2007-12-19
I need some code tweaking.

I have 2 Excel Workbooks.

Workbook 1 needs a function to find a matching string in column A and return cell contents from column B of Workbook2.Worksheet??.  The worksheet will need to be passed as a parameter in the function.


I have an idea of the structure of the coding, however, I don't code in Excel VBA so feel free to correct or start over.

Public Function GLAmt(Acct As String, FileName As String, TB As String) As Double

Dim wBook As Workbook
Dim wSheet As Worksheet

'Set variable for storing amount retrieved from acct # Column
Dim AMT As Double
'Dim variable for storing row number of found cell (if needed)
Dim x As Integer

Set wBook = FileName
Set wSheet = wBook.Worksheets(TB)

'Go To TB Worksheet



'Look for Acct # in Column ___



'Get Amount from Column ___
'x = row that acct # was found

AMT = wSheet.Cells(x, B).Value


'Set Function variable equal to amt variable.
GLAmt = AMT



End Function

Thanks.
Comment
Watch Question

Commented:
Did you try recording a Find within a column?

That should give you all the code you need to drop into any place you want.

Author

Commented:
rspahitz,

Good suggestion - I've used it before.  However, when I do that with the Lookup function I get the following:

Sub GLLookup()
'
' GLLookup Macro
' Macro recorded 09/24/2001
'

'
    ActiveCell.FormulaR1C1 = _
        "=LOOKUP(RC[-1],[LookupTest.xls]Sheet2!R2C2:R42C2,[LookupTest.xls]Sheet2!R2C4:R42C4)"
    Range("B4").Select
End Sub

The problem is in the 'Range("B4").Select".

That is a specific cell reference.  I cannot use that in a standard function.  I don't know how to do it so that I can create a dynamic Function.

Any Ideas?

Wileecoy.
Commented:
wileecoy

I don't really understand WHY you want to create this function in VBA. Excel already has a built-in worksheet function (VLOOKUP) which does exactly what you ask for.

Use it like this in a worksheet cell:
=VLOOKUP(A1,[wb2.xls]Sheet1!$A:$B,2,FALSE)

Or use it like this in VBA (the workbook with the lookup table is assumed to be open).

Function GLAmt(Acct As String, wbName As String, wsName As String) As Double
 
  'Declare variables
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim rng As Range
 
  'Initialize variables
  Set wb = Workbooks(wbName)
  Set ws = wb.Sheets(wsName)
  Set rng = ws.Range("A:B")
 
  'Set return value
  GLAmt = WorksheetFunction.VLookup(Acct, rng, 2, False)
 
  'Release object variables
  Set rng = Nothing
  Set ws = Nothing
  Set wb = Nothing
 
End Function

Ture Magnusson
Karlstad, Sweden

Commented:
Since "B4" is a string, you could pass in either a string containing a cell, or Row/Col coordinates and then build the string:

Sub GLLookup(Col as integer, Row as integer)
  Dim strCellName as string
  dim strColLetter as string

  if Col <= 26 then
    strCellName = chr$(Col + 64) & mid$(str$(Row),2)
  else
    ' get the right-most letter
    strColLetter = chr$((Col mod 26) + 64)
    ' prefix with the left letter
    strColLetter = chr$(int(Col/26)*26) & strColLetter
    ' ^^^^^^^^^^^^^^^^
    strCellName = strColLetter & mid$(str$(Row),2)
  endif
'
' etc.
end Sub


The line I haven't really tested is the one with the ^^^ pointing to it, but it's supposed to figure out the first letter of a double-letter column, where the assumption is that A=1, B=2, ...Z=26, AA=27, ... AZ=52, BA=53, etc.

Author

Commented:
ture,

Late last night I came up with the same solution.

The reason that I need this is the following:

I am looking up amounts from a trial balance like so.

Acct#          Amt
1100          1,000
1200          2,000
1300          3,000
1400          4,000
1500          5,000

Using the lookup function will only let me select one amount based on the account number.

I need to be able to have more than one account in the parameter passed.  Something like "1100,1300,1500" and have the function accumulate the lookup results.

So - as I mentioned - late last night I came up with the same solution.

Good observation questioning why I needed a function to do this as my question didn't specify the accumulation requirements.

Your train of thought followed mine to get to my solution.  So - you get the points.

btw - cool name.

Thanks.

Wileecoy.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.