Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > NZ Computing > Excel help with macro please

Reply
Thread Tools

Excel help with macro please

 
 
Brian Tozer
Guest
Posts: n/a
 
      12-18-2003
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?



 
Reply With Quote
 
 
 
 
dOTdASH
Guest
Posts: n/a
 
      12-18-2003
"Brian Tozer" <(E-Mail Removed)> wrote in message
news:brsr49$cl6$(E-Mail Removed)...
> 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


 
Reply With Quote
 
 
 
 
Joe Black
Guest
Posts: n/a
 
      12-18-2003
> 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


 
Reply With Quote
 
Brian Tozer
Guest
Posts: n/a
 
      12-18-2003
Thanks Joe very much for your comprehensive reply.
Far better than I have been able to get on the specialist Excel news groups.
Brian.


 
Reply With Quote
 
Mr Scebe
Guest
Posts: n/a
 
      12-19-2003

"Brian Tozer" <(E-Mail Removed)> wrote in message
news:brsr49$cl6$(E-Mail Removed)...

> > 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"



 
Reply With Quote
 
Brian Tozer
Guest
Posts: n/a
 
      12-19-2003
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.



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Excel reports ::::Excel 2003 Migration To Excel 2007 =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?= ASP .Net 15 10-24-2007 01:34 PM
#define macro to enclose an older macro with strings Dead RAM C++ 20 07-14-2004 10:58 AM
please help... ...me learn C++ please please please :) KK C++ 2 10-14-2003 02:08 PM
Macro help (I posted on Excel site, too) Sherry Computer Support 1 10-08-2003 10:12 PM
macro name from macro? D Senthil Kumar C Programming 1 09-21-2003 07:02 PM



Advertisments