VB Macro Error

[PETEHO]

I have a VB script that I did not write and I confess to know very little about VB.

I am trying to amend the following script with the one below but Ikeep getting a 1004 run time error

If PensionRowNum <> 0 Then
Range("J" & PensionRowNum).Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(SUM(Permanent_Staff!AO16:AO500)/V" & BasicPayRowNum & "*J" & BasicPayRowNum & ")," & NullString & ",(SUM(Permanent_Staff!AO16:AO500)/V" & BasicPayRowNum & "*J" & BasicPayRowNum & "))"
ActiveCell.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
Range("K" & PensionRowNum).Select


My attempted change as follows(first row only included)



If PensionRowNum <> 0 Then
Range("J" & PensionRowNum).Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(SUMIF(Permanent_Staff!D16500,""FS"",Permanent_Staff!AO16:AO500)/V5*J5)," & NullString & ",SUMIF(Permanent_Staff!D16500,""FS"",Permanent_Staff!AO16:AO500)/V5*J5))"
ActiveCell.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False

 

[xlbo]

change:
"=IF(ISERROR(SUMIF(Permanent_Staff!D16500,""FS"",Permanent_Staff!AO16:AO500)/V5*J5)," & NullString & ",SUMIF(Permanent_Staff!D16500,""FS"",Permanent_Staff!AO16:AO500)/V5*J5))"

to:
"=IF(ISERROR(SUMIF(Permanent_Staff!D16500,""FS"",Permanent_Staff!AO16:AO500)/V5*J5)," & NullString & ",SUMIF(Permanent_Staff!D16500,""FS"",Permanent_Staff!AO16:AO500)/V5*J5)"

just need to remove 1 bracket from the end


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

It is likely that the worksheet is protected or there is an error in formula string. In the latter case try to build the string first and see how does it look like.

combo

 

[PHV]

Furthermore use the Formula property instead of FormulaR1C1 as you use the A1 notation.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PugnaxX]

If it turns out it is a worksheet protection issue, you could record a macro while you unprotect and protect the worksheet. Then just place them respectively, at the beginning and end of the macro code.

Unprotect Code

<Your Code Here>

Protect Code

-PugnaxX

 

[xlbo]

Really don't think it is a protection issue - the formula itself had an extra bracket which causes a 1004 error whether protection is on or not

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PETEHO]

Thanks Guys All sorted. Removed the bracket and all was fine. Had my head spinning and just could think logically after a while

Cheers

Pete