Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013

    Unanswered: Macro to Find Data in Another Sheet

    I have Sheet1 with Column A being a list of all US ZipCodes.

    Sheet2 has Column A listing out only the zipcodes where we have sales and Column B has the amount of sales in that zip code.

    Ideally, here's what I want it to do:
    I want it to select A2 on Sheet1
    Search for that term on Sheet2
    If it doesn't exist on Sheet2, place a "0" in B2, then automatically move on to A3 and continue the process
    If it does exist in Column A on Sheet 2 - select the cell to the right of the result (Column B)
    Then paste that cell's contents in B2 on Sheet 1
    Then continue until ALL of column A on Sheet1 has been searched for on Sheet2.

    I have tried tons of macros, but they don't seem to work because not all of the zip codes in Column A Sheet1 exist in Sheet2...

    Here's one the one I've been using... it only works for about 5 lines before it comes up with an error... ANY HELP would be greatly appreciated. It's frustrating not being able to figure it out :/ I really don't want the MsgBox to pop up at all...

    Sub abc()
    ' abc Macro
    'Keyboard Shortcut: Ctrl+b

    Do Until IsEmpty(ActiveCell)
    Dim MyString As String
    MyString = ActiveCell
    Set RangeObj = Cells.Find(What:=MyString, After:=ActiveCell, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False)
    If RangeObj Is Nothing Then MsgBox "Not Found" Else: RangeObj.Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Offset(-1, -1).Select
    End Sub

    Please let me know what I'm doing wrong

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    Why not just use VLookup? You can wrap it in IF(ISERROR(...)...) [if you're working with Excel pre-2007] or IFERROR(...) [if you're working with Excel post-2007] to return 0 for zip codes with no sales.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Tags for this Thread

Posting Permissions

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