Macro Help-Advanced

[vbmulk]

This is way above my head but hoping you guys can help. I don't have much experience with Macros but would like to do the following:

Info:

Sheet1 has a range of numbers in cells A1-A20
Sheet2 range of numbers in cells A1-A15

I currently have a match formula on sheet 1 that says if the number in the Sheet 1 range is matched in the Sheet 2 range then highlight the cell. I actually want to highlight the numbers that don't match but couldn't figure out how to do that so I just settled with knowing that the cells not highlighted are the ones that don't match. Anyway, once I find the numbers that don't match on Sheet 1 I need to add them to Sheet 2. I don't necessarily care if they are added to the end of the range or in numerical order since I can sort the list if I need to.

Is there a way to create a Macro so that this can be done with the click of the mouse?

Thanks for the help.

 

[Snakehips]

vbmulk,

Firstly, re the formula you have for highlighting the numbers that do match.

If you have eg =ISNUMBER(MATCH(A1,Range2,0))

To change so that it highlights the non-matches use NOT()

eg =NOT(ISNUMBER(MATCH(A1,Range2,0)))


Try this code to copy 5 non-matches to sheet2 cells 16 to 20....

Sub Add_Five_Nos()
For Each cell In Sheets("Sheet1").Range("A1:A20")
If WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A15"), cell) = 0 Then
c = c + 1
Sheets("Sheet2").Range("A15").Offset(c, 0) = cell
If c > 5 Then Exit For
End If
Next cell
End Sub

Hope that helps.

 

[vbmulk]

Tony, you are a genius. Thanks. What if I wanted to have the description that is in column B put on sheet 2 also. For example, if sheet 1 had #1350 in column A with a description "windows" in column B and since that #1350 is not on sheet 2 the Macro will add #1350 to sheet 2 along with the description next to it.

Thanks again.

 

[Snakehips]

vbmulk,

This should do it.....

Sub Add_Five_Ranges()
For Each cell In Sheets("Sheet1").Range("A1:A20")
If WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A15"), cell) = 0 Then
c = c + 1
Sheets("Sheet2").Range("A15:B15").Offset(c, 0) = cell.Resize(1, 2).Value
If c > 5 Then Exit For
End If
Next cell
End Sub

 

[vbmulk]

thanks again Tony. I would love to be able to learn this. Where should I begin? A class on VBA code?

 

[vbmulk]

Actually Tony, one more glitch and then I should be good. What if the range on Sheet2 will vary. For example one worksheet might have 15 rows and the next might have 25. The Macro looks like it adds rows starting at cell 16 on sheet 2. Is there a way to say add rows starting right after the last row with data?

 

[Snakehips]

vbmulk,

Where should you begin? Good question.


If you can afford the $39 or thereabouts then I would recommend the following by MrExcel himself, Bill Jelen.
Excel VBA and Macros with MrExcel LiveLessons (Video Training) | Que


Available on disc or as download It will give you a good understanding vba and macros.


Once you have that understanding then the world is your lobster.


There are books galore out there but there is so much stuff, just a Google away that I’m not sure that books are a real necessity.
YouTube is a fantastic source of informative videos. MrExcel (Bill Jelen) and Excel’sFun (Mike Girvin) alone have hundreds of Excel videos on YouTube including some that cover vba.


Here is a link to some resources compiled by Hiker95 check it out in post #3


Good luck.

 

[vbmulk]

Thanks for the info Tony. I had one other post that I'm not sure you saw since I posted two at once. Here it is again:

Actually Tony, one more glitch and then I should be good. What if the range on Sheet2 will vary. For example one worksheet might have 15 rows and the next might have 25. The Macro looks like it adds rows starting at cell 16 on sheet 2. Is there a way to say add rows starting right after the last row with data?

 

[Snakehips]

Sorry, overlooked your post #6.

Can you confirm that you are still talking 20 rows in sheet 1 and looking for 5 numbers that do not match the Top 15 in sheet2.

Then add those 5 to the bottom of the list in sheet 2.

 

[vbmulk]

I plan on using this Macro to apply to several client account numbers and descriptions so the ranges on sheet 1 and sheet 2 will not be constant. For example one client may have a list of 20 accounts and another may have 50. I changed your range for sheet 1 to A1:A300 since I don't expect more than 300 accounts on either sheet 1 or sheet 2. Once the macro checks sheet2 for the account on sheet1 I then want it to add the accounts and description not on sheet2 to the end of the data on sheet2. The end of the data could be 15 rows or it could be 50 rows depending on the number of accounts the client has.

A workaround if this is not possible is to have the macro add the missing accounts starting at row 300 on sheet2 since I know a client won't have more than 300 accounts. Then I can just sort the rows after it adds them to pull them up.

Again, thanks for your time with this. Maybe next year, after a few courses I'll be on here with you. ; )