How to find row from address

[sbubendorf]

I have a macro where I have found a match using

Set cl = .Find(sFind, LookIn:=xlValues)

I have the address of the match using cl.address.

How may I find only the row portion of the match for the next step in my macro?

Thank you!

[Charlize]

.rowperhaps ?

Charlize

[sbubendorf]

I get a "Compile error: Invalid qualifier" when I try to use that method, tho.

[Charlize]

How did you declared CL ? As a range or variant ...

Charlize

[sbubendorf]

As a range.

[Charlize]

Sub find_it()
Dim cl As Range
Dim firstaddress As String
Dim MyValue As String
MyValue = "Test"
With Worksheets(1).Range("a1:a500")
Set cl = .Find(MyValue, LookIn:=xlValues)
If Not cl Is Nothing Then
firstaddress = cl.Address
Do
MsgBox "Row of found value : " & cl.Row
Set cl = .FindNext(cl)
Loop While Not cl Is Nothing And cl.Address <> firstaddress
End If
End With
End Sub

[sbubendorf]

Thank you, Charlize !!

I was being very dense !! I've been beating my head against the wall for two days on 4 or 5 lines of what should be very simple code. My problem here was that I was trying to do "cl.Address.Row", rather than just "cl.Row", as I saw in the code you posted.

With your help, I got the code to run, but it is giving me the wrong results. I am going to post my code. Perhaps you can quickly see something obvious that I am overlooking. Please don't feel obligated to respond, or particularly to spend any length of time, since this is a completely different issue than what you originally responded to. I very much appreciate the help that you have provided already!!
Sub revBIFcountVBA()
Dim sFind As String
Dim rng As Range
Dim cl As Range
Dim lTotal As Long
Dim sAddress As String
Dim icLastRow
Dim ic
Dim ia

On Error GoTo revBIFcountVBA_Error

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

icLastRow = Cells(Rows.Count, "C").End(xlUp).Row
For ic = icLastRow To 3 Step -1
''Line below takes file names manually cut and pasted into Column "C" and removes the
''dot and file extension, and then puts the new value (a part name)into Column "D".
Cells(ic, "D").Value = Left(Cells(ic, "C").Value, Len(Cells(ic, "C").Value) - 4)

sFind = Left(Cells(ic, "C").Value, Len(Cells(ic, "C").Value) - 4)
lTotal = 0
''The With portion below starts in the last row and writes additional information about
''the part name in cell ic,D into columns ic,F to ic,H. The additional information
''is extracted by offset from the location where the part name is located in column "A".
''Column "A" data is manually cut and pasted from an external file prior to running the macro.
With Sheet1
Set rng = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
With rng
Set cl = .Find(sFind, LookIn:=xlValues)
If Not cl Is Nothing Then
sAddress = cl.Address
Do
lTotal = lTotal + CLng(cl.Offset(-5, 0).Value) 'QUANTITY
Cells(ic, "F").Value = cl.Offset(-3, 0).Value 'DESCRIPTION
Cells(ic, "G").Value = CFeet(cl.Offset(-1, 0).Value, 16) 'LENGTH
Cells(ic, "H").Value = cl.Offset(-2, 0).Value 'GRADE
Set cl = .FindNext(cl)
Loop While Not cl Is Nothing And cl.Address <> sAddress
End If
End With
End With




''The next line totals the quantity of each part that occurs between row 3 and the last row. The
''actual quantity at each ic location is calculated by Offset (See lTotal line above, first line of DO statement.)
''The quantity is written to Column "E".

Cells(ic, "E").Value = lTotal

''''''''''''''''''''''''''''''''''''''''''''''
''The portion between the comment lines below is intended to locate the first cell in Column
''"A" located above ia (the matching part name) that contains a dash (-) as the fifth character.
''The full value in that
''cell is supposed to be the name of an assembly which contains the part. (For example, assembly
''7241-A may contain part p1689.) The full value (the assembly name) needs to be written to Column "N".
''Ideally, I would like to be able to step all the way up to row 3 and append (comma delimited) any
''additional assemblies which contain the part name, but obtaining the first assembly name and writing
''it to Column "N" is all that is actually required for my current purposes, and that is all that I have
''attempted to do below.
''''''''''''''''''''''''''''''''''''''''''''''
For ia = cl.Row To 3 Step -1

If Mid(Cells(ia, "A").Value, 5, 1) = "-" Then
Cells(ic, "N").Value = Cells(ia, "A").Value
End If

Next ia
''''''''''''''''''''''''''''''''''''''''''''''''
Next ic
End Sub


The code is writing into ic,N (every row) the very first part assembly name at the top of Column "A". Nearly every entry in column "N" should be unique, but the code is bypassing the correct entries in column "A" and proceeding to near the top of the column to extract the data. The problem code is between the comment (''''') lines near the bottom of the code. Only those 5 lines are not working correctly.

Thank you, Charlize !!

[sbubendorf]

:yes :friends: :bow: I got the entire thing to work, Charlize, even appending multiple assembly names. to column "N". Your help was the breakthrough that cleared my thinking so I could complete the whole thing.

Thank you again !!!!!!!!!!!!!!!:hi: