how to search for a string and return its column letter

[excel_sat]

Can anyone pls tell me how to search for a string in a sheet and when found return the column letter of that particular string in VBA?

Any help will be great...

Thanks

 

[Rick Rothstein]

This should work...

Set Cell = Cells.Find("Some Text String", , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then
  ColLetter = Split(Cell.Address, "$")(1)
  MsgBox ColLetter
Else
  MsgBox "I cannot find that text on this sheet"
End If

Note: The xlPart looks for the text even if it is part of other text... use xlWhole if the text will be the only text in the cell.

 

[excel_sat]

Can anyone pls tell me how to search for a string in a sheet and when found return the column letter of that particular string in VBA?

Any help will be great...

Thanks

This should work...

Set Cell = Cells.Find("Some Text String", , xlValues, xlPart, , , False)
If Not Cell Is Nothing Then
  ColLetter = Split(Cell.Address, "$")(1)
  MsgBox ColLetter
Else
  MsgBox "I cannot find that text on this sheet"
End If

Note: The xlPart looks for the text even if it is part of other text... use xlWhole if the text will be the only text in the cell.

Thanks Rick.. That was helpful

I have another question... What if I want to search something in a particular range say A1:A200 and return all the rows that matches? How can this be done?

 

[Rick Rothstein]

I have another question... What if I want to search something in a particular range say A1:A200 and return all the rows that matches? How can this be done?

That is a completely different question from the one you asked at the beginning of this thread, so you should start a new thread and ask it there. When you do, please clarify what you mean by "return all rows that matches"... are you looking for a list of row numbers or do you want all the data in all the rows for the matches? How do you want whichever returned... as an array in memory or as a range of cells? If the latter, where should it be placed... on the same worksheet deleting the non-matching data or on a separate worksheet?

One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your the data, its layout and the overall objective for it).