View Full Version : Find Multiple String Variables in a different sheet
dubabear
05-09-2017, 01:50 PM
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
05-09-2017, 06:41 PM
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
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
05-09-2017, 07:04 PM
The SalesInv is sales invoice number but can appear multiple times with different SalesItem codes.
Paul_Hossler
05-10-2017, 06:18 AM
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
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.