Find cell containing text to determine which column to sort

dancingcab

New Member
Joined
Nov 10, 2014
Messages
14
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,433
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
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):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Title A</td><td style=";">Title B</td><td style="background-color: #FFFF00;;">This Title</td><td style=";">Title D</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Galv Results</p><br /><br />

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).

Code:
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!
 
Last edited:

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
dancingcab,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 

Forum statistics

Threads
1,085,587
Messages
5,384,610
Members
401,913
Latest member
chethan av

Some videos you may like

This Week's Hot Topics

Top