Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup (Excel VBA)

    Please help. Need Code. I would like to lookup a result for a code (ie testCode) in rawdata.xls for each flask (ie. 1, 2, 5, 6) in column A of datasheet.xls. rawdata.xls is a table with many codes across row 1 and flasks in column A. Datasheet just has one code and flasks in Column A. So I would like to loop thru the flasks in datasheet.xls and look for the result for testCode in rawdata.xls and paste the result in column B of datasheet.xls. What a mess/hope it makes sense.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Excel VBA)

    Your description leaves me dizzy. Can you post a demo workbook that demonstrates what you want (it can be smaller than your "real" workbook)? (For simplicity, you could put the DataSheet.xls and RawData.xls as worksheets in one workbook.)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (Excel VBA)

    What the plan is to loop thru many sheets like the rawdata.xls to create datasheet.xls for each test code. Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Excel VBA)

    Rawdata has values 1, 4, 6, 8, ... in the Flask column, while Datasheet has values 1, 2, 3, 4, ... in the Flask column.
    Is your intention to populate only those cells in column B of Datasheet that correspond to values in 1, 4, 6, 8, ... from Rawdata (and use other sheets like Rawdata to populate the rest)?

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (Excel VBA)

    Thats the plan. Some cells in Datasheet may be left empty.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Excel VBA)

    Here is a macro that will do what you want (I hope).

    Sub FillDatasheet()
    Dim shtSource As Worksheet
    Dim shtTarget As Worksheet
    Dim rngSource As Range
    Dim rngTarget As Range
    Set shtSource = Worksheets("Rawdata")
    Set shtTarget = Worksheets("Datasheet")
    Set rngSource = shtSource.Range("A1").CurrentRegion
    Set rngTarget = shtTarget.Range(shtTarget.Range("A2"), shtTarget.Range("A2").End(xlDown)).Offset(0, 1)
    rngTarget.Formula = "=INDEX(" & rngSource.Address(True, True, xlA1, True) & _
    ",MATCH(A2," & rngSource.Columns(1).Address(True, True, xlA1, True) & _
    ",0),MATCH($B$1," & rngSource.Rows(1).Address(True, True, xlA1, True) & ",0))"
    rngTarget.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
    End Sub

    It is independent of the size of the source and target areas, but assumes that they start in cell A1 in both sheets, and that they are contiguous. Instead of looping through the cells, it fills column B in the target sheet with lookup formulas, then clears those that result in an error because the lookup value was not found.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (Excel VBA)

    Works good but returns a zero if it finds the flask but no number is there. That's a problem

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Excel VBA)

    You didn't mention that parts of the lookup table could be empty, and your sample workbook didn't make that clear, either. If the values are never equal to 0, you can add these lines to the macro:

    Dim oCell As Range
    For Each oCell In rngTarget
    If oCell = 0 Then
    oCell.Clear
    End If
    Next oCell

    But if some values can be 0 "legitimately", it'll be more complicated.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (Excel VBA)

    Yes, values can be 0. I've been working on a loop with a vlookup but it get hung on this line when it can't find a flask. Not all the rawdata sheets have all the flasks.

    result = Application.WorksheetFunction.VLookup(flask, Range("A1:P100"), counter, False)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup (Excel VBA)

    You can try to trap the error. The following is air code, it should be refined

    On Error GoTo ErrHandler
    Result = Application.WorksheetFunction.VLookup(Flask, Range("A1:P100"), Counter, False)
    ' Instructions to process Result go here
    ...
    ...
    ContinueHere:
    ' Instructions to be executed after Result has been processed, for example loop back
    ...

    Exit Sub

    ErrHandler:
    If Err = 1004 Then
    ' This error occurs if VLookup fails
    Resume ContinueHere
    Else
    ' Notify user of other errors
    MsgBox Err.Description
    End If

    End Sub

  11. #11
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup (Excel VBA)

    It works, you rock

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •