VBA runtime error 1004

O

Olbis

New Member
Joined
Mar 4, 2014
Messages
4
Hello everybody, I have some problems with the program CAPCOST 2008 because it give me a runtime error 1004. After I chose an heat exchanger and fill in every request, debug underlines the red part. The program has to return the bare module cost.
This is the complete code:
Code: 
[/COLOR]Dim iSelection As IntegerDim aProperties(7, 1)
Dim sK1 As Single
Dim sK2 As Single
Dim sK3 As Single
Dim sC1 As Single
Dim sC2 As Single
Dim sC3 As Single
Dim sB1 As Single
Dim sB2 As Single
Dim sArea As Single
Dim sShellPressure As Single
Dim sTubePressure As Single
Dim sPressure As Single
Dim lCp As Long
Dim lCBM As Long
Dim lCBM0 As Long
Dim sFM As Single
Dim sFP As Single
Dim bQuestion As Boolean
Dim strExchangerInfo As String
Dim strExchangerMOC As String
Dim strExchangerType As String
Dim strGrassRootsSuffix As String
Dim iTemp As Integer
Dim sHolder As Single


'*****************************************************************************
'   The three modules below change information in "exchangerForm" depending  *
' on whether the action chosen was add or edit.                              *
'*****************************************************************************


Sub displayAddExchanger()
    Unload addEquipmentForm
    Load exchangerForm
    exchangerForm.Caption = "Add an Exchanger to Unit " & Range("unitNumber").Value
    Call prepareExchangerFormUnits
    exchangerForm.Show
End Sub


Sub displayEditExchanger()
    Load exchangerForm
    Call prepareExchangerFormUnits
    Call fillExchangerForm
    exchangerForm.Caption = "Edit Exchanger E-" & (Val(Range("unitNumber")) + iSelection)
    exchangerForm.exchangerFinish.Caption = "Edit"
    exchangerForm.exchangerAdd.Enabled = False
    exchangerForm.exchangerAdd.Visible = False
    exchangerForm.Show
End Sub


Sub prepareExchangerFormUnits()
    With exchangerForm.cbPressure
        .RowSource = ""
        .AddItem "barg"
        .AddItem "kPag"
        .AddItem "psig"
    End With
    With exchangerForm.cbArea
        .RowSource = ""
        .AddItem "square meters"
        .AddItem "square feet"
    End With
        
    Select Case True
        Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
            .Value = "barg"
                exchangerForm.cbPressure.ListIndex = 0
        Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
            .Value = "kPag"
                exchangerForm.cbPressure.ListIndex = 1
        Case Worksheets("User Options").Range("preferencePressure").Offset(0, -1) _
            .Value = "psig"
                exchangerForm.cbPressure.ListIndex = 2
    End Select
    
    Select Case True
        Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
            .Value = "square meters"
                exchangerForm.cbArea.ListIndex = 0
        Case Worksheets("User Options").Range("preferenceArea").Offset(0, -1) _
            .Value = "square feet"
                exchangerForm.cbArea.ListIndex = 1
    End Select
    
End Sub


'**************************************************************************
'   The two modules below call different exchanger procedures depending   *
' on whether the action chosen was add or edit.                           *
'**************************************************************************


Sub addExchanger()
    If Range("userAddedExchangers").Offset(1, 0).Value = "" Then
        Call editEquipment.unhideEquipment("userAddedExchangers")
    End If
    Call getExchangerData
    Call calculateExchangerCosts
    Application.ScreenUpdating = False
    Call insertRowsExchanger
    Call insertExchanger
    Application.ScreenUpdating = True
End Sub


Sub editExchanger()
    Call getExchangerData
    Call calculateExchangerCosts
    Application.ScreenUpdating = False
    Call insertExchanger
    Call listExchangerProperties
    Application.ScreenUpdating = True
End Sub


Sub addMultipleExchangers(iCounter As Integer)
    Application.ScreenUpdating = False
    
    exchangerForm.tbShells = 1
    
    If Range("userAddedExchangers").Offset(1, 0).Value = "" Then
        Call editEquipment.unhideEquipment("userAddedExchangers")
    End If


    Call getExchangerData
    sArea = sArea / iCounter
    Call calculateExchangerCosts
    sArea = sArea * iCounter
    lCp = lCp * iCounter
    lCBM = lCBM * iCounter


    With exchangerForm
        .tbBaseCost.Value = lCp
        .tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,###"])
        .tbModuleCost.Value = lCBM
        .tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,###"])
    End With


    Call insertRowsExchanger
    Call insertExchanger
    
    Application.ScreenUpdating = True
