Excel help with macro please

Discussion in 'NZ Computing' started by Brian Tozer, Dec 18, 2003.

  1. Brian Tozer

    Brian Tozer Guest

    I am struggling with the basics, but hanging in there.
    Could someone please give the few extra instructions on how to implement
    this macro.
    At this point in my investigation I am not concerned as to whether the macro
    actually works correctly, but just want to know exactly how to implement it
    in practical terms as a typical example of implementing a macro.
    Some questions to indicate my confusion:-

    Do I enter this macro into a Worksheet Module, or a Regular Code Module, or
    somewhere/somehow else?
    Do I invoke/run/call/? it just once or not at all or what?
    How do I do this?
    Do I need to add anything to the macro to define its area of activity?
    Is the quoted macro exactly and totally what I enter or is something extra
    implicit in the text?

    I hope this helps someone to be able to help me with this typical example
    that will hopefully clarify a far wider field of interest.

    Thanks
    Brian Tozer


    > Sub FixRangeValues()
    > Selection.Value = Selection.Value
    > End Sub


    >> I have a worksheet where all the cells in a column are formatted as
    >> text but there are numbers entered in the column.
    >> Changing the format of the column, or even any individual cell, to
    >> number, does not remove the triangle in the cell top left corner.
    >> What should I be doing to fix the situation?
    Brian Tozer, Dec 18, 2003
    #1
    1. Advertising

  2. Brian Tozer

    dOTdASH Guest

    "Brian Tozer" <> wrote in message
    news:brsr49$cl6$...
    > I am struggling with the basics, but hanging in there.
    > Could someone please give the few extra instructions on how to implement
    > this macro.
    > At this point in my investigation I am not concerned as to whether the

    macro
    > actually works correctly, but just want to know exactly how to implement

    it
    > in practical terms as a typical example of implementing a macro.
    > Some questions to indicate my confusion:-
    >
    > Do I enter this macro into a Worksheet Module, or a Regular Code Module,

    or
    > somewhere/somehow else?
    > Do I invoke/run/call/? it just once or not at all or what?
    > How do I do this?
    > Do I need to add anything to the macro to define its area of activity?
    > Is the quoted macro exactly and totally what I enter or is something extra
    > implicit in the text?
    >
    > I hope this helps someone to be able to help me with this typical example
    > that will hopefully clarify a far wider field of interest.
    >
    > Thanks
    > Brian Tozer
    >
    >
    > > Sub FixRangeValues()
    > > Selection.Value = Selection.Value
    > > End Sub

    >
    > >> I have a worksheet where all the cells in a column are formatted as
    > >> text but there are numbers entered in the column.
    > >> Changing the format of the column, or even any individual cell, to
    > >> number, does not remove the triangle in the cell top left corner.
    > >> What should I be doing to fix the situation?

    >
    >


    I find it easier to use the macro record function, do what I want the macro
    to do and let Excel write the macro for me and then edit it to add for/next
    loops etc. You get to the recorder via Tools/Macro/Record New Macro and the
    macro code via Tools/Macro/Macros
    dOTdASH, Dec 18, 2003
    #2
    1. Advertising

  3. Brian Tozer

    Joe Black Guest

    > Do I enter this macro into a Worksheet Module, or a Regular Code Module,
    or
    > somewhere/somehow else?

    Worksheet module.
    With the workbook open, use menu "Tools > Macro > Visual Basic Editor".
    From the Visual Basic editor menu choose "Insert > Module".
    Type the macro in the module.

    > Do I invoke/run/call/? it just once or not at all or what?
    > How do I do this?

    The easiest way to invoke it is (from the workbook), use menu "Tools > Macro
    > Macros" which will bring up a list of macros in open workbooks. (The

    workbook with the macro needs to be open for it to be in the list). Select
    the macro from the list and click the "Run" button.

    > Do I need to add anything to the macro to define its area of activity?

    As written, the macro will work on the currently selected cell(s) of the
    currently selected worksheet of the currently active workbook.
    If you want it to work on a specific workbook, worksheet and/or cell(s) you
    can specify those in the code.
    e.g ActiveWorkbook.Worksheets("Sheet1").Range("B2")

    > Is the quoted macro exactly and totally what I enter or is something extra
    > implicit in the text?
    > > Sub FixRangeValues()
    > > Selection.Value = Selection.Value
    > > End Sub

    Yes you can enter it exactly as above.
    I would recommend the following addition:

    Sub FixRangeValues()
    Selection.ClearFormats
    Selection.Value = Selection.Value
    End Sub

    The best way I have found to learn Excel macros is to record some and look
    at the code that is created. ("Tools > Macro > Record New Macro" and perform
    some actions e.g select cells, type something, add a formula).

    Regards - Joe
    Joe Black, Dec 18, 2003
    #3
  4. Brian Tozer

    Brian Tozer Guest

    Thanks Joe very much for your comprehensive reply.
    Far better than I have been able to get on the specialist Excel news groups.
    Brian.
    Brian Tozer, Dec 18, 2003
    #4
  5. Brian Tozer

    Mr Scebe Guest

    "Brian Tozer" <> wrote in message
    news:brsr49$cl6$...

    > > Sub FixRangeValues()
    > > Selection.Value = Selection.Value
    > > End Sub

    >
    > >> I have a worksheet where all the cells in a column are formatted as
    > >> text but there are numbers entered in the column.
    > >> Changing the format of the column, or even any individual cell, to
    > >> number, does not remove the triangle in the cell top left corner.
    > >> What should I be doing to fix the situation?


    Hmmm, i presume that the cells are in the wrong format? I don't quite
    understand the triangle, but you could use something like:

    Sub ChangeType()
    With Sheets(i).Range("") ' insert your range here *change the
    "i" to whatever
    ' the number of the sheet
    is ie: first sheet = 1
    .NumberFormat = "0"
    End With
    End Sub

    This will set all the cells in the range to see the contents as numbers,
    rather than text.

    Open up the Visual Basic Editor, then select Insert -> Module and paste the
    code above into it. You should also note that you will need to set the
    worksheet and the range that you want to change.

    Try clicking on Clippy and typing in "Numberformat" this will give you some
    examples of other settings that you can have for the range

    HTH.

    --
    Mr Scebe
    Losers always whine about their 'best'.
    Winners go home and **** the prom queen".
    ~Sean Connery in "The Rock"
    Mr Scebe, Dec 19, 2003
    #5
  6. Brian Tozer

    Brian Tozer Guest

    Thanks Mr Scebe for your trouble.
    The following suggestion seems to be a good solution in my situation:-

    Select a blank cell
    Copy
    Select the range with data in it
    Edit | Paste Special | Add |OK

    Brian Tozer

    >>>> I have a worksheet where all the cells in a column are formatted as
    >>>> text but there are numbers entered in the column.
    >>>> Changing the format of the column, or even any individual cell, to
    >>>> number, does not remove the triangle in the cell top left corner.
    >>>> What should I be doing to fix the situation?

    >
    > Hmmm, i presume that the cells are in the wrong format? I don't quite
    > understand the triangle, but you could use something like:
    >
    > Sub ChangeType()
    > With Sheets(i).Range("") ' insert your range here *change
    > the "i" to whatever
    > ' the number of the
    > sheet is ie: first sheet = 1
    > .NumberFormat = "0"
    > End With
    > End Sub
    >
    > This will set all the cells in the range to see the contents as
    > numbers, rather than text.
    >
    > Open up the Visual Basic Editor, then select Insert -> Module and
    > paste the code above into it. You should also note that you will need
    > to set the worksheet and the range that you want to change.
    >
    > Try clicking on Clippy and typing in "Numberformat" this will give
    > you some examples of other settings that you can have for the range
    >
    > HTH.
    Brian Tozer, Dec 19, 2003
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sherry

    Macro help (I posted on Excel site, too)

    Sherry, Oct 8, 2003, in forum: Computer Support
    Replies:
    1
    Views:
    406
  2. J. Cod
    Replies:
    0
    Views:
    428
    J. Cod
    Sep 29, 2004
  3. Replies:
    2
    Views:
    482
    Charles
    Sep 28, 2005
  4. John Ortt

    Dedicated Macro or Normal Macro?

    John Ortt, Nov 21, 2005, in forum: Digital Photography
    Replies:
    5
    Views:
    799
    John Ortt
    Nov 22, 2005
  5. Canon S80 Macro button vs Digital Macro?

    , Feb 26, 2006, in forum: Digital Photography
    Replies:
    4
    Views:
    1,366
    Bill P
    Mar 2, 2006
Loading...

Share This Page