CODEWITHSUNDEEP
vbareference
Reccax
Reccax

Reputation: 27

VBA Syntax for Reference of a cell instead of cell value

I want the VBA syntax for the reference of a cell instead of the value.

I know that

Workbook().Worksheet().Cell().Value returns the value of the cell.

I want to know the cell reference, something like this ='[Vba Source Test.xlsx]Source'!$B$8

I tried using:

workbook().worksheet().cell().address

but it only returns the $B$8 part.

I will be using it in a code like this.

Workbook(Master).Worksheet(Summary).range(a1).value =  
workbook(Source).Worksheet(Data).cell(2,8).address

Thank you again for whoever can help.

Upvotes: 0

Views: 8108

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You need to concatenate the object names & the range address, with the appropriate separators:

"='["Workbook(Source).Name & "]" & Worksheet(Data).Name & "'" & Cells(2,8).Address

Because what you are doing is assigning a String value to the cell's Formula, I think this should work (but I have not tested, so there may be typo):

Dim myFormula as String
myFormula = "='["Workbook(Source).Name & "]" & Worksheet(Data).Name & "'!" & Cells(2,8).Address

Workbook(Master).Worksheet(Summary).range(a1).Formula = myFormula

You may be able to omit the Workbook Name from this, I'm pretty sure that the Workbook name is automatic as long as you specify the worksheet belonging to an open workbook. If the file is not open when you insert the formula, you will have to include the workbook name.

Upvotes: 1

Related Questions