Reference named range into a cell formula

[Chopsaki]

Hi,

I column "A", I have a list of named ranges. In another column, I want to paste into the cell the named range in that row
I have 100 rows, and use a simple loop to pass through the rows. However I cannot get the content of the reference cell into the formula of the other?

Should I be using a copy/paste command instead? - Can't get that to work either..

Dim I as integer

I = 1

Do While I < 101
​

Cells(i,1).value = Cells(I,2).value
​

I=I+1
​

Loop
​

End Sub


Thanks in advance!

 

[DanteAmor]

Do you only want to copy column A in B?
Or you want to get something from column A and put it in B

 

[MARK858]

@Chopsaki, going by your description you have the cells the wrong way around...

    Dim I As Long
    I = 1
    Do While I < 101
        Cells(I, 2).Value = Cells(I, 1).Value
        I = I + 1
     Loop

Although if all you are doing is copying the whole range you could do that without looping.

 

[Fluff]

Or do you mean something like

   Dim i As Long
   i = 1
   Do While i < 101
      Cells(i, 2).Formula = "=sum(" & Cells(i, 1).Value & ")"
      i = i + 1
   Loop

 

[Chopsaki]

Hi, I want to get the value of a cell in column A and insert it into column B as a formula;

Column A value "_named_cell_1" becomes formula in column B "=_named_cell_1"

 

[Chopsaki]

Or do you mean something like

   Dim i As Long
   i = 1
   Do While i < 101
      Cells(i, 2).Formula = "=sum(" & Cells(i, 1).Value & ")"
      i = i + 1
   Loop

This code fails in error on line 3? But I think you are correct in quoting the ".formula =".
It's working out how to reference the value in the reference cell to become the formula in the other..

 

[Chopsaki]

Hi All,

Found the answer Irequired - below is the script that can reference the value of one cell(containing the named cell reference), and use it as a formula reference inanother cell.

Thanks to those thatanswered!

Sub MacroUsers4()


Dim i As Long
i = 1
Do While i < 100
Cells(i, 11).Formula = "="& Cells(i, 15).Value
i = i + 1
Loop

End Sub

 

[DanteAmor]

You're welcome.