Select a column based on the name of the first value

Re,

Try it like this:

Sub Macro1() Dim O As Worksheet Dim R As Range Dim COL As Integer Dim LI As Integer Set O = Worksheets("Sheet1") Set R = O.Rows(1).Find("Speed", , xlValues, xlWhole) If R Is Nothing Then Exit Sub COL = R.Column LI = O.Cells(Application.Rows.Count, "A").End(xlUp).Row Set pl = Application.Union(O.Range(O.Cells(1, 1), O.Cells(LI, 1)), O.Range(O.Cells(1, COL), O.Cells(LI, COL))) ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SetSourceData Source:=pl O.Cells(1, COL).Select End Sub

--
See you later,
ThauTheme

Hello,

another way to do it:

Choice being the variable that contains the searched header

 Dim Cel As Range, Colchoix As Long, sColchoix As String Set Cel = Range("Q2:AE2").Find(what:=Choice) Colchoix = Cel.Column 'numerical sColchoix = Split(Cel(8, Cel.Column).Address, "$")(1) 'Alphanumeric 

Hello Scuti, hello forum,

Try it like this:

Sub Macro1() Dim BE As Variant 'declare the variable BE (Input Box) Dim O As Worksheet 'declare the variable O (Sheet) Dim R As Range 'declare the variable R (Search) Dim COL As Integer 'declare the variable COL (Column) BE = Application.InputBox("Type the text to search for!", "TEXT", Type:=2) 'set the input box BE If BE = False Or BE = "" Then Exit Sub 'if [Cancel] or not filled in, exit the procedure Set O = Worksheets("Sheet1") 'set the sheet O (to adapt to your case) Set R = O.Rows(1).Find(BE, , xlValues, xlWhole) 'set the search R (search BE whole in row 1 of sheet O) If R Is Nothing Then Exit Sub 'if no occurrence is found, exit the procedure COL = R.Column 'get the column COL of the first occurrence found MsgBox COL 'message indicating column COL (row to be deleted later...) End Sub

--
See you soon,
ThauTheme

Hello scuti,

No, there is no such function: you need to loop through your header row to find the name.

Example:

Your header row is on line 5, from columns 1 to 10;
and you are looking for the header value "Amount":

 Option Explicit Dim col As Integer For col = 1 To 10 If Cells(5, col) = "Amount" Then ' Processing for the "Amount" column Exit For End If Next col ' Continue with the code 

If we found the "Amount" column:

a) Exit For allows you to exit the loop immediately, as it is unnecessary
to test the other columns to the right. ;)

b) col contains your column number

----------------------------------------

If your Processing is very long, you can move it lower
using a flag variable: flag (or another name
of your choice).

⚠️ When declared, flag already equals False; I give it
this meaning: "column 'Amount' not found".

 Option Explicit Dim col As Integer, flag as Boolean For col = 1 To 10 If Cells(5, col) = "Amount" Then flag = True: Exit For Next col If flag Then ' Processing for the "Amount" column End If ' Continue with the code 

flag is a flag: False (implicit) = not found;
if "Amount" is found, flag is set to True and we
immediately exit the loop.

Right after, and only if flag is True, do we perform
the processing of the "Amount" column.

You may have noticed that If flag = True Then can be
abbreviated to If flag Then; it’s not an oversight! ;)

Thank you for all your answers, but I don't know which one to choose.

Let's say I want to add this afterwards, with column C:C being the column selected by its name,


Sub Macro5()
'
' Macro5 Macro
'
' Keyboard shortcut: Ctrl+Shift+G
'
Range("A:A,C:C").Select
Range("C1").Activate
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
ActiveChart.SetSourceData Source:=Range("Feuil1!$A:$A,Feuil1!$C:$C")
End Sub

(this creates a chart with A:A and C:C)

What should I put in place of C:C
and which program should I choose?

Hello thread, hello forum,

The two codes are quite similar. I used the Find function to locate the column and Paul used a For... Next loop.
My code uses an input box allowing you to type the search name. If this name is fixed and never varies, the lines referring to the input box become unnecessary in my code...
But you say absolutely nothing about it?!... I even wonder if you tested both codes...

Anyway, and strangely, we used the same name for the variable Paul and I: col.

So in the end for your code, replace:

Range("A:A,C:C").Select Range("C1").Activate 

with:

Application.Union(Columns(1), Columns(col)).Select Cells(1, col).Activate

then the rest of your code...

--
See you,
ThauTheme

Thank you everyone for your responses.