Problem using range.address in a formula

[lynnsong986]

I need to link a cell on one worksheet to a cell on another. the last line has syntax errors in it and I don't know how to fix it.
the code finds "what" in worksheet 218500Amort. range C12:C500. I need worksheets("218500").range("b22") to have a formula rather than directly = ra. can someone please help?

Dim ra As Range 
    Dim what 
what = Format(Worksheets("218500").Range("L1"), "m/d/yyyy") 
    Set ra = Worksheets("218500Amort.").Range("C12:C500").Find(what:=what, LookIn:=xlFormulas, LookAt _ 
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
        False, SearchFormat:=False).Offset(0, 7) 

Worksheets("218500").Range("B22").Formula = "='218500Amort.'!& ra.Address"

 

[PCL]

Change

[COLOR=#0000BB][FONT=monospace]Worksheets[/FONT][/COLOR][COLOR=#007700][FONT=monospace]([/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"218500"[/FONT][/COLOR][COLOR=#007700][FONT=monospace]).[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]Range[/FONT][/COLOR][COLOR=#007700][FONT=monospace]([/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"B22"[/FONT][/COLOR][COLOR=#007700][FONT=monospace]).[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]Formula [/FONT][/COLOR][COLOR=#007700][FONT=monospace]= [/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"='218500Amort.'!& ra.Address"[/FONT][/COLOR]

to

[COLOR=#0000BB][FONT=monospace]Worksheets[/FONT][/COLOR][COLOR=#007700][FONT=monospace]([/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"218500"[/FONT][/COLOR][COLOR=#007700][FONT=monospace]).[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]Range[/FONT][/COLOR][COLOR=#007700][FONT=monospace]([/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"B22"[/FONT][/COLOR][COLOR=#007700][FONT=monospace]).[/FONT][/COLOR][COLOR=#0000BB][FONT=monospace]Formula [/FONT][/COLOR][COLOR=#007700][FONT=monospace]= [/FONT][/COLOR][COLOR=#DD0000][FONT=monospace]"='218500Amort'! & ra.Address"

[/FONT][/COLOR]

Watch the dot and the space !!!

 

[lynnsong986]

cross link:

https://www.excelforum.com/excel-programming-vba-macros/1293853-problem-using-range-address-in-a-formula.html#post5214850

 

[jolivanes]

Closing double quotes are in the wrong place. Should be

Worksheets("218500").Range("B22").Formula = "='218500Amort.'!"& ra.Address