Find One Cell Value Across many Columns

[Drumin_Phreak]

Hello,

I have a question that i am trying my best to ask. I want to be able to find a SINGLE Value across a range of columns.

The best way i can explain this is I have a value of "ABC7190612" in a cell.

I need to find ABC in the "Product" Column
I need to find 7 in the "Year" Column
I need to find 19 in the "Day" Column
I need to find 06 in the "Month" Column
I need to find 1 in the "Shift" Column
And I need to find 2 in the "Order" Column

If that is all found on the same row display a message box "valid"

I have been experimenting with :
.Columns(5).Find(What:=Left(Target.Value, 4), LookAt:=xlWhole)

But how do I tell it that it all has to be on the same row and in that sequence?

 

[ttray33y]

Hello,

I have a question that i am trying my best to ask. I want to be able to find a SINGLE Value across a range of columns.

The best way i can explain this is I have a value of "ABC7190612" in a cell.

I need to find ABC in the "Product" Column
I need to find 7 in the "Year" Column
I need to find 19 in the "Day" Column
I need to find 06 in the "Month" Column
I need to find 1 in the "Shift" Column
And I need to find 2 in the "Order" Column

If that is all found on the same row display a message box "valid"

I have been experimenting with :
.Columns(5).Find(What:=Left(Target.Value, 4), LookAt:=xlWhole)

But how do I tell it that it all has to be on the same row and in that sequence?

advance filter can do it.

 

[Drumin_Phreak]

advance filter can do it.

I really need it to VBA so I can run it on the event of the work sheet changing, but thank you.

 

[inactiveuserps07]

@Drumin_Phreak:

1. Is your data formatted as a table? If so, what is the name of the table? If not, is there any reason the data can not be formatted as a table?

2. If the data is not formatted as a table and cannot be for some reason, what are the column letters that pair with each header you referenced?

3. What is the current range of your data set (e.g., A2:F1000, etc.) not including headers?

 

[Marcelo Branco]

I really need it to VBA so I can run it on the event of the work sheet changing, but thank you.

You can use COUNTIFS.
Assuming the columns are named ranges (Product, Year, Day........Order) with the same size and that the cell of interest is A2, maybe something like this

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$2" Then
        If Application.CountIfs(Range("Product"), Left(Target, 3), _
            Range("Year"), Mid(Target, 4, 1), Range("Day"), Mid(Target, 5, 2), _
            Range("Month"), Mid(Target, 7, 2), Range("Shift"), Mid(Target, 9, 1), _
            Range("Order"), Right(Target, 1)) Then MsgBox "Valid"
    End If
End Sub

Hope this helps

M.

 

[Drumin_Phreak]

@Drumin_Phreak:

1. Is your data formatted as a table? If so, what is the name of the table? If not, is there any reason the data can not be formatted as a table?

2. If the data is not formatted as a table and cannot be for some reason, what are the column letters that pair with each header you referenced?

3. What is the current range of your data set (e.g., A2:F1000, etc.) not including headers?

No it cannot be put into a table, it is forever growing.

Actually the information that i need to search with is in another workbook (thanks to footoo, I have the ability to search it).

However, I don't know how to preform a search such as this.

The Layout is as follows:

In the "N" Column of our "report"worksheet within workbook "A" is where the Lot# Will be found ABC7190612.


I need to find "ABC" in the "Product" Column "D" of workbook "B"
I need to find "7" in the "Year" Column "E" of workbook "B"
I need to find "19" in the "Day" (2 Digits) Column "F" of workbook "B"
I need to find "06" in the "Month" (2 Digits) Column "G" of workbook "B"
I need to find "1" in the "Shift" Column "H" of workbook "B"
And I need to find "2" in the "Order" Column "D" of workbook "B"

However, to be a Legit lot# all the elements of that lot # must be found on the SAME row in that order.

 

[ttray33y]

not tested but try:

Sub test()

Application.ScreenUpdating = False


Dim fproduct As string, fYear As string, fDay As string, fMonth As string, fshift As string, fOrder As string
Dim lastrow As Long
Dim i As Long
Dim wsheet As Worksheet


Set wsheet = ActiveWorkbook.ActiveSheet
lastrow = wsheet.Cells(Rows.Count, 4).End(xlUp).Row


    fproduct = wsheet.Range("D1").Value
    fYear = wsheet.Range("E1").Value
    fDay = wsheet.Range("F1").Value
    fMonth = wsheet.Range("G1").Value
    fshift = wsheet.Range("H1").Value
    fOrder = wsheet.Range("I1").Value


For i = 2 To lastrow


    If wsheet.Cells(i, 4) = fproduct And wsheet.Cells(i, 5) = fYear And wsheet.Cells(i, 6) = fDay And wsheet.Cells(i, 7) = fMonth And wsheet.Cells(i, 8) = fshift And wsheet.Cells(i, 9) = fOrder Then
        MsgBox "Valid"
    End If


Next i


If wsheet.Range("D1").Value = "" Then Exit Sub


Application.ScreenUpdating = True


End Sub

 

[Drumin_Phreak]

not tested but try:

Sub test()

Application.ScreenUpdating = False


Dim fproduct As string, fYear As string, fDay As string, fMonth As string, fshift As string, fOrder As string
Dim lastrow As Long
Dim i As Long
Dim wsheet As Worksheet


Set wsheet = ActiveWorkbook.ActiveSheet
lastrow = wsheet.Cells(Rows.Count, 4).End(xlUp).Row


    fproduct = wsheet.Range("D1").Value
    fYear = wsheet.Range("E1").Value
    fDay = wsheet.Range("F1").Value
    fMonth = wsheet.Range("G1").Value
    fshift = wsheet.Range("H1").Value
    fOrder = wsheet.Range("I1").Value


For i = 2 To lastrow


    If wsheet.Cells(i, 4) = fproduct And wsheet.Cells(i, 5) = fYear And wsheet.Cells(i, 6) = fDay And wsheet.Cells(i, 7) = fMonth And wsheet.Cells(i, 8) = fshift And wsheet.Cells(i, 9) = fOrder Then
        MsgBox "Valid"
    End If


Next i


If wsheet.Range("D1").Value = "" Then Exit Sub


Application.ScreenUpdating = True


End Sub

I'm sorry but i am not understanding where i would put the lot# (ABC7190612), to even begin the look up....