Macro inserting zeroes

Question

Any help would be much appreciated. This excel vb script searches for X or x in a sheet named "Order sheet -SL" and then insert specific cells values into a new sheet. It is working well except that it’s inserting 0’s (Zero) in the new sheet insert of the actual values

Source Code:

Sub SOLIMPORT2(worksheetname As String)
    Dim Order As Worksheet
    Dim SolomonImport As Worksheet

    Set Order = Worksheets("Order sheet -SL")
    Set SolomonImport = Worksheets(worksheetname)


    Dim i As Integer
    Dim j As Integer

    i = 8
    j = 3




    Do
        If Order.Cells(i, 6) = "x" Or Order.Cells(i, 6) = "X" Then
            SolomonImport.Range("A1") = "LEVEL0"
            SolomonImport.Range("B1") = "SO #:"
            SolomonImport.Range("C1") = "PSO"
            SolomonImport.Range("A3") = "LEVEL1"
            SolomonImport.Cells(j, 2) = Order.Cells(i, 11)
            SolomonImport.Cells(j, 3) = Order.Cells(i, 13)
            'Range("I10").Value = Range("F10").Value

            SolomonImport.Cells(j, 4) = Order.Cells(i, 14)
            SolomonImport.Cells(j, 5) = Order.Cells(i, 15)
            'SolomonImport.Cells(j, 31) = Order.Range("F1")
            'SolomonImport.Cells(j, 32) = "TC-50"
            'SolomonImport.Cells(j, 3) = Order.Cells(i, 11)
            'SolomonImport.Cells(j, 5) = "0"
            'SolomonImport.Cells(j, 13) = "0"
            'SolomonImport.Cells(j, 37) = Order.Cells(i, 8)
            'SolomonImport.Cells(j, 38) = Order.Cells(i, 10)

            j = j + 1
        End If

        i = i + 1

        If Order.Range("A" & Trim(Str(i))) = "SHEET TAB Z" Then
            Exit Do
        End If
    Loop

End Sub

Example: Ordered Sheet –SL tab contained source data:

   RELEASE    PART #      QUANTITY         UNIT COST       EXTENDED COST

   x          MX418 S/C     1              147.9           147.9
   x          MX418 D/C     4              178.5           714
   x          MX418 S/C     2              147.9       295.8

The new sheet and the output:

The new sheet: The yellow highlighted cells above are showing as 0 below. The unite Cost cells contained this formula (=A!L9) and the Extended Cost cells formula is Quantity multiply by Unit Cost.

MX418 S/C   1   0   1

MX418 D/C   4   0   0

MX418 S/C   2   0   0

Thank you.

Tim Tim · Accepted Answer · 2014-05-21T01:20:47

It looks like your code is just copying the formulas rather than the values from the original sheet.

Try adding .value at the end of each of the order.cells(x,y)'s.

i.e. SolomonImport.Cells(j, 4) = Order.Cells(i, 14).value

Macro inserting zeroes

1 Answers