Find Multiple String Variables in a different sheet

[dubabear]

Hi guys, still very new but hopefully I made a little progress on my own to create better questions.

in sheet2, I have:


Sub SalesDetail()

Dim SalesInv As String
Dim SalesCus As String
Dim SalesItem As String


SalesInv = Trim(ActiveCell.Offset(0, -24))
SalesCus = Trim(ActiveCell.Offset(0, -33))
SalesItem = Trim(ActiveCell.Offset(0, -19))


End Sub

My question is how do I find these 3 variables in sheet1. All three variables will be on the same row and I need the information from column A copied into the active cell in sheet2.

SalesInv is column B on sheet1
SalesCus is Column D on sheet1
SalesItem is column F on sheet 1

Thank you for helping a newbie out.

[Paul_Hossler]

Not tested, but try something like this. Attaching a small workbook does make testing easier

You don't need to save the Sheet2 value in a variable just for this




Option Explicit

Sub SalesDetail()

'My question is how do I find these 3 variables in sheet1. All three variables will be on the same row
'and I need the information from column A copied into the active cell in sheet2.
'SalesInv is column B on sheet1
'SalesCus is Column D on sheet1
'SalesItem is column F on sheet 1

Worksheets("Sheet1").Cells(ActiveCell.Row, 2).Value = Trim(ActiveCell.Offset(0, -24).Value)
Worksheets("Sheet1").Cells(ActiveCell.Row, 4).Value = Trim(ActiveCell.Offset(0, -33).Value)
Worksheets("Sheet1").Cells(ActiveCell.Row, 6).Value = Trim(ActiveCell.Offset(0, -19).Value)
ActiveCell.Value = Worksheets("Sheet1").Cells(ActiveCell.Row, 1).Value
End Sub

[SamT]

I assume that SalesInv is a unique Sales Invoice Number and will only appear once in Sheet1, Column B


Sub SalesDetail()

With ActiveCell
.Value = Sheets("Sheet1").Range("BB").Find(.Offset(0, -24)).Offset(0,-1)
End With

End Sub

[dubabear]

The SalesInv is sales invoice number but can appear multiple times with different SalesItem codes.

[Paul_Hossler]

The SalesInv is sales invoice number but can appear multiple times with different SalesItem codes.

and


My question is how do I find these 3 variables in sheet1. All three variables will be on the same row and I need the information from column A copied into the active cell in sheet2.


Do you want to

1. Find the row on Sheet1 Ccolumns B, D, and F that matches all 3 values from Sheet2 (e.g. row 100)
2. Put the Col A value from the matching row (e.g. 100) to the ActiveCell?

What happens is more than one row matches?

A small sample workbook with representative data will help

[SamT]

Can Sheet1 be sorted by B, D, and F?.



Would that new Excel function "SUMIFS()" do this? Sure, you're only summing one cell, but...