03-13-13, 13:11 #1Registered User
- 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...
' 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, _
If RangeObj Is Nothing Then MsgBox "Not Found" Else: RangeObj.Select
Application.CutCopyMode = False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Please let me know what I'm doing wrong
03-18-13, 12:36 #2Grumpy old man (training)
Provided Answers: 10
- Join Date
- Sep 2006
- Surrey, UK
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: 1