I am having problems with a VBA script I wrote.
It works fine with several versions of Excel (2003, 2010, and 2013).
But the problem arises when I move from Windows XP to Windows 7 or 8.
I use a scanner to scan barcode serial numbers into a textbox, and then populate these serial numbers with other associated data into a spreadsheet.
The serial numbers are supposed to be separated by each line and placed into an array.
I get a compile error "Cant find project or library" with CHR highlighted in blue from this line of code...
SNs = Split(Str, Chr(10))
Once again, it works fine on many machines with XP, but not on Win 7 or 8.
Any help would be appreciated.
Thank you.
Glenn
Private Sub CommandButton1_Click()
'This is the button that fills the CPE Imports sheet with the serial numbers from the user form
Dim EmptyRow As Integer
Dim Str, Str2, a As String
Dim SNs() As String 'array
Dim UpperBound As Long
Dim CorpItem As String
'This line will determine the corporation.
If Range("Info!D10").Value = "01719" Then
CorpItem = "Good"
ElseIf Range("Info!D10").Value = "19204" Then
CorpItem = "Functional"
Else
CorpItem = "Good"
End If
'Make sure Sheet5 is Active
Sheet5.Activate
'Determine emptyRow
EmptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'set the Str variable to the values of the serial numbers in the Text Box
Str = TextBox1.Value
'split the variable "Str" by returns "Chr(10)", and populate it into the SNs() array.
SNs = Split(Str, Chr(10))
'Set the variable "UpperBound" to the upper bound of the array SNs()
UpperBound = UBound(SNs) - 1
For i = 0 To UpperBound
Str2 = SNs(i)
Len1 = Len(Str2)
Str2 = Left(Str2, Len1 - 1)
'the last line above ^^^^ will truncate the newline character (the little square) or chr(10) off of the end of the serial number
'first check to see if the length of Str2 is 19 digits long...
If Len(Str2) = 19 Then
'... and truncate the right 13 digits.
Str2 = Right(Str2, 13)
End If
'now check length of Str2 to see if it is 14 digits or less
If Len(Str2) > 14 Then
'... and if not, save the 14 digits to the right
Str2 = Right(Str2, 14)
End If
'Now, lettuce fill the columns A, B, and C, with the scanned in items...
Range("A" & EmptyRow + i).Value = UCase(Str2)
Range("B" & EmptyRow + i).Value = CorpItem
Range("C" & EmptyRow + i).Value = ListBox1.Value
Next
'clear the text box so that a double click or more will not over populate the spreadsheet.
TextBox1.Text = ""
Range("Info!D10").Value = ""
OptionButton1.Value = False
OptionButton2.Value = False
EmptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
MsgBox EmptyRow
Range("A" & EmptyRow).Select
End Sub
Bookmarks