VBA - Reference variable in formula

[jpar1983]

I'm trying to reference a variable that I have declared in a formula using VBA. I have a variable that I've declared to capture the name of a specific worksheet in Excel. I'm trying to reference that variable in a cell formula, but I don't think I've got the syntax right somehow. I've looked at a lot of forums and other websites, and I thought I'd matched the syntax correctly, but the code is still erroring out. Can someone tell me what I'm doing wrong?

Dim OrigSheet As String
OrigSheet = ActiveSheet.Name
Range("A1").Select
ActiveCell.Formula = "=Row()" & OrigSheet

 

[Joe4]

This does not look like it would be creating a valid Excel formula:

ActiveCell.Formula = "=Row()" & OrigSheet

Can you post an example of what you want the finished formula in cell A1 to look like?

 

[jpar1983]

This does not look like it would be creating a valid Excel formula:

ActiveCell.Formula = "=Row()" & OrigSheet

Can you post an example of what you want the finished formula in cell A1 to look like?

Thanks for the quick reply! I want the final result to be the row number and the sheet name, so cell A1 would be "1 Sheet1". I'm creating a reference number of sorts for a really long report that takes multiple sheets.

 

[Joe4]

Note that you don't need VBA to create the formula. You can capture the sheet name, as described here: Excel formula: Get sheet name only | Exceljet

So then you could just use the formula:

=ROW() & " " & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)