End Sub


Sub editMultipleExchangers(iCounter As Integer)
    Application.ScreenUpdating = False


    exchangerForm.tbShells = 1


    Call getExchangerData
    sArea = sArea / iCounter
    Call calculateExchangerCosts
    sArea = sArea * iCounter
    lCp = lCp * iCounter
    lCBM = lCBM * iCounter


    With exchangerForm
        .tbBaseCost.Value = lCp
        .tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,###"])
        .tbModuleCost.Value = lCBM
        .tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,###"])
    End With
    
    Call insertExchanger
    Call listExchangerProperties
    Application.ScreenUpdating = True
End Sub


' This module takes the data from user form "exchangerForm"


Sub getExchangerData()
    sArea = convert(exchangerForm, "area", Val(exchangerForm.tbArea))
    sShellPressure = convert(exchangerForm, "pressure", Val(exchangerForm.tbPmaxShell))
    sTubePressure = convert(exchangerForm, "pressure", Val(exchangerForm.tbPmaxTube))
    
    Select Case True
        Case exchangerForm.obDouble
            strExchangerInfo = "exchangerDouble"
            strExchangerType = "Double Pipe"
        Case exchangerForm.obMultiple
            strExchangerInfo = "exchangerMultiple"
            strExchangerType = "Multiple Pipe"
        Case exchangerForm.obFixed
            strExchangerInfo = "exchangerFixed"
            strExchangerType = "Fixed, Sheet, or U-Tube"
        Case exchangerForm.obFloating
            strExchangerInfo = "exchangerFloating"
            strExchangerType = "Floating Head"
        Case exchangerForm.obBayonet
            strExchangerInfo = "exchangerBayonet"
            strExchangerType = "Bayonet"
        Case exchangerForm.obKettle
            strExchangerInfo = "exchangerKettle"
            strExchangerType = "Kettle Reboiler"
        Case exchangerForm.obScraped
            strExchangerInfo = "exchangerScraped"
            strExchangerType = "Scraped Wall"
        Case exchangerForm.obTeflon
            strExchangerInfo = "exchangerTeflon"
            strExchangerType = "Teflon Tube"
        Case exchangerForm.obAir
            strExchangerInfo = "exchangerAir"
            strExchangerType = "Air Cooler"
        Case exchangerForm.obSpiralTube
            strExchangerInfo = "exchangerSpiralTube"
            strExchangerType = "Spiral Tube"
        Case exchangerForm.obSpiralPlate
            strExchangerInfo = "exchangerSpiralPlate"
            strExchangerType = "Spiral Plate"
        Case exchangerForm.obFlat
            strExchangerInfo = "exchangerFlat"
            strExchangerType = "Flat Plate"
    End Select
    
    sHolder = -99
    If sArea < Range(strExchangerInfo & "Amin") Then
        sHolder = sArea
        sArea = Range(strExchangerInfo & "Amin")
    End If


    sK1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K1").Value
    sK2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K2").Value
    sK3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "K3").Value
    
    sB1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "B1").Value
    sB2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "B2").Value
    
    sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C1").Value
    sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C2").Value
    sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C3").Value
    
    If exchangerForm.obDouble = True Or exchangerForm.obMultiple = True Or _
    exchangerForm.obScraped = True Then
        If sTubePressure <= 100 And sTubePressure > 40 Then
            sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
            sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
            sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
        End If
        
        If sTubePressure <= 40 Then
            sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C13").Value
            sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C23").Value
            sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C33").Value
        End If
    End If
    
    If exchangerForm.obFixed = True Or exchangerForm.obFloating = True Or _
    exchangerForm.obBayonet = True Or exchangerForm.obKettle = True Then
        If sShellPressure <= 5 And sTubePressure > 5 Then
            sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
            sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
            sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
        End If
    End If
    
    If exchangerForm.obSpiralTube = True Then
        If sShellPressure <= 10 And sTubePressure > 10 Then
            sC1 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C12").Value
            sC2 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C22").Value
            sC3 = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "C32").Value
        End If
    End If
    
    '  New piece of code below, April 1st, 2002
    
    If exchangerForm.obSpiralTube = True Then
        If sShellPressure <= 10 And sTubePressure <= 10 Then
            sC1 = 0
            sC2 = 0
            sC3 = 0
        End If
    End If
        
    Select Case True
        Case exchangerForm.obMOCCSCS.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSCS").Value
            strExchangerMOC = "Carbon Steel / Carbon Steel"
        Case exchangerForm.obMOCCuCS.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSCu").Value
            strExchangerMOC = "Copper / Carbon Steel"
        Case exchangerForm.obMOCCuCu.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCuCu").Value
            strExchangerMOC = "Copper / Copper"
        Case exchangerForm.obMOCSSCS.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSSS").Value
            strExchangerMOC = "Stainless Steel / Carbon Steel"
        Case exchangerForm.obMOCSSSS.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMSSSS").Value
            strExchangerMOC = "Stainless Steel / Stainless Steel"
        Case exchangerForm.obMOCNiCS.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSNi").Value
            strExchangerMOC = "Nickel / Carbon Steel"
        Case exchangerForm.obMOCNiNi.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMNiNi").Value
            strExchangerMOC = "Nickel / Nickel"
        Case exchangerForm.obMOCTiCS.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCSTi").Value
            strExchangerMOC = "Titanium / Carbon Steel"
        Case exchangerForm.obMOCTiTi.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMTiTi").Value
            strExchangerMOC = "Titanium / Titanium"
        Case exchangerForm.obMOCCS.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCS").Value
            strExchangerMOC = "Carbon Steel"
        Case exchangerForm.obMOCCu.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMCu").Value
            strExchangerMOC = "Copper"
        Case exchangerForm.obMOCSS.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMSS").Value
            strExchangerMOC = "Stainless Steel"
        Case exchangerForm.obMOCNi.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMNi").Value
            strExchangerMOC = "Nickel"
        Case exchangerForm.obMOCTi.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMTi").Value
            strExchangerMOC = "Titanium"
        Case exchangerForm.obMOCAl.Value = True
            sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & "FMAl").Value
            strExchangerMOC = "Aluminum"
        Case Else
            MsgBox " Exchanger MOC String Concantenation Problem"
    End Select
     
