9

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:

enter image description here

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.

Improve this question
0

5 Answers 5

Reset to default
10

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Might be a silly question but what does YOURLISTOBJECT represent here?
@DannyO'Sullivan Typically, this will be a table. See learn.microsoft.com/en-us/office/vba/api/excel.listobject
@TorkilJohnsen super
8

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
Improve this answer

3 Comments

Hi @dra_red, Thank you for your assistance. I believe this converts the data into number correctly. Unfortunately the column header changes to a white background with unbolded font. Which isn't a big deal. I guess I could repaint over it. As far as logic, I used F8 to go step by step through the code. Not sure why a Temporary Sheet is created and what it does. If you could explain, I'd be very grateful. Just for my own understanding. Again, thank you for helping.
Hi @Laissez, To use 'pastespecial' with the 'multiply' operation you need to copy the value you are going to multiply by first. There are a few ways to go about this but to avoid various complications, I put the value on a new sheet before copying it. After the value is copied and the operation is completed, the sheet is deleted. This operation should not impact on formatting in the way you described. I can't say why that happened without looking at the workbook. The good thing about this approach is its efficiency. It pays off if you ever need to operate on a larger data set...
Is it possible to do this, but search the first 20 rows for the "Employee ID" to allow for the header row moving?
2

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
Improve this answer

1 Comment

Hi @Ryan Wildry, Thank you for your help. This solution tends to help the most. I didn't do a step by step process since it was a long loop. But, it accomplishes the task. Thank you for your help.
1

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
Improve this answer

4 Comments

This just changes the format, not the value. For example, you can't add the numbers together.
It's an Employee ID - why on earth would you ever want to add them together?
Your solution doesn't address the ask. Specifically asked for convert. There may be other columns that are going to be converted where this is more sensible. Not for us to say.
Hello All, Thank you for your quick replies. I didn't think anyone would be able to respond so quickly. My purpose later on is to use them for VLookup. If the IDs are not in number format for my specific purpose the VLookup will not work properly. As far as this code, thank you so much for your help, unfortunately I could not get this code to run properly. : (
0

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
Improve this answer

6 Comments

This just changes the format, not the value. For example, you can't add the numbers together.
I'm sorry, what exactly are you trying to do then? Add up all the staff ID's? Count how many times a staff id appears in the list of data? Count the staff ID's? Going to need more of a breakdown for what you are trying to do before I can help out...
I didn't ask the question. It's clear from the ask they want it converted to a number, probably to do some sort roll up on a column somewhere in there.
But convert to a number is exactly what the above is doing?
Also once you have the range you can do all sorts with it. Dim Total as long for each cell in MyRng total = total + cell.value next cell just as one example.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.