CODEWITHSUNDEEP
vbaexcel
worldCurrencies
worldCurrencies

Reputation: 467

Excel VBA Formatting not correct

So I am trying to format a date to yyyymm (e.g. 201808). However, it always comes out as (20188). On months with double digits such as december, there isnt a problem. It would come out as 201812. However this doesnt happen for months with single digits. In my code, the month and year were originally in two separate columns, but I combined them. Here is my code:

Sheets("Sheet1").Select
Columns("G:G").Select
Selection.NumberFormat = "00"
Columns("I:I").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],RC[-2])"
Selection.AutoFill Destination:=Range("I2:I800")
Range("I2:I800").Select
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Columns("G:H").Select
Selection.Delete Shift:=xlToLeft

There are probably easier ways to write this code, as I just recorded the macro.

Upvotes: 0

Views: 107

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Number format <> actual number.

You need to use TEXT() to actually change the stored value:

With Worksheets("Sheet1").Range("I2:I800")
    .FormulaR1C1 = "=RC[-1] & TEXT(RC[-2],""00"")"
    .Value = .Value
End With
Worksheets("Sheet1").Columns("G:H").Delete

Upvotes: 1

Related Questions