looking for the column

[Irek1974]

hi,
I would like to write part of the code that select a couple of cells in a row (for example : Range("A1:A10).select) than:
-find a text which is in one of these cells (for example "HP") - and gives me back column in which this text is input

texts in selected cells will always be the same (for example : HP, CH, SD, .... ect.)

could someone help pls
Irek

 

[VoG]

Try something like this

Sub test()
Dim Found As Range
Set Found = Range("A1:A10").Find(what:="HP", lookat:=xlWhole, LookIn:=xlValues)
If Found Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox "Found in Row " & Found.Row, vbInformation
End If
End Sub

 

[Irek1974]

hi
it gives me the number of the row - but I need column (I changed MsgBox "Found in Row " & Found.Row, vbInformation into MsgBox "Found in Row " & Found.column, vbInformation

it gives me number of the column but I need it gives me the result not as a number but for example C, D , ect

Try something like this

Sub test()
Dim Found As Range
Set Found = Range("A1:A10").Find(what:="HP", lookat:=xlWhole, LookIn:=xlValues)
If Found Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox "Found in Row " & Found.Row, vbInformation
End If
End Sub

 

[VoG]

Hmm. If you are looking in A1:A10 then the column has to be A surely?

Perhaps you meant something like

Sub test()
Dim Found As Range
Set Found = Range("A1:F10").Find(what:="HP", lookat:=xlWhole, LookIn:=xlValues)
If Found Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox "Found in Column " & Chr(64 + Found.Column), vbInformation
End If
End Sub

 

[Irek1974]

sorry,
of course. I wrongly defined the selected area.
it shold be A1:J10

Hmm. If you are looking in A1:A10 then the column has to be A surely?

 

[Irek1974]

thank you

Hmm. If you are looking in A1:A10 then the column has to be A surely?

Perhaps you meant something like

Sub test()
Dim Found As Range
Set Found = Range("A1:F10").Find(what:="HP", lookat:=xlWhole, LookIn:=xlValues)
If Found Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox "Found in Column " & Chr(64 + Found.Column), vbInformation
End If
End Sub

 

[Irek1974]

I have one more problem.
what about finding date ?

I'm looking for the month from cell C4 in a range of cells D5:O5 - and need to get the column in msgbox

I have a code :

Dim month
month = Range("C4")
Set month = Range("D5:O5").Find(what:=month, lookat:=xlWhole, LookIn:=xlValues)
MsgBox Chr(64 + month.Column), vbInformation

it stops at line "msgbox ......

Hmm. If you are looking in A1:A10 then the column has to be A surely?

Perhaps you meant something like

Sub test()
Dim Found As Range
Set Found = Range("A1:F10").Find(what:="HP", lookat:=xlWhole, LookIn:=xlValues)
If Found Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox "Found in Column " & Chr(64 + Found.Column), vbInformation
End If
End Sub

 

[VoG]

You should avoid using month as a variable name as it is the name of a VBA function. Also, check whether the month is found. Assuming that you are searching for text try something like

Dim amonth As String, xmonth As Range
amonth = Range("C4").Value
Set xmonth = Range("D5:O5").Find(what:=amonth, lookat:=xlWhole, LookIn:=xlValues)
If xmonth Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox Chr(64 + xmonth.Column), vbInformation
End If

The a

 

[Irek1974]

It doesn't work.
as a temporary solution I did as follows:

below cell C4 (in C5) I have a text which could be found in range below D5:O5 (in D6:O6). In this situation it works but as I mentioned it's only temporary solution as I need to look in a range for a data which is in cell C4.
Maybe the problem is with "chr(64 ......) " as 256 is the max of arguments chr could take ?

what do you think ?

You should avoid using month as a variable name as it is the name of a VBA function. Also, check whether the month is found. Assuming that you are searching for text try something like

Dim amonth As String, xmonth As Range
amonth = Range("C4").Value
Set xmonth = Range("D5:O5").Find(what:=amonth, lookat:=xlWhole, LookIn:=xlValues)
If xmonth Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox Chr(64 + xmonth.Column), vbInformation
End If

The a

 

[VoG]

Try this

Function ColumnLetter(anyCell As Range) As String
ColumnLetter = Left(anyCell.Address(False, False), 1 - CInt(anyCell.Column > 26))
End Function

Sub test()
Dim amonth As String, xmonth As Range
amonth = Range("C4").Value
Set xmonth = Range("D5:IV5").Find(what:=amonth, lookat:=xlWhole, LookIn:=xlValues)
If xmonth Is Nothing Then
    MsgBox "Not found", vbExclamation
Else
    MsgBox ColumnLetter(xmonth)
End If
End Sub