Error on ActiveCell.Offset().Value

Shidao · Aug 6, 2012

Hello guys,

I'm testing a Spreedsheet - CAPCOST, from the book Analysis, Synthesis, and Design of Chemical Processes, but it's not working, always when I try to use "add an equipment" a Run-time error 1004 show up in an yellow arrow in line of this command :

Code:

    ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
        (centrifugeForm.tbBaseCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"

Code macro complete:

Code:

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


Sub calculateCentrifugeCosts()
    lCp = 10 ^ (sK1 + sK2 * (Log(sLength) / Log(10)) + sK3 * _
        ((Log(sLength) / Log(10)) ^ 2)) * (iSpares + 1) / 397 * Range("CEPCI")
        
    If sHolder <> -99 Then
        sLength = sHolder
    End If
    
    lCBM = lCp * sFBM


    centrifugeForm.tbBaseCost.Value = lCp
    centrifugeForm.tbBaseCost = Format(centrifugeForm.tbBaseCost, ["$\ #,###,###,###"])


    centrifugeForm.tbModuleCost.Value = lCBM
    centrifugeForm.tbModuleCost = Format(centrifugeForm.tbModuleCost, ["$\ #,###,###,###"])
    
End Sub


Sub insertRowsCentrifuge()
    bQuestion = False
    iSelection = 0
    Range("userAddedCentrifuges").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 insertCentrifuge()
' Centrifuge naming procedure


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


    Range("userAddedCentrifuges").Offset(iSelection, 1).Value = strCentrifugeType
    Range("userAddedCentrifuges").Offset(iSelection, 3).Value = iSpares
    
    iTemp = roundAmount(sLength * Range("preferenceLength"))
    Range("userAddedCentrifuges").Offset(iSelection, 2).Value = "=ROUND(" & _
    sLength & "*preferenceLength, " & iTemp & ")"
    
    iTemp = roundAmount(centrifugeForm.tbBaseCost)
    Range("userAddedCentrifuges").Select
[COLOR=#daa520]   [B] ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
        (centrifugeForm.tbBaseCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"[/B][/COLOR]
    ActiveCell.Offset(iSelection, 7).Style = "Currency"
    ActiveCell.Offset(iSelection, 7).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"


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

End Sub

Well, I'm a newbee in VBA macros on excel...

Thanks a lot in advance!

Norie · Aug 6, 2012

How is the named range userAddedCentrifuges defined?

Shidao · Aug 6, 2012

Norie said:
How is the named range userAddedCentrifuges defined?

I will quote the hole code, if its help:

Code:

'Need to convert the centrifuge form to a centrifuge form  12/29/2004


Dim iSelection As Integer
Dim aProperties(7, 1)
Dim sK1 As Single
Dim sK2 As Single
Dim sK3 As Single
Dim sFM As Single
Dim sLength As Single
Dim lCp As Long
Dim sFP As Single
Dim iSpares As Integer
Dim lCBM As Long
Dim lCBM0 As Long
Dim sFBM As Single
Dim bQuestion As Boolean
Dim strCentrifugeInfo As String
Dim strCentrifugeMOC As String
Dim strCentrifugeType As String
Dim iTemp As Integer
Dim sHolder As Single
Dim iCounter As Integer


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


Sub displayAddCentrifuge()
    Unload addEquipmentForm
    Load centrifugeForm
    centrifugeForm.Caption = "Add a Centrifuge to Unit " & Range("unitNumber").Value
    Call prepareCentrifugeFormUnits
    centrifugeForm.Show
End Sub


Sub displayEditCentrifuge()
    Load centrifugeForm
    Call prepareCentrifugeFormUnits
    Call fillCentrifugeForm
    
    centrifugeForm.Caption = "Edit Centrifuge Ct-" & (Val(Range("unitNumber")) + iSelection)
    
    centrifugeForm.centrifugeFinish.Caption = "Edit"
    centrifugeForm.centrifugeAdd.Enabled = False
    centrifugeForm.centrifugeAdd.Visible = False
    centrifugeForm.Show
End Sub


Sub prepareCentrifugeFormUnits()
    With centrifugeForm.cbLength
        .RowSource = ""
        .AddItem "meters"
        .AddItem "feet"
    End With
    
    Select Case True
        Case Worksheets("User Options").Range("preferenceLength").Offset(0, -1) _
            .Value = "meters"
                centrifugeForm.cbLength.ListIndex = 0
        Case Worksheets("User Options").Range("preferenceLength").Offset(0, -1) _
            .Value = "feet"
                centrifugeForm.cbLength.ListIndex = 1
    End Select
End Sub


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


Sub addCentrifuge()
    If Range("userAddedCentrifuges").Offset(1, 0).Value = "" Then
        Call editEquipment.unhideEquipment("userAddedCentrifuges")
    End If
    Call getCentrifugeData
    Call calculateCentrifugeCosts
    Application.ScreenUpdating = False
    Call insertRowsCentrifuge
    Call insertCentrifuge
    Application.ScreenUpdating = True
End Sub


Sub editCentrifuge()
    Call getCentrifugeData
    Call calculateCentrifugeCosts
    Application.ScreenUpdating = False
    Call insertCentrifuge
    Call listCentrifugeProperties
    Application.ScreenUpdating = True
End Sub


Sub addMultipleCentrifuges(iCounter As Integer)
    Application.ScreenUpdating = False
    
    If Range("userAddedCentrifuges").Offset(1, 0).Value = "" Then
        Call editEquipment.unhideEquipment("userAddedCentrifuges")
    End If
    
    Call getCentrifugeData
    sLength = sLength / iCounter
    Call calculateCentrifugeCosts
    sLength = sLength * iCounter
    lCp = lCp * iCounter
    lCBM = lCBM * iCounter
    
    With centrifugeForm
        .tbBaseCost.Value = lCp
        .tbBaseCost = Format(centrifugeForm.tbBaseCost, ["$\ #,###,###,###"])
        .tbModuleCost.Value = lCBM
        .tbModuleCost = Format(centrifugeForm.tbModuleCost, ["$\ #,###,###,###"])
    End With


    Call insertRowsCentrifuge
    Call insertCentrifuge
    
    Application.ScreenUpdating = True
End Sub


Sub editMultipleCentrifuges(iCounter As Integer)
    Application.ScreenUpdating = False
    
    Call getCentrifugeData
    sLength = sLength / iCounter
    Call calculateCentrifugeCosts
    sLength = sLength * iCounter
    lCp = lCp * iCounter
    lCBM = lCBM * iCounter
    
    With centrifugeForm
        .tbBaseCost.Value = lCp
        .tbBaseCost = Format(centrifugeForm.tbBaseCost, ["$\ #,###,###,###"])
        .tbModuleCost.Value = lCBM
        .tbModuleCost = Format(centrifugeForm.tbModuleCost, ["$\ #,###,###,###"])
    End With
    Call insertCentrifuge
    Call listCentrifugeProperties
    Application.ScreenUpdating = True
End Sub


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


Sub getCentrifugeData()
    sLength = convert(centrifugeForm, "length", Val(centrifugeForm.tbLength))
    iSpares = Val(centrifugeForm.tbSpares)
    
    Select Case True
        Case centrifugeForm.obAutoBatch
            strCentrifugeInfo = "centrifugeAutoBatch"
            strCentrifugeType = "Auto Batch"
        Case centrifugeForm.obCentrifugal
            strCentrifugeInfo = "centrifugeCentrifugal"
            strCentrifugeType = "Centrifugal"
        Case centrifugeForm.obOscillating
            strCentrifugeInfo = "centrifugeOscillating"
            strCentrifugeType = "Oscillating"
        Case centrifugeForm.obSolidBowl
            strCentrifugeInfo = "centrifugeSolidBowl"
            strCentrifugeType = "Solid Bowl"
    End Select
    
    sHolder = -99
    If sLength < Range(strCentrifugeInfo & "Dmin") Then
        sHolder = sLength
        sLength = Range(strCentrifugeInfo & "Dmin")
    End If


    sK1 = Worksheets("Equipment Cost Data").Range(strCentrifugeInfo & "K1").Value
    sK2 = Worksheets("Equipment Cost Data").Range(strCentrifugeInfo & "K2").Value
    sK3 = Worksheets("Equipment Cost Data").Range(strCentrifugeInfo & "K3").Value
    
    sFBM = Worksheets("Equipment Cost Data").Range(strCentrifugeInfo & "FBM").Value  '2007
End Sub


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


Sub calculateCentrifugeCosts()
    lCp = 10 ^ (sK1 + sK2 * (Log(sLength) / Log(10)) + sK3 * _
        ((Log(sLength) / Log(10)) ^ 2)) * (iSpares + 1) / 397 * Range("CEPCI")
        
    If sHolder <> -99 Then
        sLength = sHolder
    End If
    
    lCBM = lCp * sFBM


    centrifugeForm.tbBaseCost.Value = lCp
    centrifugeForm.tbBaseCost = Format(centrifugeForm.tbBaseCost, ["$\ #,###,###,###"])


    centrifugeForm.tbModuleCost.Value = lCBM
    centrifugeForm.tbModuleCost = Format(centrifugeForm.tbModuleCost, ["$\ #,###,###,###"])
    
End Sub


Sub insertRowsCentrifuge()
    bQuestion = False
    iSelection = 0
    Range("userAddedCentrifuges").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 insertCentrifuge()
' Centrifuge naming procedure


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


    Range("userAddedCentrifuges").Offset(iSelection, 1).Value = strCentrifugeType
    Range("userAddedCentrifuges").Offset(iSelection, 3).Value = iSpares
    
    iTemp = roundAmount(sLength * Range("preferenceLength"))
    Range("userAddedCentrifuges").Offset(iSelection, 2).Value = "=ROUND(" & _
    sLength & "*preferenceLength, " & iTemp & ")"
    
    iTemp = roundAmount(centrifugeForm.tbBaseCost)
    Range("userAddedCentrifuges").Select
    ActiveCell.Offset(iSelection, 7).Value = "=ROUND(" & _
        (centrifugeForm.tbBaseCost / Range("CEPCI")) & "*CEPCI, " & iTemp & ")"
    ActiveCell.Offset(iSelection, 7).Style = "Currency"
    ActiveCell.Offset(iSelection, 7).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"


    iTemp = roundAmount(centrifugeForm.tbModuleCost)
    ActiveCell.Offset(iSelection, 8).Value = "=ROUND(" & _
        (centrifugeForm.tbModuleCost / 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 centrifugeForm with information   *
' regarding the centrifuge that has been chosen for editing.  Both of the   *
' modules below are for editing an existing ventrifuge.                     *
'****************************************************************************


Sub fillCentrifugeForm()
    Select Case Range("userAddedCentrifuges").Offset(iSelection, 1).Value
        Case "Auto Batch"
            centrifugeForm.obAutoBatch = True
        Case "Centrifugal"
            centrifugeForm.obCentrifugal = True
        Case "Oscillating"
            centrifugeForm.obOscillating = True
        Case "Solid Bowl"
            centrifugeForm.obSolidBowl = True
    End Select
    
    centrifugeForm.tbLength = Val(Range("userAddedCentrifuges").Offset(iSelection, 2))
    centrifugeForm.tbSpares = Val(Range("userAddedCentrifuges").Offset(iSelection, 3))
End Sub


Sub listCentrifugeProperties()
    editEquipmentForm.lbEquipmentDescription.Clear


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


    iSelection = editEquipmentForm.lbEquipmentName.ListIndex + 1


    aProperties(0, 0) = "Centrifuge Type..............................."
    aProperties(0, 1) = Range("userAddedCentrifuges").Offset(iSelection, 1)
    aProperties(1, 0) = "Centrifuge Diameter.............................."
    aProperties(1, 1) = Range("userAddedCentrifuges").Offset(iSelection, 2) & " " _
        & Range("preferenceLength").Offset(0, -1).Value
    aProperties(2, 0) = "Number of Spares............................."
    aProperties(2, 1) = Range("userAddedCentrifuges").Offset(iSelection, 3)
    aProperties(3, 0) = " "
    aProperties(3, 1) = " "
    aProperties(4, 0) = " "
    aProperties(4, 1) = " "
    aProperties(5, 0) = " "
    aProperties(5, 1) = " "
    aProperties(6, 0) = "Base Cost...................................."
    aProperties(6, 1) = Format(Range("userAddedCentrifuges").Offset(iSelection, 7), ["$\ #,###,###,###"])
    aProperties(7, 0) = "Bare Module Cost............................."
    aProperties(7, 1) = Format(Range("userAddedCentrifuges").Offset(iSelection, 8), ["$\ #,###,###,###"])


    editEquipmentForm.lbEquipmentDescription.List = aProperties
End Sub


' The modules below are used for adding the centrifuges to the cost summary, updating
' centrifuges in the cost summary as they are edited, and deleting centrifuges from
' the cost summary.


Sub addSummaryCentrifuge()
    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) = "=""Ct-"" & unitNumber + " & Val(iSelection)
    iTemp = roundAmount(lCBM * Range("totalModuleFactor") / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 1) = "=ROUND(" & lCBM & _
    "*totalModuleFactor, " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 1).Style = "Currency"
    Range("costSummary").Offset(iCounter, 1).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Call centrifugeGrassRoots
    
    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);_($* ""-""??_);_(@_)"
    
    Range("costSummary").Offset(iCounter, 5).ClearFormats
    Range("costSummary").Offset(iCounter, 5).HorizontalAlignment = xlCenter
    Range("costSummary").Offset(iCounter, 5).Font.Size = 12


    Range("costSummary").Offset(iCounter, 5) = "NA"
End Sub


Sub editSummaryCentrifuge()
Dim rRange
    Application.ScreenUpdating = False


    iCounter = 0
    strCentrifugeMOC = "Ct-" & (Range("unitNumber")) + Val(iSelection)
    Do While Range("costSummary").Offset(iCounter, 0) <> strCentrifugeMOC
        iCounter = iCounter + 1
    Loop
    
    Range("costSummary").Offset(iCounter, 0) = "=""Ct-"" & unitNumber + " & Val(iSelection)
    iTemp = roundAmount(lCBM * Range("totalModuleFactor") / 397 * Range("CEPCI"))
    Range("costSummary").Offset(iCounter, 1) = "=ROUND(" & lCBM & _
    "*totalModuleFactor, " & iTemp & ")"
    Range("costSummary").Offset(iCounter, 1).Style = "Currency"
    Range("costSummary").Offset(iCounter, 1).NumberFormat = _
    "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"
    
    Call centrifugeGrassRoots
    
    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);_($* ""-""??_);_(@_)"
    
    Range("costSummary").Offset(iCounter, 5).ClearFormats
    Range("costSummary").Offset(iCounter, 5).HorizontalAlignment = xlCenter
    Range("costSummary").Offset(iCounter, 5).Font.Size = 12
    
    Range("costSummary").Offset(iCounter, 5) = "NA"
End Sub


Sub centrifugeGrassRoots()
    lCBM0 = lCp
End Sub

Thanks in advance!

Norie · Aug 6, 2012

I'm afraid the range isn't defined/created in that code.

Are you using a template sheet with predefined named ranges?

Or is there other code?

The reason I'm asking about this named range is because the code selects it immediately meaning that when the line of code causing the error is executed ActiveCell is in that range.

So the problem could have something to do with this range.

Shidao · Aug 6, 2012

Yes, I suppose that there are template sheets with predefined ranges.

If it's easy to visualize, here is the file - https://www.dropbox.com/s/4kz25b08mt6g8gf/CAPCOST_2012.xls

Shidao · Aug 6, 2012

Also, here is an youtube video that I found, explaining the use of the spreedsheets. - http://www.youtube.com/watch?v=iDOtNZdNl_U&feature=plcp

Norie · Aug 6, 2012

That should have read '...the code selects it immediately before the problem line is executed'.

Norie · Aug 6, 2012

I've found the problem in the code it's the currency formatting of Base Cost and Bare Module Cost causing a type mismatch error.

Do you need that formatting in the actual textbox?

If you don't you could just add a $ after the 2 labels.

Norie · Aug 6, 2012

Actually you can keep the $ in the textbox if you alter the formatting.

The current format is ["$/ #,###,###,###"] but if you replace that with ["$#,###,###,###"] then the code should work.

You can do a find and replace on the entire project for that.

Shidao · Aug 6, 2012

Norie said:
Actually you can keep the $ in the textbox if you alter the formatting.

The current format is ["$/ #,###,###,###"] but if you replace that with ["$#,###,###,###"] then the code should work.

You can do a find and replace on the entire project for that.

Thanks a lot Norie!!! Its working perfectly right now!!!

Cheers!!!

Error on ActiveCell.Offset().Value

Shidao

New Member

Norie

Well-known Member

Shidao

New Member

Norie

Well-known Member

Shidao

New Member

Shidao

New Member

Norie

Well-known Member

Norie

Well-known Member

Norie

Well-known Member

Shidao

New Member

Similar threads

Share this page

Error on ActiveCell.Offset().Value

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock