Help writing a Macro that require a selection?

meesha · Apr 4, 2015

Greetings!

I am at the very beginning stages of learning VBA, and I am a basic level excel user (I have messed with one or two formula's but nothing much).
I am working on a project, and need to know if there is a way to write a macro that requires an on-screen (with a mouse) selection of the cells you want to apply the macro to?

I would like to build a macro that completes multiple steps of a clean-up process quickly, however I need to be able to select different cells, and different columns, possibly in different workbooks to apply the macro to.

This forum is famous for its knowledgeable feedback. I appreciate any help you can give me!

Sincerely,

-Meesha

Michael M · Apr 4, 2015

Welcome to the Board
It's generally not necessary to Select cells, to work with them !
Maybe if you give us more info we could point you in the right direction.
Also, consider using the macro recorder to record your tasks, and then repost with the recorded code, and someone here may be able to help you clean / speed it up !

L. Howard · Apr 4, 2015

meesha said:
Greetings!

I am at the very beginning stages of learning VBA, and I am a basic level excel user (I have messed with one or two formula's but nothing much).
I am working on a project, and need to know if there is a way to write a macro that requires an on-screen (with a mouse) selection of the cells you want to apply the macro to?

I would like to build a macro that completes multiple steps of a clean-up process quickly, however I need to be able to select different cells, and different columns, possibly in different workbooks to apply the macro to.

This forum is famous for its knowledgeable feedback. I appreciate any help you can give me!

Sincerely,

-Meesha

Hi Meesha,

Well, here is a pretty simple copy of a selected range to Cell J5 as the "upper left" cell of the range you selected.

The two commented out will copy the selection to sheet 2 column S, starting with cell S2 and then into the first empty cell below the last copied entry.
To try these lines, comment out the first line, like this '.Copy Range("J5")

Being new to macros, you may not know you seldom ever need to SELECT cells to work with them. If you could be a bit more specific, there may be a much better way to get your "clean up" done.

Howard

Code:

Option Explicit

Sub aSelection()
With Selection

  .Copy Range("J5")
  
  '.Copy
  'Sheets("Sheet2").Range("S" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
  
End With

meesha · Apr 6, 2015

Michael M, and Howard L, thank you for the replies. Apparently I have done a poor job of communication. I will attempt to rectify the situation.
I would like to continue to learn VBA for excel as time goes on, however if I can figure out the solution to my problem now, it will help immensely until I learn more.

I should start by saying that the way I currently think may not be the way that VBA actually works, so I may be trying to do something impossible, however I would like to know.

I just recorded a macro of a list of actions that I need to apply to different columns in the same sheet, and in other workbooks.

The text starts out looking like this:

K13M328M64

K17M736M42

K18M972M72

And ends up looking like this:

$13,328.64

$17,736.42

$18,972.72

Code:

Sub Cost_Clean_Up()'
' Cost_Clean_Up Macro
'


'
    Range("A1:A3").Select
    Selection.Replace What:="K", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="M", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/100"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B3")
    Range("B1:B3").Select
    Range("B1:B3").Select
    Selection.Cut
    Range("B1:B3").Select
    Application.CutCopyMode = False
    Selection.Cut
    Range("E7").Select
    Application.CutCopyMode = False
    Selection.FillDown
    Range("B1:B3").Select
    Selection.Copy
    Range("A1:A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1:B3").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("B6").Select
End Sub

I want to be able to highlight one column of coded prices, run the macro to convert them, and then select another column of coded prices and repeat the process.

Any suggestions?

-Meesha

mikerickson · Apr 6, 2015

You can use Application.InputBox to get a range from the user. Here's a template for how to do that

Code:

Dim uiRange As Range

On Error Resume Next
Set uiRange = Application.InputBox("Select a range with the mouse", Type:=8)
On Error Resume Next

If uiRange Is Nothing Then
    MsgBox "canceled"
    Exit Sub
Else
    MsgBox "You selected " & uiRange.Address(, , , True)
End If

Michael M · Apr 6, 2015

IS it always going to be Rows 1 to 3 ?
Are the columns all on the one sheet ?
If you select a range of cells you can use this, but I believe with further info it could be more efficient

Code:

Sub Cost_Clean_Up() '
For Each c In Selection
    c.Replace What:="K", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    c.Replace What:="M", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    c.Value = c.Value / 100
    c.Style = "Currency"
Next c
End Sub

Michael M · Apr 6, 2015

And if Mikes advice is helpful, you could combine both codes

Code:

Sub Cost_Clean_Up() '
Dim uiRange As Range
Set uiRange = Application.InputBox("Select a range with the mouse", Type:=8)
On Error Resume Next
If uiRange Is Nothing Then
    MsgBox "canceled"
    Exit Sub
End If
For Each c In uiRange
    c.Replace What:="K", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    c.Replace What:="M", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows
    c.Value = c.Value / 100
    c.Style = "Currency"
Next c
End Sub

meesha · Apr 6, 2015

mikerickson said:
You can use Application.InputBox to get a range from the user. Here's a template for how to do that

Code:

Dim uiRange As Range On Error Resume Next Set uiRange = Application.InputBox("Select a range with the mouse", Type:=8) On Error Resume Next If uiRange Is Nothing Then MsgBox "canceled" Exit Sub Else MsgBox "You selected " & uiRange.Address(, , , True) End If

This seems to be the closest thing to what I am looking for. However, is there a way to reference the current selection instead of having to run the macro and then make the selection?

Michael M · Apr 6, 2015

See post #6

meesha · Apr 6, 2015

Michael M said:
IS it always going to be Rows 1 to 3 ?
Are the columns all on the one sheet ?

Thanks for another quick reply. In answer to your questions: No, it will rarely be the same rows or number of rows; and it will be used on different sheets as well.

Help writing a Macro that require a selection?

meesha

New Member

Excel Facts

Michael M

Well-known Member

L. Howard

Well-known Member

meesha

New Member

mikerickson

MrExcel MVP

Michael M

Well-known Member

Michael M

Well-known Member

meesha

New Member

Michael M

Well-known Member

meesha

New Member

Similar threads

Forum statistics

Share this page

Help writing a Macro that require a selection?

New Member

Excel Facts

Well-known Member

Well-known Member

New Member

MrExcel MVP

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock