Automaticaly Define Names for Cell

[dlmandalia]

Hi I wanted to define names automaticly from Row Column Head

Example

[TABLE="width: 320"]
<TBODY>[TR]
[TD][/TD]
[TD]Ahmedabad</SPAN>
[/TD]
[TD]Bombay</SPAN>
[/TD]
[TD]New York</SPAN>
[/TD]
[/TR]
[TR]
[TD]Apple</SPAN>
[/TD]
[TD="align: right"]10</SPAN>
[/TD]
[TD="align: right"]13</SPAN>
[/TD]
[TD="align: right"]16</SPAN>
[/TD]
[/TR]
[TR]
[TD]Banana</SPAN>
[/TD]
[TD="align: right"]11</SPAN>
[/TD]
[TD="align: right"]14</SPAN>
[/TD]
[TD="align: right"]17</SPAN>
[/TD]
[/TR]
[TR]
[TD]Orange</SPAN>
[/TD]
[TD="align: right"]12</SPAN>
[/TD]
[TD="align: right"]15</SPAN>
[/TD]
[TD="align: right"]18</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]



I want to automaticaly define name for 10 as AhmedabadApple, 11 as AhmedabadBanana, 14 as BombayApple so and so forth.
I don't know how to do that. Please help me on it.

I tried find out the function for it but think there is none. I am also not that good at VB. Would please give me macro by which I can do it.

Many thanks,

Dipen Mandalia

 

[Tinbendr]

Sub Test()
Dim Temp$
With Sheet3
For A = 2 To 4
    For B = 2 To 4
        'No spaces allowed in Named range.
        Temp$ = Replace(.Cells(1, B) & .Cells(A, 1), " ", "")
        ActiveWorkbook.Names.Add Temp$, .Cells(A, B)
    Next
Next
End With
End Sub

 

[dlmandalia]

This works great Thanks so much really got thing sorted out with it!!!

Cheers!! David

Sub Test()
Dim Temp$
With Sheet3
For A = 2 To 4
    For B = 2 To 4
        'No spaces allowed in Named range.
        Temp$ = Replace(.Cells(1, B) & .Cells(A, 1), " ", "")
        ActiveWorkbook.Names.Add Temp$, .Cells(A, B)
    Next
Next
End With
End Sub

 

[dlmandalia]

David,
That work like charm!! In this coding we have to manualy calculate the range and it works on that. Now I an trying to create macro in which it takes selected range's Top Columns and Selected Range's Left Row as Temp$ in Name in loop. is it been possible?


Example

[TABLE="class: cms_table"]
<TBODY>[TR]
[TD][/TD]
[TD]Ahmedabad</SPAN>[/TD]
[TD]Bombay</SPAN>[/TD]
[TD]New York</SPAN>[/TD]
[/TR]
[TR]
[TD]Apple</SPAN>[/TD]
[TD="align: right"]10</SPAN>[/TD]
[TD="align: right"]13</SPAN>[/TD]
[TD="align: right"]16</SPAN>[/TD]
[/TR]
[TR]
[TD]Banana</SPAN>[/TD]
[TD="align: right"]11</SPAN>[/TD]
[TD="align: right"]14</SPAN>[/TD]
[TD="align: right"]17</SPAN>[/TD]
[/TR]
[TR]
[TD]Orange</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[TD="align: right"]15</SPAN>[/TD]
[TD="align: right"]18</SPAN>[/TD]
[/TR]
</TBODY>[/TABLE]


Table 1

In coding we have to manually give address of Top and Left Row. I am trying to create macro where above table can be anywhere in sheet, once we select range of this 4x4 table it automaticly take First Left Raw and First Top column as names in Temp$

Many Thanks,

Sub Test()
Dim Temp$
With Sheet3
For Raw = 2 To 4
    For Col = 2 To 4
        'No spaces allowed in Named range.
        Temp$ = Replace(.Cells(Raw, 1) & .Cells(1, Col) , " ", "")
        ActiveWorkbook.Names.Add Temp$, .Cells(Raw, Col)
    Next
Next
End With
End Sub

 

[Tinbendr]

I'm not sure if I understand.

You want the Row/Column headers to have named ranges, too?

Select your table and name it. This way, the code will work wherever the table is located.

Sub Test2()
Dim Temp$
Dim iRow as Long 
Dim Col as Long
    'Select your table and name it. (Table1)
    With Sheet1.Range("Table1")

        For iRow = 1 To .Rows.Count
            For Col = 1 To .Columns.Count
                'No spaces allowed in Named range.
                Temp$ = Replace(.Cells(iRow, 1) & .Cells(1, Col), " ", "")
                If Temp$ <> "" Then
                    ActiveWorkbook.Names.Add Temp$, .Cells(iRow, Col)
                End If
            Next
        Next
    End With
End Sub

 

[dlmandalia]

Hey David,

This one works great!! Thanks so much..

sorry for bothering you again and again but I have been trying to do it without using table, as I have to use this macro so many time that it will be very difficult to define table every time and then run macro each time with new table created.I tried coding it without table but I failed to do since 2 days. Thats why I am asking you Is it been possible doing the same without using table!