' The case select below performs a linear interpolation of pressure factor values
' Thia module interpolates the data from worksheet "Equipment Cost Data"


    If sShellPressure > sTubePressure Then sPressure = sShellPressure _
    Else: sPressure = sTubePressure
    
    If sPressure <= 10 Then
        sFP = 1
    End If
    
    If sPressure > 10 Then
        sFP = 10 ^ (sC1 + sC2 * Log(sPressure) / Log(10) + sC3 * (Log(sPressure) / Log(10)) ^ 2)
    End If
    
    If exchangerForm.obFixed = True Or exchangerForm.obFloating = True Or _
    exchangerForm.obBayonet = True Or exchangerForm.obKettle = True Then
        If sPressure > 5 Then
            sFP = 10 ^ (sC1 + sC2 * Log(sPressure) / Log(10) + sC3 * (Log(sPressure) / Log(10)) ^ 2)
        End If
    End If
    
    If sFP < 1 Then
        sFP = 1
    End If
    
End Sub


'************************************************************************************
'   The module below calculates cost for exchangers according to the correlations *
'  given in "Analysis, Synthesis, and Design of Chemical Processes."                *
'************************************************************************************


Sub calculateExchangerCosts()


    If exchangerForm.tbShells > 1 Then
        sArea = sArea / exchangerForm.tbShells
    End If
  
    lCp = 10 ^ (sK1 + sK2 * (Log(sArea) / Log(10)) + sK3 * _
        ((Log(sArea) / Log(10)) ^ 2)) / 397 * Range("CEPCI")
        
    If sHolder <> -99 Then
        sArea = sHolder
    End If


    exchangerForm.tbBaseCost.Value = lCp
    exchangerForm.tbBaseCost = Format(exchangerForm.tbBaseCost, ["$\ #,###,###,000"])
    
    If exchangerForm.tbShells > 1 Then
        sArea = sArea * exchangerForm.tbShells
        lCp = lCp * exchangerForm.tbShells
    End If
    
    lCBM = lCp * (sB1 + sB2 * sFM * sFP)
    exchangerForm.tbModuleCost.Value = lCBM
    exchangerForm.tbModuleCost = Format(exchangerForm.tbModuleCost, ["$\ #,###,###,000"])
