VBA Code To Return Formula To A Cell

[Rupert Bennett]

Good Afternoon,
I have the following bit of code which gets the correct values from each tab in the workbook and puts that value in the correct cell. However, instead of this value e.g. 123,456 I would like to return a formula that says e.g. =Sheet2!C10, or =Sheet2!C50 as the case may be. In short, instead of putting the value on the Summary page, I would like a formula that references a cell on the respective page. Here is the code I have at the moment

Code:
Dim lCount As Long
Dim lSheet As Long
Dim LastRowValue As Long

ThisWorkbook.Activate
Sheets("Summary").Select
lCount = Worksheets.Count

For lSheet = 1 To lCount
Range("A3".Offset(lSheet).Value = Worksheets(lSheet).Name
LastRowValue = Worksheets(lSheet).Cells(Rows.Count,3).End(xlUp).Value
Range("A3").Offset(lSheet,1).Value = LastRowValue
ActiveSheet.Hyperlinks.Add anchor:=Range("A3").Offset(lSheet,0), Address:="",SubAddress:= " ' " & Worksheets(lSheet).Name & " ' !A3"

Next lSheet

End Sub


Your help with this would be greatly appreciated.

Thanks,

Rupert

 

[Max1616]

Hello Rupert,

Try this:

Dim lCount As Long
Dim lSheet As Long
Dim LastRowValue As Long

ThisWorkbook.Activate
Sheets("Summary").Select
lCount = Worksheets.Count

For lSheet = 1 To lCount
Range("A3").Offset(lSheet).Value = Worksheets(lSheet).Name
Range("A3").Offset(lSheet, 1).Value = "='" & Worksheets(lSheet).Name & "'!" & Worksheets(lSheet).Cells(Rows.Count, 3).End(xlUp).Address
ActiveSheet.Hyperlinks.Add anchor:=Range("A3").Offset(lSheet, 0), Address:="", SubAddress:=" ' " & Worksheets(lSheet).Name & " ' !A3"

Next lSheet

This bit of code here says puts in 3 different components into the cell

Range("A3").Offset(lSheet, 1).Value = "='" & Worksheets(lSheet).Name  & "'!" & Worksheets(lSheet).Cells(Rows.Count,  3).End(xlUp).Address

1) The = sign to start the formula
2) 'SheetName'! to indicate the sheet for the cell reference
3) Cell Address to indicate the referenced location

The result looks like this : ='SheetName'!A1

I hope this helps!

 

[Rupert Bennett]

Perfect!!. Exactly what I am looking for. I made several attempts, but could not get the syntax right. Thank you very much and I appreciate your assistance.

Rupert