Run Time Error 1004: Application or Object Defined Error

[Guest]

Error shows up in second code.
I've tried all kinds of ways of stating but end up with this error every time.


Option Explicit
Private Sub REmove_Net_Pricing_Info_Click()
' SelectButton Macro

Sheets("Options").Range("OptionsPgPricing").ClearContents
Sheets("Pricing").Range("PricePgPricing").ClearContents
Sheets("Contract").Activate
Range("B1").Select
End Sub

Private Sub Replace_Deleted_Click()

' >>>> Run Time Error 1004: Application Defined or Object Defined Error
Sheets("Pricing").Range("C127").Select
ActiveCell.FormulaR1C1 = "CUSTOMER SPECIAL CONTRACT PRICE"

Sheets("Pricing").Range("C128").Select
ActiveCell.FormulaR1C1 = "=Round(G128,0)"

' Replace Link on Options Sheet
Sheets("Options").Range("H6").Select
ActiveCell.FormulaR1C1 = "=Pricing!$C$128"

' Place curson on Contract, Cell B1
Sheets("Contract").Range("B1").Select
End Sub

 

[Guest]

If you intend to select a range on a sheet then you first need to select the
sheet something like this...

Sheets("Pricing").Select
Range("C127").Select
ActiveCell.FormulaR1C1 = "CUSTOMER SPECIAL CONTRACT PRICE"

or you could avoid the selects all together like this...

Sheets("Pricing").Range("C127").Value = "CUSTOMER SPECIAL CONTRACT PRICE"

 

[Nick Hodge]

2 things I would do

1) Make sure the TakeFocusOnClick property of the button(s) is set to false
2) Don't select and then work with activecell as you will not be able to
select cells on a non-activesheet, so...

Becomes...

Sheets("Pricing").Range("C127").Value = "CUSTOMER SPECIAL CONTRACT PRICE"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk

 

[Guest]

Gentlemen: Thanks much. I used the 'non-select' examples - No problems.

Questions/Comments:
1: Jim: I had tried your first suggestion, with no success.
It would work for statement 1 and 2, then error on statement 3.

2: "Before selecting a range, make sure you've ACTIVATED (emphasise mine)
the ranges worksheet" (J.Walk Excel VBA Programming for Dummies).
So, changed the first line to ACTIVATE instead of Select.
Still the same error problem.
Is there a beginners level explanation for this?

ALSO:
As I understand it:
Activate: The equivalent of selecting the tab for that sheet.
Select: Do something to that (range on) that sheet, even though it
is not the current active (opened) Sheet.
Am I close?
Is there a good (Excel) dictionary available somewhere on the internet that
would
be suitable for 'dummies' like me?

Thanks again. I get a wealth of information and knowledge from this
newsgroup.
This keeps me keep on trying.

 

[Guest]

Try this...
Sheets("Pricing").Select
Range("C127").Select
ActiveCell.Value = "CUSTOMER SPECIAL CONTRACT PRICE"