Modify Code found on the Web

[Pookiemeister]

Below is the original code that I found which will find the word "Excel" in worksheet named "Source" from Range("A1:A10000") and the return the range address for the word.

Sub x()    Dim rngX As Range
    
    Set rngX = Worksheets("Source").Range("A1:A10000").Find(" Excel", lookat:=xlPart)
    If Not rngX Is Nothing Then
        MsgBox "Found at " & rngX.Address
    End If


End Sub

I would like to modify the above code to find the product code (sPrdCde) from the Worksheet(formTitle) but instead of using Range("A1:A10000"), I would like to use the variable finalRow.

Sub Test()

Dim ws_count As Integer, i As Integer, finalRow As Integer, x As Integer


lDz = 0
lCs = 0
sUOM = " "


ActiveWorkbook.Worksheets(formTitle).Activate
finalRow = Cells(Rows.Count, 2).End(xlUp).Row


Call findItem


End Sub

Sub findItem()    
    Dim rngItem As Range
    
    Set rngItem = Worksheets(formTitle).finalRow.Find(sPrdCde, lookat:=xlPart)
    If Not rngItem Is Nothing Then
        MsgBox "Found at " & rngItem.Address
    End If


End Sub

Thank You

 

[Fluff]

Something like

Sub Test()

Dim ws_count As Integer, i As Integer, finalRow As Integer, x As Integer
Dim Rng As Range

lDz = 0
lCs = 0
sUOM = " "


With ActiveWorkbook.Worksheets(formTitle)
   Set Rng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With
MsgBox findItem(Rng, "abc")


End Sub
Function findItem(Rng As Range, sPrdCde As String) As String
    Dim rngItem As Range
    
    Set rngItem = Rng.find(sPrdCde, lookat:=xlPart)
    If Not rngItem Is Nothing Then
       findItem = rngItem.Address
    End If
End Function

 

[Pookiemeister]

Thank you. That worked great. I did, however, had to modify your code just a little bit.

 

[Fluff]

Glad you sorted it & thanks for the feedback