using name address in vba

[G2K]

Hi All,

I am using following code to copy certain range and insert, name the range and delete. but i want to make this code dynamic and named E30 as EC.can anybody help me to get the cell address of this cell and range(E30:E60).

below is code for your reference--


Application.EnableEvents = False
With Range("E30:E59")
If Target.Value = "" Then
On Error Resume Next
Range("MyInsertedCells").Delete xlShiftToLeft
Else
.Copy
.Offset(, 1).Resize(, Target.Value).Insert xlShiftToRight
On Error Resume Next
ThisWorkbook.Names("MyInsertedCells").Delete
On Error GoTo 0
.Offset(, 1).Resize(, Target.Value).Name = "MyInsertedCells"
Application.CutCopyMode = False
End If
End With
Application.EnableEvents = True

thanks

 

[xld]

Is this what you mean

    Application.EnableEvents = False
    With Range(Range("EC"), Range("E60"))
        If Target.Value = "" Then
            On Error Resume Next
            Range("MyInsertedCells").Delete xlShiftToLeft
        Else
            .Copy
            .Offset(, 1).Resize(, Target.Value).Insert xlShiftToRight
            On Error Resume Next
            ThisWorkbook.Names("MyInsertedCells").Delete
            On Error GoTo 0
            .Offset(, 1).Resize(, Target.Value).Name = "MyInsertedCells"
            Application.CutCopyMode = False
        End If
    End With
    Application.EnableEvents = True

 

[G2K]

Thanks a ton,

yes, this is working perfect.but everytime we need to press delete to clear the inserted cell. is it possible to set inserted range according to value entered in specific cell as if value entered is 3 it insert 3 columns and if i enter 2 it remove existing 3 columns, and insert 2 new column.

also, how can we format this range? is it possible to merge top cells of given range i.e

E5 to E20 is copied and inserted 5 times and given a name MyInsertedCells
i want to merge top 5 cell of given range(E5 to I5)


thanks again

 

[xld]

yes, this is working perfect.but everytime we need to press delete to clear the inserted cell. is it possible to set inserted range according to value entered in specific cell as if value entered is 3 it insert 3 columns and if i enter 2 it remove existing 3 columns, and insert 2 new column.

Is that two requests or is it meant to be part of the same one (if the latter, I am confused, if the former I don't understand the first bit).

alos how can we format this range? is it possible to merge top cells of given range i.e

E5 to E20 is copied and inserted 5 times and given a name MyInsertedCells
i want to merge top 5 cell of given range(E5 to I5)

Again, I am confused. Why would you want to merge them, and why E5 to E15?

 

[G2K]

it is E5 to I5, or any number of cell that is inserted, according to value entered in specific cell., say if value is 3 range would be E5 t0 G5.however, this is just for formating and i can do it manualy.

my main concern is the changing value in the target cell. each time i need to press delete button to clear the inserted range and then enter new value again where as i want the output range according to the value entered in target cell. without pressing delete button

if target value 3, three new columns will be inserted, and if i press 4 again without removing the existing value,it clear the existing range(3 Columns) and show new Range(4 columns)

Thanks