PDA

View Full Version : Need to pick up the row and column of a cell?



doctortt
11-19-2012, 02:54 PM
Hi, can someone please help?

I want to read all the cells in row 3, and if one of the cells is equal to Assumptions!F8, store the row and column value to TEMPNAME.

I know that I need to do a For Each loop, but I'm struggling. Can someone please help? Thanks

doctortt
11-19-2012, 09:51 PM
Can someone please help? This shouldn't take you pro more than 5 mins. Thanks

mancubus
11-20-2012, 12:10 PM
hi.
you already know the row number, which is 3. :)



Sub Find_Row_Col()

Dim RowNum As Long, ColNum As Long
Dim FindStr

FindStr = Worksheets("Assumptions").Range("F8").Value

RowNum = Cells.Find(What:=FindStr, LookIn:=xlValues, LookAt:=xlPart).Row
ColNum = Cells.Find(What:=FindStr, LookIn:=xlValues, LookAt:=xlPart).Column

End Sub

Teeroy
11-20-2012, 08:09 PM
hi.
you already know the row number, which is 3. :)


@mancubus, Priceless :thumb

@doctortt there's another couple of techniques in the code below that might help you. It restricts the search to Row 3 and finds the first match.
You didn't mention what "TEMPNAME" was so I used an example to write it to a named range on a worksheet sheet.
Sub Find_Col()
Dim rMatch As Range, ColNum As Long
Set rMatch = Rows(3).Find(What:=[Assumptions!F8], LookIn:=xlValues, LookAt:=xlPart, After:=Cells(3, Columns.Count))
If Not rMatch Is Nothing Then
ColNum = rMatch.Column
Range("TEMPNAME").Value = "3," & ColNum
End If
End Sub