我正在创建一个电子表格,当按下宏时显示条形码。
我现在的代码: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
这将在现有列旁边创建一个新列,并将字体更改为可读的条形码字体,同时在数字周围加上星号。
我想让它做的是基本上只打印条形码,如果有数字在前一列。Essential "如果number存在,则执行条形码">
在此之上,我只希望条形码在物料号的前4位数字周围加上星号。我们有1130.201这样的物料号,但我只希望代码在条形码中包含1130。这就是电子表格的样子。
代码更新后的电子表格图片。
如果我理解你想:
A)With
语句中的If语句对于Offset(,-1)
中的IsNumeric()
B)用Left(,4)
代替.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:添加Len()
到If
语句的额外检查,并添加checkValue
来限制对表的引用。