I am trying to create a macro which works like this.

"Step 1 :- Select Area {Example B2:E5 or A1:D5 anything selected manually.}

Step 2:- Run Macro. (Name Defined as Row/Column Header of Selected Area)

I hope you understand it. If you don't then let me know. Please let me know is it been possible.

Many Thanks,

Dipen Mandalia

I'm not sure if I understand.

You want the Row/Column headers to have named ranges, too?

Select your table and name it. This way, the code will work wherever the table is located.

Sub Test2()
Dim Temp$
Dim iRow as Long 
Dim Col as Long
    'Select your table and name it. (Table1)
    With Sheet1.Range("Table1")

        For iRow = 1 To .Rows.Count
            For Col = 1 To .Columns.Count
                'No spaces allowed in Named range.
                Temp$ = Replace(.Cells(iRow, 1) & .Cells(1, Col), " ", "")
                If Temp$ <> "" Then
                    ActiveWorkbook.Names.Add Temp$, .Cells(iRow, Col)
                End If
            Next
        Next
    End With
End Sub

 

[Tinbendr]

Then instead of

With Sheet1.Range("Table1")

Use

With Selection

 

[dlmandalia]

Thanks you so much David,

Your code work like charm...

This how I used your code.

this code has made my work very easy.. thank you so much... God bless you..


Dipen Mandalia

Sub Name_defined()
Dim Temp$
Dim iRow As Long
Dim Col As Long
'Select your table and name it. (Table1)
    With Selection
        For iRow = 2 To .Rows.Count
            For Col = 2 To .Columns.Count
                'No spaces allowed in Named range.
                Temp$ = Replace(.Cells(iRow, 1) & .Cells(1, Col), " ", "")
                If Temp$ <> "" Then
                    ActiveWorkbook.Names.Add Temp$, .Cells(iRow, Col)
                End If
            Next
        Next
    End With
End Sub

Then instead of

With Sheet1.Range("Table1")

Use

With Selection

 

[dlmandalia]

Now Same way I want to do new thing to this macro.

This time we want to add value to cell rather then changing name of the cell. I want to collect values from DDE Connector.

The formula =WinRos|LAST!GOOG will update target cell with most recent market price for Google.

Since this formula is not an excel formula I can't reference excel cells with formula - ie =WinRos|LAST!Indirect&A3 or anything like this won't work.

So i want to create macro by which I can get formula created like bellow given table

Example of formulas that should be


[TABLE="class: cms_table_cms_table"]
<tbody>[TR]
[TD][/TD]
[TD]Bid[/TD]
[TD]Ask[/TD]
[TD]Last[/TD]
[/TR]
[TR]
[TD]GCG3[/TD]
[TD="align: right"]=WinRos|Bid!GCG3[/TD]
[TD="align: right"]=WinRos|ASK!GCG3[/TD]
[TD="align: right"]=WinRos|LAST!GCG3[/TD]
[/TR]
[TR]
[TD]GCJ3[/TD]
[TD="align: right"]=WinRos|Bid!GCJ3[/TD]
[TD="align: right"]=WinRos|ASK!GCJ3[/TD]
[TD="align: right"]=WinRos|LAST!GCJ3[/TD]
[/TR]
[TR]
[TD]GCZ3[/TD]
[TD="align: right"]=WinRos|Bid!GCZ3[/TD]
[TD="align: right"]=WinRos|ASK!GCJ3[/TD]
[TD="align: right"]=WinRos|LAST!GCZ3[/TD]
[/TR]
</tbody>[/TABLE]


I have tried doing the same by

Sub Name_defined()
Dim Temp$
Dim iRow As Long
Dim Col As Long
'Select your table and name it. (Table1)
With Selection
For iRow = 2 To .Rows.Count
For Col = 2 To .Columns.Count
'No spaces allowed in Named range.
Temp$ = Replace(.Cells(iRow, 1) & .Cells(1, Col), " ", "")
If Temp$ <> "" Then
ActiveWorkbook.FormulaR1C1 = "=WinRos|" Temp$, .Cells(iRow, Col)
End If
Next
Next
End With
End Sub

but it didnt work. I dont know how to add value to cell like that.

Please help me doing so.


Dipen Mandalia

Thanks you so much David,

Your code work like charm...

This how I used your code.

this code has made my work very easy.. thank you so much... God bless you..


Dipen Mandalia

Sub Name_defined()
Dim Temp$
Dim iRow As Long
Dim Col As Long
'Select your table and name it. (Table1)
    With Selection
        For iRow = 2 To .Rows.Count
            For Col = 2 To .Columns.Count
                'No spaces allowed in Named range.
                Temp$ = Replace(.Cells(iRow, 1) & .Cells(1, Col), " ", "")
                If Temp$ <> "" Then
                    ActiveWorkbook.Names.Add Temp$, .Cells(iRow, Col)
                End If
            Next
        Next
    End With
End Sub