Find Column Header By Name And Select All Data Below Column Header (Excel-VBA)

Question

I'm attempting to create a macro to do the following:

1. Search a spreadsheet column header by name.
2. Select all data from the selected column, except column header.
3. Take Number Stored As Text & Convert to Number.

• Converting to Number to use for VLookup.

For Example:

Visual Spreadsheet Example:

I've discovered the following code online:

With ActiveSheet.UsedRange

Set c = .Find("Employee ID", LookIn:=xlValues)

If Not c Is Nothing Then

    ActiveSheet.Range(c.Address).Offset(1, 0).Select
End If

End With

However, I'm still experiencing some issues.

Answer 1

I just stumbled upon this, for me the answer was pretty straightforward, in any case If you're dealing with a ListObject then this is the way to go:

YOURLISTOBJECT.HeaderRowRange.Cells.Find("A_VALUE").Column

Answer 2

It is good to avoid looping through all cells. If the data set grows the macro can become too slow. Using special cells and paste special operation of multiplying by 1 is an efficient way of accomplishing the task.

This works...

Dim SelRange As Range
Dim ColNum As Integer
Dim CWS As Worksheet, TmpWS As Worksheet

'Find the column number where the column header is
Set CWS = ActiveSheet
ColNum = Application.WorksheetFunction.Match("Employee ID", CWS.Rows(1), 0)

'Set the column range to work with
Set SelRange = CWS.Columns(ColNum)

'Add a worksheet to put '1' onto the clipboard, ensures no issues on activesheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

    Set TmpWS = ThisWorkbook.Worksheets.Add
    With TmpWS
        .Cells(1, 1) = 1
        .Cells(1, 1).Copy
    End With

    'Select none blank cells using special cells...much faster than looping through all cells
    Set SelRange = SelRange.SpecialCells(xlCellTypeConstants, 23)
    SelRange.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
    TmpWS.Delete
    CWS.Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True

Answer 3

Try this out. Simply add all the column header names you want to find into the collection. I'm assuming you don't have more than 200 columns, if you do simply update the for i = 1 to 200 section to a larger number.

Public Sub FindAndConvert()
    Dim i           As Integer
    Dim lastRow     As Long
    Dim myRng       As Range
    Dim mycell      As Range
    Dim MyColl      As Collection
    Dim myIterator  As Variant

    Set MyColl = New Collection

    MyColl.Add "Some Value"
    MyColl.Add "Another Value"

    lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For i = 1 To 200
        For Each myIterator In MyColl
            If Cells(1, i) = myIterator Then
                Set myRng = Range(Cells(2, i), Cells(lastRow, i))
                For Each mycell In myRng
                    mycell.Value = Val(mycell.Value)
                Next
            End If
        Next
    Next
End Sub

Answer 4

Ok, here's a brief way of achieving your goal. First, locate the column that holds the Employee IDs. Then simply set the entire Column to be formatted as Number instead of Text?

With Worksheets(1)  ' Change this sheet to the one you are using if not the first sheet
    Set c = .Find("Employee ID", LookIn:=xlValues)

    If Not c Is Nothing Then
        ' The column we want is c's Column.
        Columns(c.Column).NumberFormat = 0
    End If
End With

Answer 5

Add a dim for the range that you want:

Dim MyRng, RngStart, RngEnd as Range

Then change:

ActiveSheet.Range(c.Address).Offset(1, 0).Select

to the below so that all data in that column is found.

set RngStart = ActiveSheet.Cells(1, c.column)
set RngEnd = ActiveSheet.Cells(rows.count, c.column).end(xlup)
set MyRng = ActiveSheet.Range(RngStart & ":" & RngEnd)

Now you can play about with the data. If you want to paste this somewhere which is formatted as number:

MyRng.copy
Sheets("Wherever").Range("Wherever").pastespecial xlvalues

If you want to change the format of the cells you have now found (How to format column to number format in Excel sheet?) that is whole number format, if you want decimal points then use "number" instead of "0":

MyRng.NumberFormat = "0"

or the new destination:

Sheets("Wherever").Range("Wherever").NumberFormat = "0"

General formatting which matches exactly the convert to number function:

MyRng.NumberFormat = "General"
MyRng.Value = MyRng.Value