End Sub


Sub insertRowsExchanger()
    bQuestion = False
    iSelection = 0
    Range("userAddedExchangers").Select


    Do While bQuestion = False
        iSelection = iSelection + 1
        If IsEmpty(ActiveCell.Offset(iSelection, 0)) Then
            bQuestion = True
        End If
    Loop
    ActiveCell.Offset((iSelection + 1), 0).Rows("1:1").EntireRow.insert Shift:=xlDown
End Sub


Sub insertExchanger()
    Dim iName As Integer
    Dim iUnitNumber As Integer


' Exchanger naming procedure


Range("userAddedExchangers").Offset(iSelection, 0).Value _
    = "=""E-"" & unitNumber + " & Val(iSelection)


    Range("userAddedExchangers").Offset(iSelection, 1).Value = strExchangerType
    Range("userAddedExchangers").Offset(iSelection, 5).Value = strExchangerMOC
    
    iTemp = roundAmount(convert(exchangerForm, "area", Val(exchangerForm.tbArea)) _
    * Range("preferenceArea"))
    Range("userAddedExchangers").Offset(iSelection, 6).Value = "=ROUND(" & _
    sArea & "*preferenceArea, " & iTemp & ")"
    
    If exchangerForm.tbPmaxShell.Enabled = True Then
        iTemp = roundAmount(sShellPressure * Range("preferencePressure"))
        Range("userAddedExchangers").Offset(iSelection, 2).Value = "=ROUND(" & _
        (sShellPressure) & "*preferencePressure, " & iTemp & ")"
    End If
    
    If exchangerForm.tbPmaxShell.Enabled = False Then
        Range("userAddedExchangers").Offset(iSelection, 2).Value = ""
    End If
        
    iTemp = roundAmount(sTubePressure * Range("preferencePressure"))
    Range("userAddedExchangers").Offset(iSelection, 3).Value = "=ROUND(" & _
        (sTubePressure) & "*preferencePressure, " & iTemp & ")"


    iTemp = roundAmount(lCp)
    Range("userAddedExchangers").Select
[COLOR=#ff0000]    ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _[/COLOR]
[COLOR=#ff0000]        (lCp / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"[/COLOR]
    ActiveCell.Offset(iSelection, 7).Style = "Currency"
    ActiveCell.Offset(iSelection, 7).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"


    iTemp = roundAmount(lCBM)
    ActiveCell.Offset(iSelection, 8).Value = "=ROUND(" & _
        (lCBM / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
    ActiveCell.Offset(iSelection, 8).Style = "Currency"
    ActiveCell.Offset(iSelection, 8).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
End Sub


'***************************************************************************
'   This module fills the edit version of exchangerForm with information   *
' regarding the exchanger that has been chosen for editing.  Both of the   *
' modules below are for editing an existing exchanger.                     *
'***************************************************************************


Sub fillExchangerForm()
    Select Case Range("userAddedExchangers").Offset(iSelection, 1).Value
        Case "Double Pipe"
            exchangerForm.obDouble = True
        Case "Multiple Pipe"
            exchangerForm.obMultiple = True
        Case "Fixed, Sheet, or U-Tube"
            exchangerForm.obFixed = True
        Case "Floating Head"
            exchangerForm.obFloating = True
        Case "Bayonet"
            exchangerForm.obBayonet = True
        Case "Kettle Reboiler"
            exchangerForm.obKettle = True
        Case "Scraped Wall"
            exchangerForm.obScraped = True
        Case "Teflon Tube"
            exchangerForm.obTeflon = True
        Case "Air Cooler"
            exchangerForm.obAir = True
        Case "Spiral Tube"
            exchangerForm.obSpiralTube = True
        Case "Spiral Plate"
            exchangerForm.obSpiralPlate = True
        Case "Flat Plate"
            exchangerForm.obFlat = True
    End Select
    
   Select Case Range("userAddedExchangers").Offset(iSelection, 5).Value
        Case "Carbon Steel / Carbon Steel"
            exchangerForm.obMOCCSCS.Value = True
        Case "Copper / Carbon Steel"
            exchangerForm.obMOCCuCS.Value = True
        Case "Copper / Copper"
            exchangerForm.obMOCCuCu.Value = True
        Case "Stainless Steel / Carbon Steel"
            exchangerForm.obMOCSSCS.Value = True
        Case "Stainless Steel / Stainless Steel"
            exchangerForm.obMOCSSSS.Value = True
        Case "Nickel / Carbon Steel"
            exchangerForm.obMOCNiCS.Value = True
        Case "Nickel / Nickel"
            exchangerForm.obMOCNiNi.Value = True
        Case "Titanium / Carbon Steel"
            exchangerForm.obMOCTiCS.Value = True
        Case "Titanium / Titanium"
            exchangerForm.obMOCTiTi.Value = True
        Case "Carbon Steel"
            exchangerForm.obMOCCS.Value = True
        Case "Copper"
            exchangerForm.obMOCCu.Value = True
        Case "Stainless Steel"
            exchangerForm.obMOCSS.Value = True
        Case "Nickel"
            exchangerForm.obMOCNi.Value = True
        Case "Titanium"
            exchangerForm.obMOCTi.Value = True
        Case "Aluminum"
            exchangerForm.obMOCAl.Value = True
        Case Else
            MsgBox " Exchangerr MOC String Concantenation Problem"
    End Select
    
    exchangerForm.tbPmaxTube.Value = Range("userAddedExchangers").Offset(iSelection, 3).Value
    exchangerForm.tbArea.Value = Range("userAddedExchangers").Offset(iSelection, 6).Value
    If exchangerForm.tbPmaxShell.Enabled = True Then
        exchangerForm.tbPmaxShell.Value = Range("userAddedExchangers").Offset(iSelection, 2).Value
    End If
End Sub


Sub listExchangerProperties()
    editEquipmentForm.lbEquipmentDescription.Clear


' This section of code fills an array with the selected exchanegr's properties
' The properties are then displeyd in the edit window


    iSelection = editEquipmentForm.lbEquipmentName.ListIndex + 1
    
    aProperties(0, 0) = "Exchanger Type.............................."
        aProperties(0, 1) = Range("userAddedExchangers").Offset(iSelection, 1)
        
        aProperties(1, 0) = "Area....................................."
        aProperties(1, 1) = Range("userAddedExchangers").Offset(iSelection, 6) & " " _
            & Range("preferenceArea").Offset(0, -1).Value
    
    
        aProperties(2, 0) = "Tube Side Pressure........................"
        aProperties(2, 1) = Range("userAddedExchangers").Offset(iSelection, 3) & " " _
            & Range("preferencePressure").Offset(0, -1).Value
    
        aProperties(3, 0) = ""
        aProperties(3, 1) = ""
        
    If Range("userAddedExchangers").Offset(iSelection, 2) <> "" Then
        aProperties(3, 0) = "Shell Side Pressure..........................."
        aProperties(3, 1) = Range("userAddedExchangers").Offset(iSelection, 2) & " " _
        & Range("preferencePressure").Offset(0, -1).Value
    End If
         
    aProperties(4, 0) = "Construction material........................"
    aProperties(4, 1) = Range("userAddedExchangers").Offset(iSelection, 5)
    aProperties(5, 0) = " "
    aProperties(5, 1) = " "
    aProperties(6, 0) = "Base Cost...................................."
    aProperties(6, 1) = Format(Range("userAddedExchangers").Offset(iSelection, 7), ["$\ #,###,###,###"])
    aProperties(7, 0) = "Bare Module Cost............................."
    aProperties(7, 1) = Format(Range("userAddedExchangers").Offset(iSelection, 8), ["$\ #,###,###,###"])


    editEquipmentForm.lbEquipmentDescription.List = aProperties
End Sub


' The modules below are used for adding the exchangers to the cost summary and updating
' exchangers in the cost summary as they are edited


Sub addSummaryExchanger()
    Dim rRange
    Application.ScreenUpdating = False


    Do While Range("costSummary").Offset(iCounter, 0).Value <> ""
        iCounter = iCounter + 1
    Loop
    
    Range("costSummary").Offset(iCounter, 0).Rows("1:1").EntireRow.insert Shift:=xlDown
    
    Range("costSummary").Offset(iCounter, 0) = "=""E-"" & unitNumber + " & Val(iSelection)
    iTemp = roundAmount(Range("totalModuleFactor") * lCBM0 / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 1) = "=ROUND(totalModuleFactor*" & lCBM & _
    ", " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 1).Style = "Currency"
    Range("costSummary").Offset(iCounter, 1).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Call exchangerGrassRoots
    
    iTemp = roundAmount((Range("totalModuleFactor") * lCBM + _
    Range("grassRootsFactor") * lCBM0) / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 3) = "=ROUND((totalModuleFactor*" & lCBM & _
    "+ grassRootsFactor*" & lCBM0 & "), " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 3).Style = "Currency"
    Range("costSummary").Offset(iCounter, 3).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
' Procedure below fills in cell with colors and shading to let user
' know that these cells should be clicked on


    Range("costSummary").Offset(iCounter, 5) = "Unspecified"
    With Range("costSummary").Offset(iCounter, 5).Interior
        .ColorIndex = 37
        .Pattern = xlSolid
    End With
    Range("costSummary").Offset(iCounter, 5).Font.ColorIndex = 36
    Application.ScreenUpdating = True
End Sub


Sub editSummaryExchanger()
Dim rRange
    Application.ScreenUpdating = False


    iCounter = 0
    strExchangerMOC = "E-" & (Range("unitNumber")) + Val(iSelection)
    Do While Range("costSummary").Offset(iCounter, 0) <> strExchangerMOC
        iCounter = iCounter + 1
    Loop
    
    Range("costSummary").Offset(iCounter, 0) = "=""E-"" & unitNumber + " & Val(iSelection)
    iTemp = roundAmount(Range("totalModuleFactor") * lCBM0 / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 1) = "=ROUND(totalModuleFactor*" & lCBM & _
    ", " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 1).Style = "Currency"
    Range("costSummary").Offset(iCounter, 1).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Call exchangerGrassRoots
    
    iTemp = roundAmount((Range("totalModuleFactor") * lCBM + _
    Range("grassRootsFactor") * lCBM0) / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 3) = "=ROUND((totalModuleFactor*" & lCBM & _
    "+ grassRootsFactor*" & lCBM0 & "), " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 3).Style = "Currency"
    Range("costSummary").Offset(iCounter, 3).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Application.ScreenUpdating = True
End Sub


Sub exchangerGrassRoots()
    strGrassRootsSuffix = "FMCSCS"


    If exchangerForm.obTeflon = True Or exchangerForm.obSpiralPlate = True _
    Or exchangerForm.obFlat = True Or exchangerForm.obAir = True Then
        strGrassRootsSuffix = "FMCS"
    End If
    
    sFM = Worksheets("Equipment Cost Data").Range(strExchangerInfo & strGrassRootsSuffix).Value
    lCBM0 = lCp * (sB1 + sB2 * sFM)
End Sub


[COLOR=#333333]
 
Sort by date Sort by votes
I suggest you temporarily remove parts of the line of code so you can see which one is not working.
 
Upvote 0
Loooks like you just missed a quote mark or two.
Code: 
ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
       [COLOR=#ff0000]"[/COLOR](lCp / Range("CEPCI"))[COLOR=#ff0000]"[/COLOR] & "*CEPCI, " & iTemp & ")"
 
Last edited:
Upvote 0
JLGWhiz said:
Loooks like you just missed a quote mark or two.
Code: 
ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
       [COLOR=#ff0000]"[/COLOR](lCp / Range("CEPCI"))[COLOR=#ff0000]"[/COLOR] & "*CEPCI, " & iTemp & ")"
Click to expand...



Thank you JLGWhiz, but the code doesn't run still and give me the same error...what can i do?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Spot the Stack Space Error! (Runtime Error 28)
Replies
6
Views
1K
Tarkemelion
T
S
  • Solved
Trouble With Find Works fine for days then Errors with "runtime error 1004 select method of range class failed"
Replies
5
Views
1K
Shiseiji
S
M
  • Question Question
runtime error 1004
Replies
2
Views
586
mellows23
M
D
  • Question Question
Error 1004 Help. I'm over my head!
Replies
3
Views
752
DravenCA
D
O
  • Question Question
vb help with CAPCOST software
Replies
0
Views
4K
Olbis
O

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top