Macro required to find matches and return value in the next column.

**excelvba123** · 05-05-2012, 10:37 AM

Hi All,

I have attached a worksheet for your reference. I am a new starter in the world of data validation. My task includes to make sure that I only pay for jobs which are original and not invoiced to us in the past. In order to solve duplication issue, I want to compare two worsheets, sheet 1 has the jobs invoiced to us for the current month and sheet two has got standard report from the past 12 months giving all jobs invoiced to us in the past 12 months. I need a VBA based macro to find all matching jobs which are in sheet 1 column A, in sheet2 column A and return the corresponding value in column B in sheet 2. Vlookup is a standard answer but because there can be a max of 1000 jobs invoiced to us in any given month, this is a tedious task. I did try to record macro to find these jobs and return the month by making the colour of cell red but every time i run that macro it has run time errors and it doesnt change the format of the months and gets stuck basically.

I have attached the spreadsheet for help please.

Any help woud be grearly appreciated.

Thanks & Regards
Manav

**FDibbins** · 05-05-2012, 10:52 AM

not sure why you need vba for this, i modified your formula so that you can copy it down as far as you need, and it will only show a result (0 or a value) if there is data in column A. see the attached

Also, i noticed that your cell reference into A looked wrong. seems it is referencing the row below it? eg the formula in A2 was looking up the value in A3

**excelvba123** · 05-05-2012, 11:14 AM

Hello (FDibbins)

Thank you for your reply. Yes you are right regarding cell reference, actually I made the job numbers myself at random as I dont have access of the actual sheet at work. I was trying to explain the situation with a sample sheet also the reason I need vba is because I need to automate this at a touch of a button, basically if any new starter in the team it will save a lot of time in explaining formulas. I already have used a vba code(given below) to find duplicates within column A in itself and then assign that macro to a button. So just one press and all duplicates within column A would turn red. The same thing i want for the comparison as well through a vba macro.

Thanks
Manav

Please Login or Register  to view this content.

**FDibbins** · 05-05-2012, 11:48 AM

ok, what i was trying to do was let you copy my formula down a looooong way, and it would populate only if data was entered.

my VBA is poor at best, is i will let 1 of the others pick this 1 up from here
Also, you dont need the "" when typing in [code]

**AlvaroSiza** · 05-05-2012, 12:00 PM

If it must be VBA, this should do the trick. I admit that this is not the most efficient approach (which would be to analyze the entire thing in array and pass back...I'm working on that one).

Please Login or Register  to view this content.

**TMS** · 05-05-2012, 12:11 PM

You don't need VBA for this.

If you select all of column A on Sheet 2 and name it as S2NGM, you can use Conditional Formatting on the entries in Sheet 1.

The CF formula would be: =COUNTIF(S2NGM,A2)>0 applied to cell A2 down.

I note that your VLOOKUP is offset and not highlighting the correct entries. On row 11, you are referring to cell A12.

Regards, TMS

**AlvaroSiza** · 05-05-2012, 10:02 PM

OP, you agreed when you signed up not to cross-post without providing information as to having done so and a link. Mods, do as you will.

http://www.ozgrid.com/forum/showthread.php?t=165089

**excelvba123** · 05-07-2012, 06:47 AM

Dear Sir (AlvaroSiza):

I was not expecting to get this results, it woked seamlessly, i/m just too excited to see the VBA power. No doubt programming always has an upper hand to excel formulas. I am so grateful to you, cant just express in words. Also I didnt quite understood your second post regarding some rules about posting, what was that about please.

Billion thanks sir.

Originally Posted by AlvaroSiza

If it must be VBA, this should do the trick. I admit that this is not the most efficient approach (which would be to analyze the entire thing in array and pass back...I'm working on that one).

**excelvba123** · 05-07-2012, 06:50 AM

Thanks very much (TMShucks), I think I got it what i was looking for, your efforts are much appreciated. Many Regards Manav

**AlvaroSiza** · 05-07-2012, 11:11 AM

Originally Posted by excelvba123

Also I didnt quite understood your second post regarding some rules about posting, what was that about please.

It is called cross-posting when you post in multiple forums looking for an answer to your question. This is frowned upon as the volunteers who take time to help you in one forum might be wasting their time as an answer might already be developed elsewhere. One of the rules that you agreed to when you signed up was to NOT cross-post. I found your identical query on OzGrid.

**excelvba123** · 05-08-2012, 02:29 AM

You are absolutely right Sir, I really dont want to waste the precious time of senior members.

I will make sure that I dont repeat that in future. Many Thanks & Regards for your esteemed help.

Originally Posted by AlvaroSiza

It is called cross-posting when you post in multiple forums looking for an answer to your question. This is frowned upon as the volunteers who take time to help you in one forum might be wasting their time as an answer might already be developed elsewhere. One of the rules that you agreed to when you signed up was to NOT cross-post. I found your identical query on OzGrid.

**excelvba123** · 05-08-2012, 06:45 AM

Hi there,

I have just started to use the code at work and it showing me the following error(Runtime error 9 Subscript out of range (Set ws2 = ActiveWorkbook.Sheets("Sheet2"), I have uploaded the actual file(Testfordups_Master)and I have assigned the second button (Find Duplicates since April) to the macro called compare, please advise further.

Thanks
Manav

Originally Posted by AlvaroSiza

It is called cross-posting when you post in multiple forums looking for an answer to your question. This is frowned upon as the volunteers who take time to help you in one forum might be wasting their time as an answer might already be developed elsewhere. One of the rules that you agreed to when you signed up was to NOT cross-post. I found your identical query on OzGrid.

**TMS** · 05-08-2012, 08:40 AM

You're code looks for "Sheet2", no spaces; the worksheet name is "Sheet 2", with a space.

Change one or the other to match.

Regards, TMS

**excelvba123** · 05-09-2012, 02:08 AM

Spot on, thanks a trillion. Many Thanks & Regards.

Originally Posted by TMShucks

You're code looks for "Sheet2", no spaces; the worksheet name is "Sheet 2", with a space.

Change one or the other to match.

Regards, TMS

**TMS** · 05-09-2012, 03:33 AM

You're welcome.

Macro required to find matches and return value in the next column.

LinkBack

Thread Tools

Rate This Thread

Display

Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Re: Macro required to find matches and return value in the next column.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions