Hello. Whenever there is 0 in B3 AND there is a value greater than 0 in C3, I would like to adjust the following data one column to the left. C3:K3, N3:V3, Y3:AG3, AI3:AR3. All of the other data will not adjust.
Spreadsheet attached BUT it is just one row. In the real data I have 16,000 rows. Thank you!!
Are you wanting EACH row to be evaluated individually? or if B3 = and C3 < 0 then shift everything, the ENTIRE dataset to the right?
This evaluate and will shift each row individually:
macro help With Code.xlsm
Code with comments so you can understand what I am doing and why:
Please Login or Register to view this content.
Last edited by mikeTRON; 09-02-2014 at 08:43 PM.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
This does that:
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Here's my attempt:
Regards,Please Login or Register to view this content.
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
@MikeTron: Yes, each row should be evaluated individually. Thanks everyone, I'll try these out.
Last edited by ammartino44; 09-03-2014 at 12:55 PM.
@ MikeTron. Your setup is a little different than mine. For example, in columns M:V, there would be 0's in columns M and data in columns N and O. The code works fine though. Thank you. Another question: When I run your data, it eliminates data from K, how could I make this all 0's? The point is to push the data back one year (given the criteria) but then this would make year 10, "0", because there is no data in that year. Does that make sense?
Also, why do you clear contents in column K?
What is cutcopymode?
Thanks for your help!!
Correction to my code for putting 0 in year 10.
Please Login or Register to view this content.
@ JBeaucaire. Could you add notes for your code? There were some things I didn't quite understand. Thanks for your help.
Please Login or Register to view this content.
@jbeaucaire. Beautiful, thanks. Just one more question: When you enter the "with" code, why do you set everything to ".value"?
@trebor76. Thanks for your help. Would you mind writing notes with your code?
A couple of question: 1) Why do you use a "const", is that necessary? 2) Why do you look for the last row in a different manner than jbeaucaire? Which way is better?
The "." connects the commands to follow to the original WITH parent. Instead of having to identify parents over and over, then issue commands:
...you can set a parent object once... makes editing easier:Please Login or Register to view this content.
Please Login or Register to view this content.
Trebor's LR construct is fundamentally better as it looks for the "last used row" in several columns.
Me, I prefer to select one key most important column, invariably column A, and just look UP that one column.
Hi ammartino44,
Here's my code commented:
HTHPlease Login or Register to view this content.
Robert
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks