Assign values to cells

[ChristineJ]

Column N has numerical values, such as N1 is 10, N2 is 15, etc. Column O has text values that are cell references, such as O1 is B7, O2 is B9, etc.

The number of rows may vary, but for every value in column N there will be a corresponding cell reference in column O.

Is there VBA code that can assign the value in column N to the cell referenced in column O? Thanks.

 

[mumps]

You can do this without VBA. Put his formula in cell N1 and copy it down: =INDIRECT(O1)

 

[Fluff]

My take on this

Sub ChristineJ()
   Dim Cl As Range
   
   For Each Cl In Range("O1", Range("O" & Rows.Count).End(xlUp))
      If Not Cl.Value = "" Then Range(Cl.Value) = Cl.Offset(, -1).Value
   Next Cl
End Sub

 

[ChristineJ]

Fluff - Yes, this is what I am looking for. However, it failed at "Range(Cl.Value) = Cl.Offset(, 1).Value". The message was Method 'Range' of 'Object _Global' Failed.

Thanks so much for the help.

 

[Fluff]

What was the value of Cl.Value when it fails?

 

[ChristineJ]

Completely my fault. There were table headings in cells N1 and O1.

I deleted the table and just put 66 in N1, B7 in O1, 55 in N2, and B9 in O2. It is not generating an error any more, but it is not placing the 66 and 55 in cells B7 and B9, respectively.

Is it possible to leave the table headings and start the code in row 2?

Thanks!

 

[Fluff]

To ignore the header use

Sub ChristineJ()
   Dim Cl As Range
   
   For Each Cl In Range("O2", Range("O" & Rows.Count).End(xlUp))
      If Not Cl.Value = "" Then Range(Cl.Value) = Cl.Offset(, -1).Value
   Next Cl
End Sub

Not sure why it was not copying the values
This is what I get

+Fluff New.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O
1
2											101	D10
3											abc	H5
4											123	G11
5					abc
6
7
8
9
10	101
11				123
Sheet2

 

[ChristineJ]

Works PERFECTLY now! Awesome. Thanks for sticking with me on this!

 

[Fluff]

You're welcome & thanks for the feedback.