PDA

View Full Version : [SOLVED] Help with Loop



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!

p45cal
01-21-2016, 11:27 AM
Supply a file please to play with (link to one (missing out the http:// bit) if you can't upload one here yet).

SamT
01-21-2016, 12:22 PM
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
01-21-2016, 12:38 PM
Can you modify this so that it matches the entire cells contents and not just a partial? Other than that, its working well!

SamT
01-21-2016, 12:44 PM
Can you modify this so that it matches the entire cells contents and not just a partial?
I don't understand the situation.

qitjch
01-21-2016, 02:47 PM
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
01-21-2016, 03:31 PM
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
01-22-2016, 10:06 AM
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. (https://drive.google.com/open?id=0B7oMo2XZIUFkVEo1X3V6QzJLU2M)

p45cal
01-22-2016, 10:16 AM
file needs permission.

qitjch
01-22-2016, 11:09 AM
file needs permission.

Apologies. Should be good now, made it public.

p45cal
01-22-2016, 01:27 PM
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
01-22-2016, 01:44 PM
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
01-22-2016, 02:47 PM
OK, then here's the trimmed version with the macro.

SamT
01-22-2016, 03:24 PM
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
01-23-2016, 05:27 AM
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
01-23-2016, 04:08 PM
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
01-23-2016, 07:33 PM
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
01-23-2016, 08:19 PM
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
01-23-2016, 08:26 PM
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
01-24-2016, 12:41 AM
I fell out of the boat this morning so am having concentration issues..... and I'm sticking with my story.

Aussiebear
01-24-2016, 12:42 AM
Besides, what does the GetCommCode do?

I'm assuming here that the sheet ppv.-oracle is an imported sheet of data. The CommCode sheet has been setup as a Checks and Balance sheet. Columns S (Team Desc) & T (Secondary Desc) of the ppv-oracle sheet need to be filled with relevant data based on that which is held within the CommCode sheet.

So.... I would test each cell in Column A of ppv-oracle to see if it exists in CommCode and failing that test add the new string to CommCode Sheet as a new row. If it does exist then fill Columns S & T from the data in CommCode sheet

qitjch
01-25-2016, 11:09 AM
I've tested both methods and they seem to be working flawlessly! I'll let you guys know if I run into any more issues with this report. Thanks again for the help, much appreciated!