Problem using range.address in a formula


Board Regular
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"


Well-known Member
[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]

[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"

Watch the dot and the space !!!
Last edited:


Well-known Member
Closing double quotes are in the wrong place. Should be
Worksheets("218500").Range("B22").Formula = "='218500Amort.'!"& ra.Address

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...