How to make reference in VBA to a column that has a defined name

Actually what I need is that my macro, which pastes info in column SALES (column C:C) from column E:E, to always reference that column even
when new columns are inserted before C:C or after it.

You can refernce the column "Sales" by using the syntax:

Range("Sales").Whatyouwant

Hope this helps.

Ryan

I can not make it work

This is my macro:

Sub addtest()
Dim myselection As range
Set myselection = Selection
On Error Resume Next
Dim MARKUP As Name

For Each c In myselection
CELLS(1, range("MARKUP")).Value = 33
Next
End Sub

when I use CELLS(1,34).Value=33 it works. Please help. Thank you so much.

Re: I can not make it work

Marla,

If you have already named your range (Markup), then you don't need to DIM it As Name. Delete that line and it should work! Hope this helps.

Ryan

I do not know what I'm doing wrong but still does not work

Ryan, I defined the column say Z as "MARKUP". Should I define only a cell in that column?

Re: I do not know what I'm doing wrong but still does not work

Marla,

Looking back at your code, I jumped the gun. What is your code doing? It looks like it is cycling through a selection, but assigning values (33) to a different selection? What's going on? Let me know and I'll help out.

Ryan

I was trying to simplify my example to make your life easier but I think I messed it up more

Ok I gooffed I guess. I have in cells
M1=33, M2=22 M3=39.

I defined column C:C "MARKUP" and I need to enter in C1, C2 and C3 the numbers from M column. I try to make the macro to find always the MARKUP column even when new columns are inserted between
C and M or before C.
myselection would be M1:M3 but can vary.

I tried two versions after I spent hours in various forums.

Sub addtest()
Dim myselection As range
Set myselection = Selection
On Error Resume Next

For Each c In myselection
cells((c.row), range("MARKUP")).Value = c.value
Next
End Sub

or

Sub addtest()
Dim myselection As range
Set myselection = Selection
On Error Resume Next

For Each c In myselection
c.offset(0, -range("MARKUP")).Value = c.value
Next
End Sub

thanks

Looking back at your code, I jumped the gun. What is your code doing? It looks like it is cycling through a selection, but assigning values (33) to a different selection? What's going on? Let me know and I'll help out. Ryan

Re: I was trying to simplify my example to make your life easier but I think I messed it up more

ALRIGHT!!!!,

You are going to hit yourself for this one. Took me a second to figure it out but I did :-). Here is code that will work:

Sub addtest()
Dim myselection As Range
Set myselection = Selection

On Error Resume Next

For Each c In myselection
Cells((c.Row), Range("MARKUP").Column).Value = c.Value
Next
End Sub

As you can see, the only thing that you left out was to reference what column Range("Markup") was. Other then that it was fine. I hope this helps you out! Let me know.

Ryan

Thank you Ryan you were right; it works perfectly thanks a lot