CODEWITHSUNDEEP
excelvba
James Hurst
James Hurst

Reputation: 63

VBA not working if there is an empty cell

I have been working on script which will first import a CSV and then add values to cells depending on the value in the 'material' column.

I can get the script to work, but only if all the cells have values.

This is not always possible as some of the data which is imported will have these values missing.

I tried to add a line to the code which would add a value of "0" to the cell if it was blank. I was hoping this line would be executed in sequence, but that is not the case. I get the run time error 1004 message. I know it works if there is a numerical value in the cell.

Sub CostCal()

'Call load_csv

    Dim Firstrow As Long
    Dim lastrow As Long
    Dim lRow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim i As Long
    Dim ws2 As Worksheet

    Application.Volatile


    'Optimize Code
      Call OptimizeCode_Begin

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        'ViewMode = ActiveWindow.View
       'ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        '.DisplayPageBreaks = False

        Firstrow = .UsedRange.Cells(1).Row
        lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        For lRow = lastrow To Firstrow Step -1

            Set ws2 = ThisWorkbook.Sheets("MASTER")


            With .Cells(lRow, "G")

                If Not IsError(.Value) Then

                       If Cells(lRow, "D").Value = "" Then Cells(lRow, "D").Value = ("0")

                       'If .Value Like ("*MS*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H5").Value
                       If .Value Like ("*MS*") Then Cells(lRow, "D").Value = ("=" & Cells(lRow, "E") & "*MASTER!$H$5")

                       If .Value Like ("*ANGLE*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H6").Value

                       If .Value Like ("*BOX SECTION*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H6").Value

                       If .Value Like ("*CHANNEL*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H6").Value

                       If .Value Like ("*I-BEAM*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H6").Value

                       If .Value Like ("*PIPE*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H6").Value

                       If .Value Like ("*ROUND-BAR*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H6").Value

                       If .Value Like ("*SQUARE-BAR*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H6").Value

                       If .Value Like ("*STAINLESS-STEEL*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H7").Value

                       If .Value Like ("*THREADED-BAR*") Then Cells(lRow, "D").Value = Cells(lRow, "D").Value * ws2.Range("H8").Value

                       'If .Value Like ("*PURCHASED*") Then Cells(lRow, "D").Value = Cells(lRow, "D").Value * ws2.Range("H8").Value
                       If .Value Like ("*PURCHASED*") Then Cells(lRow, "D").Value = "=(RC[-6]) * (ws2.range(R8, C8)"

                       If .Value Like ("*POLYCARBONATE*") Then Cells(lRow, "D").Value = Cells(lRow, "D").Value * ws2.Range("H8").Value

                       If .Value Like ("*POLYURETHANE*") Then Cells(lRow, "D").Value = Cells(lRow, "D").Value * ws2.Range("H8").Value

                       If .Value Like ("*PVC*") Then Cells(lRow, "D").Value = Cells(lRow, "D").Value * ws2.Range("H8").Value

                       If .Value Like ("*RUBBER*") Then Cells(lRow, "D").Value = Cells(lRow, "D").Value * ws2.Range("H8").Value

                       If .Value Like ("*DURBAR*") Then Cells(lRow, "D").Value = Cells(lRow, "E").Value * ws2.Range("H5").Value

                       If .Value Like ("*1_INCH_MESH*") Then Cells(lRow, "D").Value = Cells(lRow, "D").Value * ws2.Range("H8").Value

                      'If .Cells.Offset(0, -6).Value Like ("*Part*") Then Cells(lRow, "J").Formula = ("=" & Cells(lRow, "D") & "*" & Cells(lRow, "C"))

                       If .Cells.Offset(0, -6).Value Like ("*Part*") Then Cells(lRow, "J").Value = "=(RC[-6]*RC[-7])"


                End If

            End With

        Next lRow

    End With

'Optimize Code
  Call OptimizeCode_End

End Sub

The routine should look for a value in column D, if the cell value matches that of the code then it will multiply by the value in the specific cell in the master sheet. I have two options below, the first will use only the values and the second will use the cell location, the second allows me to easily update the native cost for the specific material.

The final If statement will provide a total in column J for which is quantity x unit cost

I still need to provide a sum total at the bottom of this column, but I want to get the first part of the code working.

I'm sure there are much smarter and simpler methods for doing what I need to do. but I do not have the knowledge yet...

Many Thanks for any and all help.

Upvotes: 0

Views: 73

Answers (1)

James Hurst
James Hurst

Reputation: 63

I replaced the line:

If Cells(lRow, "D").Value = "" Then Cells(lRow, "D").Value = ("0")

With:

If Trim(.Cells.Offset(0, -3)) = "" Then Cells(lRow, "D").Value = ("0")

Turns out the cell was full of spaces, so it needed to be trimmed first

Upvotes: 1

Related Questions