Active Cell not first Cell

[bromy2004]

Hi Everyone,

I have a macro that mimics pressing F2 & Enter to reformat the cells.

Sub F2_Enter()
Dim NUM As Integer
Dim c As Range

On Error Resume Next
    NUM = Application.InputBox(Prompt:="Repeat how many times?", Title:="Choose", Default:=Selection.Count, Type:=1)
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row + NUM - 1, _
    ActiveCell.Column)).Select

For Each c In Selection
    c.FormulaR1C1 = c.Value
Next

End Sub

it helps with converting numbers to Text and back.
however i'm getting a problem when i select from a higher row that the Lowest.
i.e. i click and drag from A10 to A5
Range A5:A10
but the active cell is still A10 instead of A5.

how can this be changed?

the Range can be anywhere from 5 cells to 10000 cells

 

[bromy2004]

Spark of inspiration.
for anyone else who has the same problem add this code

Cells(Selection.Row, Selection.Column).Activate

 

[bromy2004]

Final Macro for anyone who wants to know

Sub F2_Enter()
Dim NUM As Integer
Dim c As Range
Dim d As String
Dim calc As String
Dim CurrentRange
Dim CurrentCell

'Performance
calc = Application.Calculation
Application.Calculation = xlManual
Application.ScreenUpdating = False

'Current Selection
CurrentRange = Application.Selection.Address
CurrentCell = Application.ActiveCell.Address

'Macro
On Error Resume Next
If Selection.Areas.Count = 1 Then
    NUM = Application.InputBox(Prompt:="Repeat how many times?", Title:="Choose", Default:=Selection.Count, Type:=1)
    Cells(Selection.Row, Selection.Column).Activate
    Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row + NUM - 1, _
    ActiveCell.Column)).Select

    For Each c In Selection
        d = c.Value
        c.FormulaR1C1 = ""
        c.Value = d
    Next
Else
MsgBox "To stop confusion, only select one range"
End If

'select origional Data
Application.Range(CurrentRange).Select
Application.Range(CurrentCell).Activate

Application.Calculation = calc
Application.ScreenUpdating = True

End Sub

i think its pretty self explanatory
Any tips??? let me know.


Bromy

 

[pgc01]

Hi Bromy

Another option:

Selection(1).Activate