How to select current region's activecell's column

[gauravkrgautam]

Hi team,
I have created a simple macro which is changing some integers into string from referenced column.
Coding for the same is

Sub integer_to_name()
Dim Int_rng As Range, name_rng As Range, cell As Variant
Set Int_rng = ActiveCell.CurrentRegion
MsgBox Int_rng.Address
Set name_rng = Sheet1.Range("n1:n" & Range("m65000").End(xlUp).Row)
For Each cell In Int_rng
cell.Value = name_rng.Cells(cell.Value, 1)
Next
End Sub

But when I tried to change my_simple_range into more dynamic_range there I am getting error please help me in same
New coding is

Sub integer_to_name1()
Dim Int_rng As Range, name_rng As Range, cell As Variant
Set Int_rng = ActiveCell.CurrentRegion.Columns(ActiveCell.Column - ActiveCell.CurrentRegion.Column + 1)
MsgBox Int_rng.Address
Set name_rng = Sheet4.Range("M2:M" & Range("m65000").End(xlUp).Row)
For Each cell In Int_rng
cell.Value = name_rng.Cells(cell.Value, 1)
Next
End Sub

I want to update only activecell's column of current region
File is been attached with sample data. Please help me.

 

[Luke M]

I think you want the Intersect. In which case, change this line:

Set Int_rng = ActiveCell.CurrentRegion.Columns(ActiveCell.Column - ActiveCell.CurrentRegion.Column + 1)

to this:

Set Int_rng = Intersect(ActiveCell.EntireColumn,ActiveCell.CurrentRegion)

 

[gauravkrgautam]

Thanks Luke you are genius but I answerless to my self ,
why it was not working with my coding

Set Range_by_GauravKrGautam = ActiveCell.CurrentRegion.Columns(ActiveCell.Column - ActiveCell.CurrentRegion.Column + 1)

Set Range_by_Chandoo_Ninja = Intersect(ActiveCell.EntireColumn, ActiveCell.CurrentRegion)

msgbox showing same output
does vb does not dare to come in front of ninja's sword.


Regards,
Gaurav Kr Gautam