Entire Column Format base on Format via Vlookup

[wiscokid]

I currently have a set of columns I would like to format, based on the title of the column. To find out how to format the column I perform a Vlookup using the column title to find the matched format type

My vlookup sheet looks like this (and that part of the code is working to return the correct format value

My code is the following
Sub FormatCols()

Dim CurrCol As String
Dim NumCol As Integer

Sheets("CORE").Select
Range("A10").Select
NumCol = ActiveCell.Value


Range("C10").Select
For I = 1 To NumCol
Sheets("CORE").Select
ActiveCell.Offset(0, 1).Select
CurrCol = ActiveCell.Value
Sheets("Formats").Select
CurrFormat = WorksheetFunction.VLookup(CurrCol, Range("A:B"), 2, False)
Sheets("CORE").Select
EntireColumn.NumberFormat = CurrFormat
Next I
End Sub

It is bombing the first time through the loop at the end of the loop
EntireColumn.NumberFormat = CurrFormat

CurrCol="Ticker"
CurrFomat = ""_0""

I also tried making the CurrFormat in my Vlookup table to be just _0 so that the CurrFomat variable = "_0" (single rather than double quotes, and that did not work either

any help would be appreciated



And the main CORE sheet is

 

[myall_blues]

There's a few issues, but the main problem is the syntax of that line:

EntireColumn.NumberFormat = CurrFormat

You can't use EntireColumn that way - it has to be preceded by an expression (variable) that represents a range object - refer here.

A few other things:
- In your formats lookup take out all the quotes and just enter the values as required preceded by a single quote to prevent it being interpreted as a number e.g. '0_)- It's almost never necessary to use Select in VBA as things can be done all in one line by referring to the relevant object. See code below.
- In your code you have a loop For i = 1 To NumCol. Because you are selecting a new cell in each case it works but if you remove the selects then you need to include i in the loop.
- The code will need some sort of error trap because NumCol=40, but there are only four columns containing data in your example. When NumCol=5 the code will error at the VLOOKUP because it won't find a value.

Hope this helps.

Sub FormatCols()
    Dim CurrCol As String, CurrFormat As String
    Dim NumCol As Integer
    Dim i As Long, CurrCol2 As Long
    '
    NumCol = Sheets("CORE").Range("A10").Value
    For i = 1 To NumCol
        CurrCol = Sheets("CORE").Range("C10").Offset(0, i).Value
        CurrCol2 = Sheets("CORE").Range("C10").Column
        CurrFormat = WorksheetFunction.VLookup(CurrCol, Sheets("Formats").Range("A:B"), 2, False)
        Sheets("CORE").Columns(CurrCol2).NumberFormat = CurrFormat
    Next i
End Sub

Book1
	A	B
1	Ticker	0_)
2	Rank	0_)
3	Thm	0_)
4	DSI	0_)
5
6
Formats

 

[wiscokid]

Thank you very much, Works beautifully!