Create New Column and changes number into barcode-CodePudding

I am working on something where I need Excel to create a new column, and in that new column, take information from the previous column and change the number so there are asterisks around it, and also change the font.

I have a barcode 39 font that works when there are asterisks surrounding a number. I am new to coding in VBA (coding in general) and this is what I have so far:

 Private Sub CommandButton1_Click()

Sheets("Material Data").Range("D2").Select
ActiveCell.EntireColumn.Insert shift:=xlDown

For a = 2 To 16

Worksheets("Material Data").Cells(a, 4).Value = Worksheets("Material Data").Cells(a, 3).Value

Next

End Sub

This creates a new column next to the existing one, and displays the existing numbers. I don't know how to make the new numbers have asterisks around them and have the font/size changed.

This is what the spreadsheet looks like. I basically need the material number to be turned into a barcode.

I know ="(asterisk)"&[MATERIAL]&"(asterisk)" adds the asterisks around the numbers. [can't actually put * or it will just italicize on stackoverflow]

Any help would be much appreciated!

CodePudding user response：

Like this:

Private Sub CommandButton1_Click()
    
    Dim ws As Worksheet, a As Long
    
    Set ws = ThisWorkbook.Sheets("Material Data") 'use a worksheet variable
    ws.Range("D1").EntireColumn.Insert
    ws.Range("D1").Value = "Material Barcode"

    For a = 2 To 16
        With ws.Cells(a, 4)
            .Value = "*" & .Offset(0, -1).Value & "*"
            .Font.Name = "Arial" 'for example
            .Font.Size = 14
        End With
    Next

End Sub