Error on ActiveCell.Offset().Value

[Shidao]

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 :

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

Code macro complete:

'*************************************************************************************
' 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]

Glad it's working.

Just wondering, have you checked the formulas in the workbook?

I looked at some of them and they didn't seem right.

Mind you that might have been due to the problem you were having.

 

[iklil]

Hi Hi !

I seem to have a VERY SIMILAR problem, yet, the solution provided did not actually work for me...

Then I tried to find out if it was not working for me because I was trying to select a filter... Decided to try on the centrifuge as it sounded like it was solving the problem for that, yet - the solution did not help and I still have the same problem as the person who has originally reported this issue.

Apparently the document has been updated, version I have is from 2017 and I can see the original problem was reported here in 2012. So there is a possibility that the document was updated. Yet I find it quite astounding that both centrifuge and the filter suffered form the same "$\ #" issue.

Here is where I am directed to once I see the Run time Error "13" - Type mismatch:

I have NO experience in visual basic and have never been good ad coding so... All I can say is - I think I did it right???

Not sure.

I have saved both versions (where I added the correction suggested).
This is actually a free programme by Turton - and here is also the link for it.

https://richardturton.faculty.wvu.edu/files/d/911cff9b-a9b4-48ec-83f3-683353487bfc/capcost_2017_rev2.xlsm

Any help would be MUCH appreciated.

???

 

[iklil]

OK. So I have been checking other equipment too from the excel. I have the feeling it has to do with what has been suggested, but not totally.
I believe the problem is that it cannot differentiate the format vs the value. In other words the person who wrote the coding defined the value as a number. Then converted to a certain format containing the currency. However then additional subs uses the definition containing the currency which creates the confusion. However I cannot seem to fix it. If I fix it for one equipment - I would get it for all but... Still reaching no solution.

If anyone can help - would really appreciate it.

 

[Joe4]

Welcome to the Board iklil!

Posting to old threads (this one is 8 years old), is usually not the best approach to take, if you want to get your questions answered, for the following reasons:
1. New questions with no replies show up on the "Unanswered threads" listing that many members use to look for new questions that they can help out on. By replying to an existing thread, it will never show up on that list, and get a lot less people looking at it.
2. Many times the people who were involved in old threads are not active here anymore.

Basically, we recommend only posting back to old threads if:
1. You have a question about the existing question or solution (not introducing new information, just looking for clarification on the existing replies).
- or -
2. Your question is EXACTLY the same as the original question.

Usually, you will probably have more success posting a new thread. You can post links back to other threads (like this one) which you think may be helpful, if you like.
So I would recommend doing that.

 

[iklil]

Welcome to the Board iklil!

Posting to old threads (this one is 8 years old), is usually not the best approach to take, if you want to get your questions answered, for the following reasons:
1. New questions with no replies show up on the "Unanswered threads" listing that many members use to look for new questions that they can help out on. By replying to an existing thread, it will never show up on that list, and get a lot less people looking at it.
2. Many times the people who were involved in old threads are not active here anymore.

Basically, we recommend only posting back to old threads if:
1. You have a question about the existing question or solution (not introducing new information, just looking for clarification on the existing replies).
- or -
2. Your question is EXACTLY the same as the original question.

Usually, you will probably have more success posting a new thread. You can post links back to other threads (like this one) which you think may be helpful, if you like.
So I would recommend doing that.

Thanks for the recommendation Joe, and I was wondering why I was getting nowhere...
The problem was I found this website by finding the answer to a question which is almost the same as mine and somehow(!) it was solved for that person, yet, didnt work for me... And I thought I might be stealing more peoples time unnecessarily (stepping on your 2nd point) which I did not want to do...
I have seen the person who has responded earlier is still on site but you are right, I don't think they are aware of my question.
I will create a new one and try to put their previous response as a link - might be more effective.
Thank you - BUNCHES!

 

[Joe4]

Thank you - BUNCHES!

You are welcome.
I hope you get some responses that help you find the issue!

 

[patriciocabello]

I have exactly the same problem but in every device. Has anyone resolve it?

 

[patriciocabello]

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.

I have the exact same problem but every device. What should I need to do ? I already change the $ format but it marks the same error.