[RESOLVED] Linking cells from other sheets formula (EXCEL)

[billhuard]

Hi,

I'm getting a run time error 1004
Application-defined or object-defined error

What I'm trying to do is to link the value from another sheet.
The error is raised at: ActiveCell.Offset...... line

Sub Linker(Dis As String)
TDis = Dis
With Sheets("Total").Range("A1:A500")
    Set b = .Find(TDis)   'Find the district
    aadd = b.Address
End With
Sheets("Total").Select
ActiveSheet.Range([aadd]).Activate
'Put the formulas where it should be
ActiveCell.Offset(0, 2).Formula = "= " & TDis & " ! " & gtotactsalesw & " "
End Sub

The name of the sheet is coming by Dis who is a String
gtotactsalesw is also a String

Usually in an excel cell you link a value from another by entering the formula =sheet!cell

But don't seems to do the trick here.
Any help or hint would be appreciated.

thanks

[VBAhack]

I didn't verify, but I believe you activate sheets and select ranges, not the other way around. Also, you don't need to select/activate to change the formula. You could use:

Sheets("Total").Range([aadd]).Offset(0, 2).Formula = "= " & TDis & " ! " & gtotactsalesw & " "

P.S. why use [aadd] instead of just aadd?

[mikeyc1204]

What is the value in TDis? If the sheet name contains spaces you need to enclose it in single quotes.

Sheet1!$A$1

vs.

'Sheet 1'!$A$1

And out of curiosity, what is the purpose of the trailing space in the derived formula?

[billhuard]

VbHack: I use [aadd] because if I just use aadd it does't work at all. Also Sheets("Total").Range([aadd]).Offset(0, 2).Formula = "= " & TDis & " ! " & gtotactsalesw & " " does't seem to work.


The error seems to be on the right side of the = sign.

mickeyc1204: you're right again my friend...
TDis contains : "North S. & Outaouais"

the space between words is the problem.
And the trailing space were mistyping...

Work perfectly

Thanks again mickey1204