instanceoftime
Board Regular
- Joined
- Mar 23, 2011
- Messages
- 102
I have the following code ... works fine but at times it drops the leading zero when placing it in a cell.
I have the cells formatted as text as well but doesn't seem to change things.
how can I make sure the cell is written to as text?
I have the cells formatted as text as well but doesn't seem to change things.
how can I make sure the cell is written to as text?
Code:
Sub FillUPCNumber()
Dim arr1 As Variant
Dim ii As Long
Dim str9 As String, str10 As String, str11 As String, str12 As String, str13 As String, str14 As String, str15 As String, str16 As String
Dim Response2 As String
Dim Response3 As Variant
ii = 0
ActiveWorkbook.Sheets("Items").Activate
With ActiveSheet
arr1 = .Range("A1:H" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
For ii = 1 To UBound(arr1)
If arr1(ii, 1) = GetItemNumber Then
str9 = IIf(str9 = "", arr1(ii, 1), str9 & " " & arr1(ii, 1)) 'item number
str10 = IIf(str10 = "", arr1(ii, 2), str10 & " " & arr1(ii, 2)) 'description
str11 = IIf(str11 = "", arr1(ii, 3), str11 & " " & arr1(ii, 3)) 'id
str12 = IIf(str12 = "", arr1(ii, 4), str12 & " " & arr1(ii, 4)) 'date
str13 = IIf(str13 = "", arr1(ii, 5), str13 & " " & arr1(ii, 5)) 'dept#
str14 = IIf(str14 = "", arr1(ii, 6), str14 & " " & arr1(ii, 6)) 'dept
[COLOR=#ff0000] str15 = IIf(str15 = "", arr1(ii, 7), str15 & " " & arr1(ii, 7)) 'upc[/COLOR]
str16 = IIf(str16 = "", arr1(ii, 8), str16 & " " & arr1(ii, 8)) 'price
If Cells(ii, 7).Value = "" Then
[COLOR=#ff0000] Cells(ii, 7).Value = UPCNumber[/COLOR]
Cells(ii, 8).Value = GetPrice
End If
If Cells(ii, 5).Value = "" Then
Cells(ii, 5).Value = GetDeptNum
Cells(ii, 6).Value = GetDepartment
End If
End If
Next
End Sub