search sheet for a specific value 1

[maximas]

I have a spreadsheet A. A is expandable, therfore, there is no specific # of rows, but have 5 columns.

A B C D E
1 A E I O U
2 E I O U A
3 J U S T A
4 T H A T S
5 P E T E E
6 Y O U C A
7 N H E L P

But the row is not static, how can you search the spreadsheet to find S, which is in cell E4 and return the row number with the column.
Thanks a mill!

 

[Loomah]

this should do the trick - with options!

Sub lime()
  Dim lRow, iCol
'    'selects the cell
'    Range(ActiveSheet.Cells.Find(What:="s", _
'      SearchDirection:=xlPrevious, _
'      SearchOrder:=xlByRows).Address).Select

  ' Find the row
    lRow = ActiveSheet.Cells.Find(What:="s", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByRows).Row

  ' Find the column
    iCol = ActiveSheet.Cells.Find(What:="s", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column

ActiveSheet.Cells(lRow, iCol).Select
MsgBox "Row = " & lRow & Chr(13) & "Column = " & iCol
End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[maximas]

HEY THANKS A LOT! IT WORKS.

 

[luceze]

I am not sure if this is what you are looking for but this will return the cell reference of the cell that contains the value that you are looking for.

Just highlight the range that you want to search.
then run this macro.

Sub FindAddress()
Criteria = InputBox("Enter Value&quot

Selection.Find(What:=Criteria, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
MsgBox ActiveCell.Address
End Sub

 

[maximas]

problem with code:
I try this, but have a run-time error'91'
object variable or with block variable not set
'this is active sheet "orders"
Private Sub Worksheet_Deactivate()
ActiveSheet.Unprotect password:="test"
Sheets("Payment&quot.Select
Sheets("Payment&quot.Activate
FIND
ActiveSheet.Protect password:="test"
End Sub
Sub FIND()
' Find the row
lRow = ActiveSheet.Cells.FIND(What:="C0#", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).row

' Find the column
iCol = ActiveSheet.Cells.FIND(What:="C0#", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
ActiveSheet.Cells(lRow, iCol).Select
MsgBox "Row = " & lRow + 1 & Chr(13) & "Column = " & iCol
End Sub

 

[maximas]

you know what? I think I solved it, there isn't any value that match the search criteria, that is why it bomb.