IF statement and limiting numbers displayed (Excel/VBA)-CodePudding

I am creating a spreadsheet that displays a barcode when a macro is pressed.

Code I have now:

Private Sub CommandButton1_Click()
    
    Dim ws As Worksheet, a As Long
    
    Set ws = ThisWorkbook.Sheets("Pick Sheet") 'use a worksheet variable
    ws.Range("C1").EntireColumn.Insert
    ws.Range("C1").Value = "Pick Sheet"

    For a = 5 To 16
        With ws.Cells(a, 3)
            .Value = "*" & .Offset(0, -1).Value & "*"
            .Font.Name = "Free 3 of 9" 'barcode font
            .Font.Size = 32
        End With
    Next
ws.Columns(3).AutoFit
End Sub

This creates a new column next to the existing one and changes the font to a readable barcode font while also putting asterisks around the number.

What I want it to also do is to basically only print the barcode if there are numbers in the previous column. Essential "If number exists then perform the barcode code"

On top of this, I only want the barcode to put asterisks around the first 4 digits of the material number. We have material numbers like 1130.201, but I only want the code to include 1130 in the barcode. This is what the spreadsheet looks like.

Picture of spreadsheet after code update.

CodePudding user response：

If I'm understanding you want:

A) If statement within With statement for IsNumeric()in Offset(,-1)

B) Use Left(,4) for your .Value

Private Sub CommandButton1_Click()
    
    Dim ws As Worksheet, a As Long
    
    Set ws = ThisWorkbook.Sheets("Pick Sheet") 'use a worksheet variable
    ws.Range("C1").EntireColumn.Insert
    ws.Range("C1").Value = "Pick Sheet"

    For a = 5 To 16
        With ws.Cells(a, 3)
            dim checkValue as Variant
            checkValue = .Offset(,-1).Value
            If IsNumeric(checkValue)= True And Len(checkValue)>0 Then
                .Value = "*" & Left(checkValue,4) & "*"
                .Font.Name = "Free 3 of 9" 'barcode font
                .Font.Size = 32
            End if
        End With
    Next
ws.Columns(3).AutoFit
End Sub

Edit1: Adding additional check for Len() to If-statement and added checkValue to limit references to the sheet.