qitjch

01-21-2016, 09:36 AM

In my workbook, I have worksheet("IPV-Oracle"). Starting in row 3, this sheet lists thousands of transactions each with an item# in column A. In column S, I have a vlookup that takes the item# and searches for it in Sheets("CommCode").Range("B:E") and then it reports back column E. Columns C, D, E, F, G, and H on the CommCode tab are all formulas that pull information from other workbooks.

On any given day, many of the vlookups result in #N/A. This can happen for 1 of 2 reasons.

1) The item# is not listed on the CommCode tab.

2) The item# is listed on CommCode, but the formula in column E, that it reports back, is #N/A.

Currently, I manually check each individual #N/A found on IPV-Oracle and do a ctrl+f for that item# on the CommCode tab. If it is found, I move on to the next #N/A. If it isn't found, then I add the item# to the next blank row in Column B and copy the formulas from columns C:H down to the new row.

This process can be quite tedious as sometimes there are hundreds of item#'s that do not show up on CommCode tab. I am looking for a way to loop through every #N/A in column S of the IPV-Oralce sheet and search the corresponding item number in column B of the CommCode tab. If found, move on to the next #N/A. If not found, then add that item# to the next blank row in column B and finally copy the formulas in columns C,D,E,F,G, and H down a row. If this results in column S of IPV-Oracle still returning #N/A, then move on to the next #N/A.

I am new to VBA and am not quite sure the best way to approach this. I've attempted to use some of the code I found on google, but nothing seems to quite match up with what I am trying to accomplish. Any suggestions or tips would be greatly appreciated.

Thanks!

On any given day, many of the vlookups result in #N/A. This can happen for 1 of 2 reasons.

1) The item# is not listed on the CommCode tab.

2) The item# is listed on CommCode, but the formula in column E, that it reports back, is #N/A.

Currently, I manually check each individual #N/A found on IPV-Oracle and do a ctrl+f for that item# on the CommCode tab. If it is found, I move on to the next #N/A. If it isn't found, then I add the item# to the next blank row in Column B and copy the formulas from columns C:H down to the new row.

This process can be quite tedious as sometimes there are hundreds of item#'s that do not show up on CommCode tab. I am looking for a way to loop through every #N/A in column S of the IPV-Oralce sheet and search the corresponding item number in column B of the CommCode tab. If found, move on to the next #N/A. If not found, then add that item# to the next blank row in column B and finally copy the formulas in columns C,D,E,F,G, and H down a row. If this results in column S of IPV-Oracle still returning #N/A, then move on to the next #N/A.

I am new to VBA and am not quite sure the best way to approach this. I've attempted to use some of the code I found on google, but nothing seems to quite match up with what I am trying to accomplish. Any suggestions or tips would be greatly appreciated.

Thanks!