Reference named range into a cell formula

Chopsaki

New Member
Joined
Mar 23, 2019
Messages
4
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,323
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@Chopsaki, going by your description you have the cells the wrong way around...


Code:
    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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Or do you mean something like
Code:
   Dim i As Long
   i = 1
   Do While i < 101
      Cells(i, 2).Formula = "=sum(" & Cells(i, 1).Value & ")"
      i = i + 1
   Loop
 

Chopsaki

New Member
Joined
Mar 23, 2019
Messages
4

ADVERTISEMENT

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

New Member
Joined
Mar 23, 2019
Messages
4
Or do you mean something like
Code:
   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

New Member
Joined
Mar 23, 2019
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,902
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top