What I need it to do is look in a large range for and cell that equals "CR", then change the cell to the left of it to a negative and remove the "CR".
I have a code that I was working for a day. But now when I run the macro, it no longer does anything. Could it be a setting that I am overlooking, or is there a more reliable way to write this code?
Please Login or Register to view this content.
I am sorry my friend but your code is no good.
1. If you are looking at a large range the last thing you want to do is to use a loop.
2.Will return the last row in column A.Please Login or Register to view this content.
Will return the last row in column Y.Please Login or Register to view this content.
What makes you sure that Column Y is your longest column?
Perhaps you should useI will rewrite your code for you.Please Login or Register to view this content.
This is more complicated than your code but hundreds of times faster.
Mainly because it uses the find function rather than a loop.
Please Login or Register to view this content.
Last edited by mehmetcik; 01-14-2016 at 01:39 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
You said the cell to the left, but Offset(0,1) is the cell to the right. Offset(0,-1) would be the cell to the left. Not sure which you want, adjust the code below accordingly.
Please Login or Register to view this content.
I'm interested in starting a career working with VBA, if anyone knows of any opportunities!
Thank you very much for helping me!Originally Posted by walruseggman
You said the cell to the left, but Offset(0,1) is the cell to the right. Offset(0,-1) would be the cell to the left. Not sure which you want, adjust the code below accordingly.
Please Login or Register to view this content.
I just testing this code and it worked, but it does give me an error on the "Loop..." line once the code is done.
The error message says "Run-time error '91': Object variable or with Block variable not set."
walruseggmans code would work if you inserted the two lines
andPlease Login or Register to view this content.
The reason being that you are Deleting CR, eventually there will be no CR to find giving you an error.Please Login or Register to view this content.
you should also remove all reference to c.address for the same reason.
Turning off screen updates would make it even faster.
Please Login or Register to view this content.
Last edited by mehmetcik; 01-14-2016 at 01:49 PM.
@mehmetcik: Good point, wasn't thinking about that.
@pas: You'll need to also remove And c.Address <> FirstAdd from mehmetcik's modified code.
This code worked great!! Thank you guys very much for helping mewalruseggmans code would work if you inserted the two lines
andPlease Login or Register to view this content.
The reason being that you are Deleting CR, eventually there will be no CR to find giving you an error.Please Login or Register to view this content.
you should also remove all reference to c.address for the same reason.
Turning off screen updates would make it even faster.
Please Login or Register to view this content.
I had one more question about this code. I saved this macro in a master file that opens at startup that is hidden so that I can use this macro in all my workbooks.
When I run the macro in another workbook it work, but it seems to continue to loop. It locks up excel until I hit the ESC key.
But when I run this code in the workbook that it is saved in, it does not lock up excel and works just fine.
Do you know why this could be?
Thank you
I cannot see any reason for that.
I pasted the last code into my Peersonal workbook, also a hidden book and ran the macro with no issues.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks