Error on ActiveCell.Offset().Value

S

Shidao

New Member
Joined
Aug 6, 2012
Messages
5
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!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
How is the named range userAddedCentrifuges defined?
 
Upvote 0
Norie said:
How is the named range userAddedCentrifuges defined?
Click to expand...

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!
 
Upvote 0
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.
 
Upvote 0
That should have read '...the code selects it immediately before the problem line is executed'.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
Click to expand...

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

Cheers!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Locked
  • Question
Capcost help
Replies
1
Views
6K
RoryA
RoryA
O
  • Question
vb help with CAPCOST software
Replies
0
Views
3K
Olbis
O
O
  • Question
VBA runtime error 1004
Replies
3
Views
2K
Olbis
O
B
  • Question
#Value error only first time round code
Replies
1
Views
676
erik.van.geit
erik.van.geit

Forum statistics

Threads
1,216,172
Messages
6,129,290
Members
449,498
Latest member
Lee_ray

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