Find cell containing text to determine which column to sort

[dancingcab]

Hi,

I have several columns of data. I have created a form that allows the user to select which variable they wish to sort by and they can select up to four variables. The combobox allows a selection only from the headers in the columns. In order to sort in the correct order I need to find out which column that variable is in. I was trying to use something like

myColumn1 = Worksheets("Galv Results").Find(cboDataSort1.Value, , xlGeneral, xlWhole).column

but i get runtime error 438 Object doesn't support this property or method.

Can anyone advise on what I've done wrong here? Is using Find the issue? I've never used it before. Should i use Hlookup or index and match instead?

Thanks!

 

[JLGWhiz]

myColumn1 = Worksheets("Galv Results").Find(cboDataSort1.Value, , xlGeneral, xlWhole).column

xlGeneral is not a valid parameter. You can use xlFormulas, xlValues or xlComments. I expect that xlValues would be applicable to your issue.

 

[hiker95]

dancingcab,

I have not worked with forms in a very long time.

And, you have not given us your sort code.

You may be able to adjust your macro code, based on the following macro code.

Sample raw data (we are searching for title This Title):

Excel 2007
	A	B	C	D	E
1	Title A	Title B	This Title	Title D
2
Galv Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub FindMyTitle()
' hiker95, 11/13/2014, ME818021
Dim myColumn1 As Range, cboDataSort1 As String
cboDataSort1 = "This Title"
Set myColumn1 = Sheets("Galv Results").Rows(1).Find(cboDataSort1, LookAt:=xlWhole)
If Not myColumn1 Is Nothing Then
  MsgBox ("The title '" & cboDataSort1 & "' was found in column number " & myColumn1.Column & "")
ElseIf myColumn1 Is Nothing Then
  MsgBox ("The title '" & cboDataSort1 & "' was NOT found in row 1 - macro terminated!")
  Exit Sub
End If
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FindMyTitle macro.


When you run the above macro, you will get one of two message boxes:

The title 'This Title' was found in column number 3

The title 'This Title' was NOT found in row 1 - macro terminated!

 

[dancingcab]

Great! Thanks Hiker95 - that's done the trick

 

[hiker95]

dancingcab,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.