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).
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!
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.
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
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.
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..
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
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.