Hi Im trying to select a range with a macro...

I would like to select the range from the active cell and X rows down.
How can I do this?

My code:

Myrows = Selection.rows.count
MST = Activecell.address

Range(& ActiveCell & " : " & Myrows )

However this gives me, i.e. B3:53

How can I get the Column letter before Myrows?


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=480983

Hello Ctech,

Use ActiveCell.Offset(<rows, <cols)

Myrows = Selection.rows.count

'Zero means stay in the same Row or Column
MST = ActiveCell.Offset(Myrows, 0).Address

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48098

Hello Ctech,

Use ActiveCell.Offset(<rows, <cols)

Myrows = Selection.rows.count

'Zero means stay in the same Row or Column
MST = ActiveCell.Offset(Myrows, 0).Address

Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=48098

This is the code I've written now..

However it still does not work.


' Mark formulas and Copy them

MST1 = ActiveCell.Address

MST = ActiveCell.Offset(0, 4).Address

Range(MST1 & ":" & MST).Copy


--
Ctech
------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...o&userid=27745
View this thread: http://www.excelforum.com/showthread...hreadid=480983

This is my whole macro so far...


Private Sub cmd2_Click()
Dim Mrange
Dim Mrows As Long
Dim Mcolumns As Long
Dim Mcell
Dim McolumnC
Dim MrowC
Dim MST
Dim MST1


Dim McolumnC2
Dim MrowC2


'Picks up the range from the form

Mrange = ChangeToDateFormat.RefEdit1

Range(Mrange).Select

Mcell = ActiveCell.Address

' Counts the Rows and Columns of the selection

Mrows = Selection.Rows.Count
Mcolumns = Selection.Columns.Count

' Adds Columns to work with

Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

' Adds the forumals in the added columns
Range(Mcell).Select
McolumnC = Range(Mcell).Column
MrowC = Range(Mcell).Row


ActiveCell.FormulaR1C1 = "=RC[1]&""/""&RC[2]&""/""&RC[3]"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[3],2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MID(RC[2],5,2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[1],4)"

' Mark formulas and Copy them
MST1 = ActiveCell.Address
MST = ActiveCell.Offset(0, 4).Address

Range("" & MST1 & ":" & MST).Copy

'Zero means stay in the same Row or Column

MST = ActiveCell.Offset(myRows, 4).Address

Range("" & MST1 & ":" & MST).Paste

' Copy and paste special values, Result

MST = ActiveCell.Offset(Mrows, 0).Address

Range("" & MST1 & ":" & MST).Copy
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False

' Delete the old and unneeded columns

MST1 = ActiveCell.Offset(0, 1).Address
MST = ActiveCell.Offset(Mrows, 4).Address

Range("" & MST1 & ":" & MST).Select

Selection.Delete Shift:=xlToLeft
End Su

--
Ctec
-----------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=48098

I found the solution(s) to my problem:


Here is my final code:



Private Sub cmd1_Click()
Unload ChangeToDateFormat
End Sub

Private Sub cmd2_Click()
Dim Mrange
Dim Mrows As Long
Dim Mcolumns As Long
Dim Mcell
Dim McolumnC
Dim MrowC
Dim MST
Dim MST1


Dim McolumnC2
Dim MrowC2


'Picks up the range from the form

Mrange = ChangeToDateFormat.RefEdit1

Range(Mrange).Select

Mcell = ActiveCell.Address

' Counts the Rows and Columns of the selection

Mrows = Selection.Rows.Count
Mcolumns = Selection.Columns.Count

' Adds Columns to work with

Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

' Adds the forumals in the added columns
Range(Mcell).Select
McolumnC = Range(Mcell).Column
MrowC = Range(Mcell).Row

ActiveCell.FormulaR1C1 = "=value(RC[1])"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[1]&""/""&RC[2]&""/""&RC[3]"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[3],2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MID(RC[2],5,2)"

ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[1],4)"

' Mark formulas and Copy them
ActiveCell.Offset(0, -4).Select
MST1 = ActiveCell.Address
MST = ActiveCell.Offset(0, 4).Address

Range("" & MST1 & ":" & MST).Select
Range(MST).Activate
Selection.Copy

'Zero means stay in the same Row or Column
Range(MST1).Select
Mrows = Mrows - 1

MST = ActiveCell.Offset(Mrows, 4).Address

Range("" & MST1 & ":" & MST).Select
ActiveSheet.Paste

' Copy and paste special values, Result

MST = ActiveCell.Offset(Mrows, 0).Address

Range("" & MST1 & ":" & MST).Copy
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone
SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False

' Delete the old and unneeded columns

MST1 = ActiveCell.Offset(0, 1).Address
MST = ActiveCell.Offset(Mrows, 5).Address

Range("" & MST1 & ":" & MST).Select

Selection.Delete Shift:=xlToLeft


' Change it to Date Format
MST1 = Range(MST1).Offset(0, -1).Address
MST = ActiveCell.Offset(Mrows, 0).Address
Range("" & MST1 & ":" & MST).Select

Selection.NumberFormat = "d-mmm-yy"

' Mark first cell in range

Range(MST1).Select

Unload ChangeToDateFormat
End Su

--
Ctec
-----------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=48098