Solved: VBA formula referencing cell in different worksheet

[gototcm]

I have 2 WS, sheet1 and sheet2. Sheet1 has a column of text or numeric values which are to be entered by a user. Say a1=1 and a2=2. Sheet2 is to have a formula referring to these entries, in this example, to be put into column d. For example, to get the reference to cell a1 in sheet1 I can use the following (abbreviated) VBA code

[VBA]myrange=range("a1")
Range("d1").Formula = "='dpids'!a1". [/VBA]This yields the correct formula in cell d1 (sheet2) as "=sheet1!a1 and in the cell the value is 1.

But how do I do this if I want to use an offset from cell a1. I have tried the following.

[VBA]myrange1 = Range("a1").Offset(1, 0)
Range("d2") = "='dpids'!myrange1"[/VBA]WRONG! This yields the formula =dpids!myrange1 and the entry of #Name in the cell, not what I wanted which is the value of 2.

I have attempted to use the Indirect function without any success. This has to have a clear and obvious answer but it is eluding my abilities.

Any help is very welcome after spending hours using trial and error.


[VBA]Range("d3").Formula = "='dpids'!myrange1"
Range("d4").Formula = "='dpids'!myrange2"[/VBA]

[omp001]

Hi.
is this what you want?
[vba]Sub test1()
Dim myrange1 As Range
Set myrange1 = [A1].Offset(1)
[D2] = "=dpids!" & myrange1.Address
End Sub[/vba] also
[vba]Sub test2()
Dim myrange1 As String
myrange1 = [A1].Offset(1).Address
[D2] = "=dpids!" & myrange1
End Sub[/vba]

[gototcm]

Wow, that was simple and thank you so much and that did work. Found some documentation on this "Address" property that I was ignorant of. Sometimes, the more you know, the simplest things escape you. Thanks again.