gototcm
12-17-2011, 09:14 AM
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
myrange=range("a1")
Range("d1").Formula = "='dpids'!a1". 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.
myrange1 = Range("a1").Offset(1, 0)
Range("d2") = "='dpids'!myrange1"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.
Range("d3").Formula = "='dpids'!myrange1"
Range("d4").Formula = "='dpids'!myrange2"
myrange=range("a1")
Range("d1").Formula = "='dpids'!a1". 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.
myrange1 = Range("a1").Offset(1, 0)
Range("d2") = "='dpids'!myrange1"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.
Range("d3").Formula = "='dpids'!myrange1"
Range("d4").Formula = "='dpids'!myrange2"