Programming Help

Web Development



What's Here?

  • Members: 150,201
  • Replies: 507,770
  • Topics: 80,060
  • Snippets: 2,668
  • Tutorials: 707
  • Total Online: 2,018
  • Members: 79
  • Guests: 1,939

Welcome to Dream.In.Code
Become a VB Expert!

Join 150,201 VB Programmers for FREE! Get instant access to thousands of VB experts, tutorials, code snippets, and more! There are 2,018 people online right now. Registration is fast and FREE... Join Now!




[HELP]VBA Trim Macro

 
 Reply to this topicStart new topic

[HELP]VBA Trim Macro
lazenca
1 Oct, 2008 - 06:23 PM
Post #1

New D.I.C Head
*

Joined: 1 Oct, 2008
Posts: 3
CODE
Sub TrimCol()
Dim r As Range
   Set r = Intersect(ActiveCell.EntireColumn, ActiveSheet.UsedRange)
   r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",TRIM(" & r.Address & "),""""))")
End Sub

IPB Image

What this code does is basically to trim whichever cell or column I select. It's working on one of my PC, but when I Copy and Paste over the code to another PC, it shows this "function not defined error". What is happening exactly and any solutions for this?

Thanks!
User is offlineProfile CardPM
+Quote Post

thava
RE: [HELP]VBA Trim Macro
2 Oct, 2008 - 09:02 PM
Post #2

D.I.C Regular
Group Icon

Joined: 17 Apr, 2007
Posts: 486



Thanked: 20 times
Dream Kudos: 50
My Contributions
one small thing (not sure) did you add a reference for office execl in your project

i think you create a seperate project for this why don't u try in the same project
(i am not sure Why your code produce an error it's perfectly work for me)
User is offlineProfile CardPM
+Quote Post

akhileshbc
RE: [HELP]VBA Trim Macro
3 Oct, 2008 - 04:38 AM
Post #3

D.I.C Head
Group Icon

Joined: 26 Sep, 2008
Posts: 177



Thanked: 3 times
Dream Kudos: 50
My Contributions
@thava, It is not a vb6 program (I think so). Its a vbscript-macro, that he had used in his Excel
@lazenca, currently I haven't installed the office package in my system. So, I am not able to check it.... smile.gif
User is offlineProfile CardPM
+Quote Post

thava
RE: [HELP]VBA Trim Macro
3 Oct, 2008 - 06:30 AM
Post #4

D.I.C Regular
Group Icon

Joined: 17 Apr, 2007
Posts: 486



Thanked: 20 times
Dream Kudos: 50
My Contributions
i know its vba i run this code in my execel sheet it works perfectly
User is offlineProfile CardPM
+Quote Post

akhileshbc
RE: [HELP]VBA Trim Macro
3 Oct, 2008 - 06:49 AM
Post #5

D.I.C Head
Group Icon

Joined: 26 Sep, 2008
Posts: 177



Thanked: 3 times
Dream Kudos: 50
My Contributions
@thava, have u added any reference. I am asking this because I havent used vbscript & macros. If possible, tell him, how u had tested it & whether u had made any changes or not. smile.gif
User is offlineProfile CardPM
+Quote Post

thava
RE: [HELP]VBA Trim Macro
3 Oct, 2008 - 07:03 AM
Post #6

D.I.C Regular
Group Icon

Joined: 17 Apr, 2007
Posts: 486



Thanked: 20 times
Dream Kudos: 50
My Contributions
no i didn't make any changes
ok lets explain you just
add a macro in excel and place the coding there and run
there is no errors
since the macro runs in excel it gets the reference default
if not we should set the reference,

i didn't add more than one project for an excel file
that's why i suggest that way make one project


This post has been edited by thava: 3 Oct, 2008 - 07:04 AM
User is offlineProfile CardPM
+Quote Post

akhileshbc
RE: [HELP]VBA Trim Macro
3 Oct, 2008 - 07:15 AM
Post #7

D.I.C Head
Group Icon

Joined: 26 Sep, 2008
Posts: 177



Thanked: 3 times
Dream Kudos: 50
My Contributions
Thanx 4 explaining it smile.gif
User is offlineProfile CardPM
+Quote Post

lazenca
RE: [HELP]VBA Trim Macro
5 Oct, 2008 - 05:46 PM
Post #8

New D.I.C Head
*

Joined: 1 Oct, 2008
Posts: 3
Ok, in fact it seemed that I found a solution which I couldn't give an explanation to why its like that.

It seems that in this line,
CODE
Set r = Intersect(ActiveCell.EntireColumn, ActiveSheet.UsedRange)


If the "Set" is not dark blue in color, it wont work which explains the reason why "r" is highlighted in the screenshot below which I believe is somehow that "r" is not yet given a value or something like that.

However, I still don't know what causes this. Thus help is still needed.

PS: Its a copy and paste code from emails

This post has been edited by lazenca: 5 Oct, 2008 - 05:57 PM
User is offlineProfile CardPM
+Quote Post

thava
RE: [HELP]VBA Trim Macro
5 Oct, 2008 - 05:50 PM
Post #9

D.I.C Regular
Group Icon

Joined: 17 Apr, 2007
Posts: 486



Thanked: 20 times
Dream Kudos: 50
My Contributions
i think when the cursor is not in the active col (focus not in current sheet)
if you run the macto it can produce the error but i am not sure ???

try the option explicit

This post has been edited by thava: 5 Oct, 2008 - 06:14 PM
User is offlineProfile CardPM
+Quote Post

lazenca
RE: [HELP]VBA Trim Macro
5 Oct, 2008 - 05:57 PM
Post #10

New D.I.C Head
*

Joined: 1 Oct, 2008
Posts: 3
I made an edit to my previous post, please take a look at it.. thanks
User is offlineProfile CardPM
+Quote Post

thava
RE: [HELP]VBA Trim Macro
5 Oct, 2008 - 06:20 PM
Post #11

D.I.C Regular
Group Icon

Joined: 17 Apr, 2007
Posts: 486



Thanked: 20 times
Dream Kudos: 50
My Contributions
see my previous post
and also see the following link

http://msdn.microsoft.com/en-us/library/y9341s4f(VS.80).aspx
User is offlineProfile CardPM
+Quote Post

Fast ReplyReply to this topicStart new topic
Time is now: 1/9/09 05:02AM
Invision Power Board v2.1.7 © 2009  IPS, Inc.
Licensed to: MediaGroup1

Be Social

Dream.In.Code RSS Feed Dream.In.Code LinkedIn Group Follow Us On Twitter

Live VB Help!

VB Tutorials

Reference Sheets

VB Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month