PDA

View Full Version : [SOLVED] Find with additional requirement



cwhite86
02-26-2016, 11:20 AM
I am developing an inventory tool that checks our manually entered data for our physical inventory checks against the computer inventory list of what we should have in inventory.

So far it is working well, but I need to up the accuracy. I have attached a desensitized sample Workbook.
Sheets names etc. will be different, and product name spacing etc.

So my current hurdle is this.

Module 2 is currently looking on the 2 Physical INV pages (BI2c and BW2C) for matches.
Column C defaults everything Red, then fills anything found yellow.

But this only matches the reference number.

I need to be able to match each reference number with the cell to the left of it.

So Sheet INV Master is looking in its own Column C for matching values in (all sheets, but lets be specific for a moment) Sheet BI2C in Range B5:B15. So INV Master C2 is found, but I want to know if B2 matches the column the value in C2 is found. The match for B2 is found on BI2C B$3.

Right now if the value in C2 is found under a different category it still lists as found because I am searching the whole range.

Not sure if I can use a find with an and as an added restriction or is there is a better way to do this.

Anyone got any ideas? Or understand my rant?

SamT
02-26-2016, 02:41 PM
Well I guarantee that B2 is not found in B5:B15 and it wont be found in B3 either. The only place you will find B2 is B2.

p45cal
02-26-2016, 04:53 PM
Module 2 is currently looking on the 2 Physical INV pages (BI2c and BW2C) for matches.
Column C defaults everything Red, then fills anything found yellow. Actually, the code in Module2 colours column A red and looks for the values as part of the cells in column A. Column C is not referred to at all.


But this only matches the reference number.
I need to be able to match each reference number with the cell to the left of it. The code below replaces all of your macro Check_INVMaster and checks column C and the cell to the left of each find.
Dim myRange, v, F, A, ws1, ws2, ws4, Rng

Set ws1 = Worksheets("INVMaster")
Set ws2 = Worksheets("BI2C")
Set ws4 = Worksheets("BW2C")
Set myRange = ws1.Range("C1:C5000")

ws1.Activate
myRange.Interior.ColorIndex = 3
For Each Rng In Array(ws2.Range("B5:k100"), ws4.Range("B5:H50"))
For Each v In Rng.Cells
If Len(Trim(v.Value)) > 0 Then
Set F = myRange.Find(what:=v, lookat:=xlPart)
If Not F Is Nothing Then
A = F.Address
Do
If F.Offset(, -1).Value = v.Parent.Cells(3, v.Column).Value Then F.Interior.ColorIndex = 6
Set F = myRange.FindNext(F)
Loop While Not F Is Nothing And F.Address <> A
End If
End If
Next v
Next Rng 'Start Next Worksheet (Rng)
End Sub

cwhite86
02-29-2016, 04:48 AM
Sam T


The match for B2 is found on BI2C B$3.

Not sure i follow your meaning, as I referenced that B2 is found on another sheet named BI2C in cell B3.


p45cal, thanks for your help. I've never used the Parent Cell reference, but now that i see that in action I am sure I will use that more often.
So I am assuming I can continue to add sheets to the Array?


For Each Rng In Array(ws2.Range("B5:k100"), ws4.Range("B5:H50"))

I had been copy and pasting this script end to end for each sheet, this is much simpler!


I apologize for inconsistencies in my wording, this isn't my full time job. I am a floor supervisor and often get interrupted throughout the day, coming back to this hours later. This is a project I took on for personal growth, as well as trying to make my life a little easier here.

cwhite86
02-29-2016, 04:49 AM
Is there a way to add reputation on this forum? I would surely do so!

p45cal
02-29-2016, 05:51 AM
So I am assuming I can continue to add sheets to the Array? Yes.