* Urgent VBA formula help

[storer]

Hi guys

I am doing a programme at work and I am struggling to finish the macro.

I am running a macro in a cell (j8), but I want to offset to the cell to the right and insert the following formula:

=J8-((three worksheets to the left)J8)

The formula i have is below

Function addValue(number) 'Add value into the value box
x = ActiveSheet.Name 'active worksheet denotated as x
If (number >= 0) Then
Sheets(x).Activate
Worksheets(x).Cells(8, 10) = number
addValue = True
Else
addValue = False
End If
Range("K8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-sheets(sheetcount-2)RC[-1]"
End Function

If anyone can help it would be massively appreciated.

Kind regards

Storer

 

[jim may]

Worksheets(Activesheet.Index - 3).Cells(8, 11).formula = Activesheet.Cells(8, 10).formula

 

[JLGWhiz]

From: ActiveCell.FormulaR1C1 = "=RC[-1]-sheets(sheetcount-2)RC[-1]"



To: ActiveCell.Formula = "=j8-'Sheet" & Sheets.Count - 2 & "'!j8"

 

[storer]

From: ActiveCell.FormulaR1C1 = "=RC[-1]-sheets(sheetcount-2)RC[-1]"



To: ActiveCell.Formula = "=j8-'Sheet" & Sheets.Count - 2 & "'!j8"

JLGWhiz, this is the best of the two posted. I can get it to put everything in the speech marks in when i don't have the = sign. When it runs with the = sign it comes up with an error (runtime error 1004) application defined or object defined error.

Its really close i just need this last step.

AS

 

[Trebor76]

Hi storer,

Try this:

If InStr(Sheets(ActiveSheet.Index - 3).Name, " ") > 0 Then
        ActiveCell.Formula = "=J8-'" & Sheets(ActiveSheet.Index - 3).Name & "'!J8"
    Else
        ActiveCell.Formula = "=J8-" & Sheets(ActiveSheet.Index - 3).Name & "!J8"
    End If

HTH

Robert

 

[JLGWhiz]

JLGWhiz, this is the best of the two posted. I can get it to put everything in the speech marks in when i don't have the = sign. When it runs with the = sign it comes up with an error (runtime error 1004) application defined or object defined error.

Its really close i just need this last step.

AS

I suspect that if you typed the line of code in instead of copy and paste, that there might be a typo involving the ( "" ) marks and the ( ' ) mark. It has to end up as =j8 - 'Sheet#'!j8 so the placement of the double quotes and the apotrophes are critical.

Or you could just do it with code:

Range("K8") = Range("J8").Value - Sheets(Sheets.Count-2).Range("J8").Value

 

[storer]

Thank you guys, yet again this wonderful forum have saved my bacon! ray:



AS