1

I'm copying columns from one spreadsheet to another:

Set sourceColumn = wb.Worksheets(cmb).Columns(wb.Sheets(cmb).Rows(1).Find(Form.ComboBox2.Value).Column)
Set targetColumn = Workbooks("B.xlsm").ActiveSheet.Columns("A")
sourceColumn.Copy Destination:=targetColumn

After copying, I want to convert the number formatting of the targetColumn to number. In reference from this post, I tried Workbooks("B.xlsm").ActiveSheet.Columns("1").NumberFormat = "0" but that isnt changing the column formatting from text to number.

Any ideas whats going awry?

user248884
  • 851
  • 1
  • 11
  • 21

1 Answers1

1

If you are sure that all values on your column are numbers formatted as text, you can try Text to Column functionality like:

With Workbooks("B.xlsm").ActiveSheet ' you might want to be more explicit here
    .Columns(1).TextToColumns Destination:=.Columns(1), _
                              DataType:=xlDelimited, _
                              FieldInfo:=Array(1, 1)
End With

Above changes the column 1 format to General which will make all entries that looks like a number to number format.

L42
  • 19,427
  • 11
  • 44
  • 68
  • This still stores the column entry as text. How do I typecast it to number? – user248884 Oct 07 '17 at 04:14
  • @user248884 You execute this after copying the contents. – L42 Oct 07 '17 at 05:14
  • @user248884 And all your date is in Column 1 (A)? It should work as long as your entry are numbers entered as text. Can you show actual sample data so I can test? – L42 Oct 16 '17 at 01:40