Find Method and Address Property

[oric]

Hi all,

I have been trying to finish this code for searching for Column descriptions (text in the first few rows) and then using that address as a starting point to loop through the cells in that column. I think I got the searching part down. My only problem now is to use the address property to return the column number so that I can use it in the Worksheets.Cells(#,#) syntax. Here is the code I got so far.

Sub FindingColumn()
Dim rngLook As Range, rngFound As Range, fndcell As Range
Dim strVal As String
Set rngLook = Worksheets(1).Rows("1:2")
Set rngFound = rngLook.Find(What:=strVal)
If Not rngFound Is Nothing Then
fndcell = rngFound.Address(False, False)
End If
...
...

End Sub

Any suggestions on how to proceed??

many thx's

 

[Oorang]

Range(mystr).Column Returns the column number.
".Column" can appended to any range to give you the first column in the range.

Ex: Range("A1:H1").Column

= 1

Range("H1").Column

= 8

To get the last column you could do somthing like:

Range("C1:I1").Columns.Count + Range("C1:I1").Column - 1

Which is the number of columns in the range (7) + the number of the left colum(3) minus one becuase the left column is count twice by this addition.