PDA

View Full Version : [SOLVED] How to insert a Reference in a sheet



GlazedIZ
02-10-2005, 07:29 PM
This is gonna show my ignorance, but I'm banging my head...
I'm populating a worksheet ("Scorecard")with data from another worksheet, but I'd like it to create a Reference to the data instead of the value.

I thought I was on the right track with the lines below,


wSheetstr = "='" & wSheet.Name & "'!"
Worksheets("Scorecard").Cells(ScorecardRow, 1).Formula = wSheetstr & "A8"

This works, but the problem is that (in another line), I'd like to use variables (R1C1 format) to locate where the Referenced cell is located.

In other words, the following line populates the scorecard with the correct data...


Worksheets("Scorecard").Cells(ScorecardRow, 3) = _
wSheet.Cells(Ycounter + 2, Xcounter + 5)

.... But Id like it to populate it with a Reference to the data instead.
(& preferably have the Reference in A1 format, but as long as works I dont care)
Any ideas? - I'll bet it's so simple.

Jacob Hilderbrand
02-10-2005, 07:36 PM
When you build the range string you can use A1 or R1C1 references.

Variable Row y A1 Style

wSheetstr = "='" & wSheet.Name & "'!"
Worksheets("Scorecard").Cells(ScorecardRow, 1).Formula = _
wSheetstr & "A" & y

Variable Row y and Column x A1 Style

wSheetstr = "='" & wSheet.Name & "'!"
Worksheets("Scorecard").Cells(ScorecardRow, 1).Formula = _
wSheetstr & Cells(y,x).Address

Variable Row y and Column x R1C1 Style

wSheetstr = "='" & wSheet.Name & "'!"
Worksheets("Scorecard").Cells(ScorecardRow, 1).Formula = _
wSheetstr & "R" & y & "C" & x

GlazedIZ
02-10-2005, 07:46 PM
Cells(y,x).Address - ahhh

Thank you DRJ, you ARE the Jedi Master.

Jacob Hilderbrand
02-10-2005, 08:23 PM
You're Welcome :beerchug:

Take Care