Thread: r1c1 formulae for linked cell

i need to link a cell to another cell in a document, it would take ages to do by hand so i wrtoe some formulae!

Sub makelinks()
Dim r, s, t, p As Integer
Dim clet, bobby, frobby, tobby, country, cvalue As String


p = 1
r = 4
Do While Len(Range("A" & r).Formula) > 0
country = Cells(r, 1).Value




For a = 7 To 87


If a = 7 Then clet = "c"
If a = 8 Then clet = "d"
If a = 9 Then clet = "e"
If a = 10 Then clet = "f"
If a = 11 Then clet = "g"
If a = 12 Then clet = "h"
If a = 13 Then clet = "i"
If a = 14 Then clet = "j"
If a = 15 Then clet = "k"
If a = 16 Then clet = "l"
If a = 17 Then clet = "m"
If a = 18 Then clet = "n"
If a = 19 Then clet = "o"
If a = 20 Then clet = "p"
If a = 21 Then clet = "q"
If a = 22 Then clet = "r"
If a = 23 Then clet = "s"
If a = 24 Then clet = "t"
If a = 25 Then clet = "u"
If a = 26 Then clet = "v"
If a = 27 Then clet = "w"
If a = 28 Then clet = "x"
If a = 29 Then clet = "y"
If a = 30 Then clet = "Z"
If a = 31 Then clet = "aa"
If a = 32 Then clet = "ab"
If a = 33 Then clet = "ac"
If a = 34 Then clet = "ad"
If a = 35 Then clet = "ae"
If a = 36 Then clet = "af"
If a = 37 Then clet = "ag"
If a = 38 Then clet = "ah"
If a = 39 Then clet = "ai"
If a = 40 Then clet = "aj"
If a = 41 Then clet = "ak"
If a = 42 Then clet = "al"
If a = 43 Then clet = "am"
If a = 44 Then clet = "an"
If a = 45 Then clet = "ao"
If a = 46 Then clet = "ap"
If a = 47 Then clet = "aq"
If a = 48 Then clet = "ar"
If a = 49 Then clet = "as"
If a = 50 Then clet = "at"
If a = 51 Then clet = "au"
If a = 52 Then clet = "av"
If a = 53 Then clet = "aw"
If a = 54 Then clet = "ax"
If a = 55 Then clet = "ay"
If a = 56 Then clet = "aZ"
If a = 57 Then clet = "ba"
If a = 58 Then clet = "bb"
If a = 59 Then clet = "bc"
If a = 60 Then clet = "bd"
If a = 61 Then clet = "be"
If a = 62 Then clet = "bf"
If a = 63 Then clet = "bg"
If a = 64 Then clet = "bh"
If a = 65 Then clet = "bi"
If a = 66 Then clet = "bk"
If a = 67 Then clet = "bl"
If a = 68 Then clet = "bm"
If a = 69 Then clet = "bn"
If a = 70 Then clet = "bo"
If a = 71 Then clet = "bp"
If a = 72 Then clet = "bq"
If a = 73 Then clet = "br"
If a = 74 Then clet = "bs"
If a = 75 Then clet = "bt"
If a = 76 Then clet = "bu"
If a = 77 Then clet = "bv"
If a = 78 Then clet = "bw"
If a = 79 Then clet = "bx"
If a = 80 Then clet = "by"
If a = 81 Then clet = "bz"
If a = 82 Then clet = "ca"
If a = 83 Then clet = "cb"
If a = 84 Then clet = "**"
If a = 85 Then clet = "ce"
If a = 86 Then clet = "cf"
If a = 87 Then clet = "cg"








If p < 16 Then cvalue = c
If p = 16 Then cvalue = cothers
If p = 17 Then cvalue = Total

Cells(r, a).Select
ActiveCell.FormulaR1C1 = "='M:\Television and Broadband\DATA_2\[" & country & "_cable.xls]" & cvalue & "'!" & clet & "$47"




p = p + 1
If p = 18 Then p = 1

Next a

Loop

End Sub

unfortunately this produces "application-defined or object defined error"

now when i run it without the = sign as just paste as text not formulae i get a nice looking string of something like:

M:\Television and Broadband\DATA_2\[Austria_cable.xls]'!be$47


which looks about right to me

what s wrong with my code?

thanks

Jonathan

Hi,

there was not supposed to be a gap in the column but is didn't know how else to do it

also how would i go about passing an A1 formula to a cell?


thanks

Jonathan

hey thanks,

that looks much more concise then my code

but i still get the error on the line

Cells(r, a - 4).FormulaR1C1 = "='M:\Television and Broadband\DATA_2\[" & country & "_cable.xls]" & cvalue & "'!R47C" & a - 4

oooops

yeah they are part of a filename

i should have "" around them!

saying that.. when i correct this i still get the same error!

hi,

yeah i tried using a formula that i know to work instead of the generated one and that refuses to play ball as well

hmm why i try that i get a "method not valid without suitable object" error!