Excel VBA Formatting not correct

Question

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.

Use the TEXT function to "reapply" the format when concatenating. — BigBen
– BigBen, Commented Aug 13, 2018 at 17:05
@rawrplus Hmm, unfortunately I cannot put it in that format because it cannot be interpreted that way to another system. It must be "yyyymm". Could i possibly do "yyyy.mm" and delete the period afterwards? — worldCurrencies
– worldCurrencies, Commented Aug 13, 2018 at 17:06
@BigBen, true but that isn't remotely what the op is doing here. The string concatenation doesn't use the cell's number format. — user4039065
– user4039065, Commented Aug 13, 2018 at 17:07
If you are exporting to TXT or CSV, doesn't a cell date format of yyyymm produce the correct results? — user4039065
– user4039065, Commented Aug 13, 2018 at 17:08

Scott Craner · Accepted Answer · 2018-08-13 17:08:49Z

1

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

answered Aug 13, 2018 at 17:08

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Formatting not correct

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related