Help using visual basic in a macro for excel?

Question

I need to select a column using code in a macro in excel, however as the column WILL be different everytime I cannot just use

columns("B:B").select

It needs to select the column which has let's say "surname" as the heading.

Please remember that the column ref will be different everytime as it is used for varied reports.

Thanks guys!

Answer 1

Create a function which returns the column number. Then you can tell it which sheet and the name of the column you are searching for. It returns 0 if it doesn't find the column (stops searching when it finds the first empty column).

Function getColumn(ByRef sht As Excel.Worksheet, ByVal name As String) As Integer
Dim col As Integer
Dim header As String
col = 1
header = sht.Cells(1, col).Value
Do Until ((header = "") Or (header = name))
col = col + 1
header = sht.Cells(1, col).Value
Loop

If header = "" Then
col = 0
End If

getColumn = col
End Function

Answer 2

I'm assuming that each time the macro is run, you want to be able to specify the column to use.

Make each column a named range on your worksheet, e.g. if col. A contains surnames, select it and name it "Surname"

In your VBA code, create an input box to ask for the name of the column, and assign it to a variable, let's say "ColName"

Then Range(ColName).Select will select that column.

If my assumption was wrong, and you always want to select a column with surnames, but that will be in different locations, then naming the column "Surname" and using Range("Surname").Select will work.

Answer 3

You need to identify the column with something which won't be changing or is at least unique compared to other data types. i.e. Its the only text field within the first 6 columns.

If you have consistent column heading titles like "NAME" which will appear within a certain cange of columns then you can use a for/next loop to read and compare each column looking for a match with "NAME".

Once a match is found you have found your column, use teh value of the for/next loop at that point as your column index and exit the loop to continue processing.

Answer 4

task replace: that's between Macro and VBA a million. 2 themes with those styles of automation 2. 2 advantages of this way of automation 3. Used with type controls/lively X controls.

Answer 5

check out http://www.pscode.com for great sample codes.