Date barcode in Excel

Question

Hi, 

I have the following code that populates a barcode of the date entered in the cell above it. 

Range("F" & BarRow).FormulaR1C1 = "=UPPER(""*"" & MONTH(R[-1]C) & "" / "" & DAY(R[-1]C) & "" / "" & YEAR(R[-1]C) & ""*"")"

This code does create the barcode; however, the date shows up in the following format when the barcode is scanned:  

7 /O 18 /O 2019. 

I need it to appear as 7/18/2019.  

Any help is greatly appreciated!!

Scott

Answer 1

It seems like the problem may be the /s   What do you get with dashes or some other separator?

Range("F" & BarRow).FormulaR1C1 = "=UPPER(""*"" & TEXT(R[-1]C,""MM-DD-YYYY"") & ""*"")"

When you scan the barcode, could you process the value before using it?

Answer 2

It seems to be an issue with the scanning software - you may need to contact that vendor.

Answer 3

Thanks Bernie.  That worked to remove the O, but now I have empty space:  2 – 2 – 2022

Any thoughts on how to fix that, so that it shows up as 2-2-2022?

Thanks again for the help!

Answer 4

Thanks Bernie.  Unfortunately, when I replaced my formula with yours, I still end up with the same result: 02/O02/O2022.

Any thoughts?

Here is the full code:  

CurRow = Lastrow

            If Application.WorksheetFunction.CountA(Range("A" & CurRow & ":F" & CurRow)) = 6 Then

                BarRow = Lastrow + 1

            'Adjust row height and alignment

                With Range("A" & BarRow & ":F" & BarRow).Select

                    Selection.RowHeight = 24

                    Selection.VerticalAlignment = xlCenter

                    Selection.NumberFormat = "General"

                End With

                'Adjust Font

                With Range("A" & BarRow & ":F" & BarRow).Font

                    .Name = "Free 3 of 9 Extended"

                    .Size = 18

                    .Strikethrough = False

                    .Superscript = False

                    .Subscript = False

                    .OutlineFont = False

                    .Shadow = False

                    .Underline = xlUnderlineStyleNone

                    .ColorIndex = xlAutomatic

                    .TintAndShade = 0

                    .ThemeFont = xlThemeFontNone

                End With

                'Barcode formulas

                Range("A" & BarRow).FormulaR1C1 = "=UPPER(""*"" & R[-1]C & ""*"")"

                Range("B" & BarRow).FormulaR1C1 = "=UPPER(""*"" & R[-1]C & ""*"")"

                Range("C" & BarRow).FormulaR1C1 = "=UPPER(""*"" & R[-1]C & ""*"")"

                Range("D" & BarRow).FormulaR1C1 = "=UPPER(""*"" & R[-1]C & ""*"")"

                Range("E" & BarRow).FormulaR1C1 = "=UPPER(""*"" & R[-1]C & ""*"")"

                Range("F" & BarRow).FormulaR1C1 = "=UPPER(""*"" & TEXT(R[-1]C,""MM/DD/YYYY"") & ""*"")"

It checks that the row above is complete, and then formats and enters barcodes of the data in the completed row above.  Maybe there is something wrong in this section. 

Thanks!

Answer 5

You can control the format of the date a little better using TEXT, using the format string of your choice:

Range("F" & BarRow).FormulaR1C1 = "=UPPER(""*"" & TEXT(R[-1]C,""M/D/YYYY"") & ""*"")"