Help with Loop

[qitjch]

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!

[p45cal]

Supply a file please to play with (link to one (missing out the http:// bit) if you can't upload one here yet).

[SamT]

With Sheets IPV-Oracle
Column A = Item#
Column S = Return from Sheets CommCode, Column E where Item# is in Column B

Problem: If Item# is not in CommCode, Column B, Add Item#, Copy Formulas down



Replace the Formulas in Column S with

=GetCommCode(A)

And place this code in a standard Mopdule

Option Explicit

Function GetCommCode(ItemCell As Range) As Variant
Dim LastItem As Range
Dim Found As Range

With Sheets("CommCode")
  Set Found = .Range("B:B").Find(ItemCell)
  If Not Found Is Nothing Then
    GetCommCode = Found.Offset(0, 3)
    Exit Function
  Else
    Set LastItem = .Cells(Rows.Count, "B").End(xlUp)
    LastItem.Resize(, 4).Copy LastItem.Offset(1)
    LastItem = ItemCell
  End If
End With
End Function

If some other name makes more sense to you, replace all three instances of "GetCommCode" with that name

[qitjch]

Can you modify this so that it matches the entire cells contents and not just a partial? Other than that, its working well!

[SamT]

Can you modify this so that it matches the entire cells contents and not just a partial?

I don't understand the situation.

[qitjch]

I don't understand the situation.

I went to test this function by plugging in item#'s that I knew did not exist on the CommCode tab. For example, I used 1, 5, 15, 25, and 1 again. When plugged into the function as ItemCell, they should not be found on the CommCode and as a result should need to be added. However, they are not being added to the CommCode tab nor are the formulas being copied down 1 row. Ideas?

Don't have enough posts yet to give you a link to the test file.

[SamT]

When plugged into the function as ItemCell,

Did you put 25 in column A on sheet IPV-Oracle" and put the formula =GetCommCode(ARowNum) in Column S?

I am thinking that I wasn't clear in my instructions in post #3.

The formula in Cell S1 should be =GetCommCode(A1) and then copy it down to the bottom of the used range.

You can insert the Function into a formula just like you do any Excel Function with the Fx button on the Formula bar. It is a Category "User Defined" Function.

Don't have enough posts yet to give you a link to the test file.

IF you use the "Go Advanced" button, under the new Advanced Editor, you will see a "Manage Attachments" button that will let you uplaod a test book here.

[qitjch]

Did you put 25 in column A on sheet IPV-Oracle" and put the formula =GetCommCode(ARowNum) in Column S?

I am thinking that I wasn't clear in my instructions in post #3.

The formula in Cell S1 should be =GetCommCode(A1) and then copy it down to the bottom of the used range.

You can insert the Function into a formula just like you do any Excel Function with the Fx button on the Formula bar. It is a Category "User Defined" Function.

IF you use the "Go Advanced" button, under the new Advanced Editor, you will see a "Manage Attachments" button that will let you uplaod a test book here.

I've attached a cleaned up version of the file I'm using this function in. As you can see in the first few rows of the PPV-Oracle tab, I have added random numbers into column A and then in column S, I have added the GetCommCode function you provided. If you look, you'll see that any item#'s not being found are not getting added to the Comm Code tab like they should. In addition, these item#'s are being found as partial matches within larger item#'s. For example, it is finding item# "10" in item#" 00000100001" when in fact item# 10 does not exist on the CommCode tab.

File size was too large for me to upload to this post. Here is a link to the file.

[p45cal]

file needs permission.

[qitjch]

file needs permission.

Apologies. Should be good now, made it public.

[p45cal]

Functions can be dodgy when asked to do things on sheets, sometimes they work but mostly not, really they're only supposed to return values.
In the attached you'll find a button on sheet PPV-Oracle (note it's not IPV-Oracle) in the vicinity of cell ZZ1.
Clicking it runs this macro:

Sub blah()
Dim CellsToProcess As Range
Set Sht = Sheets("PPV-Oracle")
With Sheets("CommCode")
  On Error Resume Next
  Set CellsToProcess = Sht.Range("S:S").SpecialCells(xlCellTypeFormulas, 16)
  On Error GoTo 0
  If Not CellsToProcess Is Nothing Then
    For Each cll In CellsToProcess.Cells
      zzz = Application.Match(Sht.Cells(cll.Row, "A").Value, .Range("B:B"), 0)
      If IsError(zzz) Then
        Set LastItem = .Cells(.Rows.Count, "B").End(xlUp).Offset(1)
        LastItem.Value = Sht.Cells(cll.Row, "A").Value
        'LastItem.Offset(-1, 1).Resize(, 6).Copy LastItem.Offset(, 1)
      End If
    Next cll
  End If
End With
End Sub

Note that there are no formulae to copy in CommCode in the sample file, hence a commented-out line in the macro, which you should enable to do that and test.
For the code to work on a different sheet such as IPV-Oracle adjust the line:
Set Sht = Sheets("PPV-Oracle")

File too big to upload here so it's here: https://app.box.com/s/avalec4n3jmv4wzocftg16kflbj76b2z

[SamT]

There is no way that little bit of data should be 6MB. I ran ExcelDiet on it and here is that version of the file.

[p45cal]

OK, then here's the trimmed version with the macro.

[SamT]

Function GetCommCode(ItemCell As Range) As Variant
     
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual
          
          Call AddItem(ItemCell.Value)
            DoEvents

GracefulExit:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Function

Sub AddItem(ByVal NewItem As String)

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual

    Sheets("CommCode").Range("B12") = "Sub terminates at this line."
    MsgBox "You Can't see me!"

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

[p45cal]

SamT, I haven't been able to get the MsgBox up if I use the Function on the sheet.
Do you get B12 to change and the message box up? A bit confused..

[SamT]

I found thru extensive rewriting that as soon as an attempt is made to change a value on the sheet, the Sub terminates without notice and control reverts back to Excel or the Function. I didn't check to see which one, I was getting frustrated, but I suspect Excel..

From this, it seems that your Button & Sub are the only way to go. Maybe an Event Sub, but we need more info from the OP to know that.

[Aussiebear]

Maybe I'm reading this all wrong, but my impression is;
With Sheets(PPV_oracle)
For each cell in range S3: B & LRow, if the Value is #N/A then,
Use the Value in Column A of that row as a lookup value on sheet CommCode,
If Found then do nothing

This does not fix the issue of the #N/A, which is what you were initially chasing. The #N/A value should be replaced by the value in Column D of CommCode

[SamT]

Yeah, you need glasses. If the VLookUp fails, he wants to add the missing value to the CommCode sheet and copy down the formulas in the adjacent columns.

[p45cal]

Exactly:

For each cell in range S3: B & LRow, if the Value is #N/A then

Well, only column S needs to be looked at:
Set CellsToProcess = Sht.Range("S:S").SpecialCells(xlCellTypeFormulas, 16)
the 16 is xlErrors so returns just formula cells with errors. Then for each of those cells:
For Each cll In CellsToProcess.Cells

Use the Value in Column A of that row

Sht.Cells(cll.Row, "A")

as a lookup value on sheet CommCode

well, exact MATCH in column B of CommCode:
zzz = Application.Match(Sht.Cells(cll.Row, "A").Value, .Range("B:B"), 0)

If Found then do nothing

if found, zzz is not an error and do nothing, but if it is:
If IsError(zzz) Then

This does not fix the issue of the #N/A, which is what you were initially chasing. The #N/A value should be replaced by the value in Column D of CommCode

This does by adding it to the bottom of the list in CommCode:
Set LastItem = .Cells(.Rows.Count, "B").End(xlUp).Offset(1)
LastItem.Value = Sht.Cells(cll.Row, "A").Value
and copying formulas in CommCode to that new line:
LastItem.Offset(-1, 1).Resize(, 6).Copy LastItem.Offset(, 1)
see msg#11.

[Aussiebear]

I fell out of the boat this morning so am having concentration issues..... and I'm sticking with my story.