Can someone help me with this? Please look at the code in yellow below and tell me how to re-write the code so it doesn't look for absolute cells but relative cells?
![]()
Please Login or Register to view this content.
Can someone help me with this? Please look at the code in yellow below and tell me how to re-write the code so it doesn't look for absolute cells but relative cells?
![]()
Please Login or Register to view this content.
Last edited by dalewms2; 09-29-2011 at 10:18 AM. Reason: fix tags
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.
---Keep on Coding in the Free World---
Can someone help me with this? Please look at the code in red below and tell me how to re-write the code so it doesn't look for absolute cells but relative cells?
![]()
Please Login or Register to view this content.
One thing I do to make things "Relative" is to use Named Ranges. By naming a cell or range, that name is valid no matter where that cell or range is moved to. This is especially useful when using Dynamic Named Ranges (Google it).
Another method I use is to make things "relative" is to use Application.Match() to find the row and column number (assuming you can search your data labels horizontally and/or vertically)
I haven't read through your code very thoroughly, but here is one thing that really stands out.
In VBA, you do not need to select an object to work with it (usually). The above code can be more simply written (and execute faster) as:![]()
Please Login or Register to view this content.
Additionally this:![]()
Please Login or Register to view this content.
Can be better written as this:![]()
Please Login or Register to view this content.
![]()
Please Login or Register to view this content.
And this:Can easily be this:![]()
Please Login or Register to view this content.
Go through your code and remove as many "Select" statements as you can, and then I will read your code more thoroughly and try to address your original question/problem.![]()
Please Login or Register to view this content.
Thanks. I made your suggested changes. The last one gave me a Compile Error. I am so new to VBA so I don't know how to replace the select statements. I did change one based on what you have shared. I have attached the Excel file. Thanks.
Sorry about the last one. That was a typo on my part. Should have been:
![]()
Please Login or Register to view this content.
And here is your code without any Select statements. It could be much cleaner, but I wanted to show you the next step to improving your code. (Please note that I didn't test it. There are probably a couple of typos mixed in).
![]()
Please Login or Register to view this content.
Please let me know if the above code works for you and accomplishes the intended goal of the sub. If the above still works the way you want it, I will attempt to clean up the code and address your concern of relative-ness.
Last edited by Whizbang; 09-26-2011 at 09:58 AM.
I received an out of memory message on Line 52, Col 1
Also where the code indicates Row 2573 or cell K2575, those need to be relative because the number of rows will vary from time to time.![]()
Please Login or Register to view this content.
Thanks
Try this:
Like I said, I didn't test it. I just typed it out in the VBE then copy/pasted it here.![]()
Please Login or Register to view this content.
I'll post later today with your code using relative ranges.
Thanks. I appreciate it very much.
Here is a slightly improved macro. It makes the row numbers dynamic. If you want the columns to be dynamic as well, let me know. Also, I got rid of the ws variable. There is no need for this. It is assumed that if the sheet is not specified, then the active sheet is being enacted upon. If you were hopping about between workbooks or worksheets, then you would need it, but otherwise it is just a waste of characters.
![]()
Please Login or Register to view this content.
Last edited by Whizbang; 09-26-2011 at 11:40 AM.
This works great. I was just wondering is there a way to make the following code relative because the rows may vary in number. And how can I move the border that's under the sum up one row? Thank you very much
![]()
Please Login or Register to view this content.
![]()
Please Login or Register to view this content.
Sorry. My eyes just slid right past those lines, I guess.
Try:
As for moving the border at the bottom, the code I supplied should automatically reference the bottom row. If you need it somewhere else, let me know where.![]()
Please Login or Register to view this content.
Thanks so much for your help. The following code needs to be relative also. And the borderline is underneath the sum figures. If it's not too much trouble I need it above the sum figures.
![]()
Please Login or Register to view this content.
Man, this request is making me eat my words. I must've contracted the "Footinmouth" syndrome over the weekend.
A few things:
Remember when I said "In VBA, you do not need to select an object to work with it (usually)."? Well, splitting the window and freezing the panes is a case where you do actually have to select a cell, or at least scroll the window up. Maybe there is a better way, but the quick and easy way, to me, is to just select Range("A2"), as you had done in your first code post.
Additionally, when I said that the ActiveSheet is assumed and not generally needed, your code does present one of the exceptions. In the case of referencing cells and columns and rows and the like, you do not need to specify the sheet. In the case of .UsedRange and .Sort, however, you do. This is due to the fact that if things were consistent, developers would quickly become complacent. So, to spice things up, sometimes you need to specify the sheet, and sometimes you don't. *Shrugs*
As for the border and the formulas you specified in your last post... My code, above, places the lines above the sums, and the sums are dynamic. I can only assume you copied the code incorrectly or failed to remove those lines.
The subroutine should be as follows (which includes any bug fixes I have added since my last post):
![]()
Please Login or Register to view this content.
The attached workbook demonstrates using named ranges in code as a qay to keep references relative.
![]()
Please Login or Register to view this content.
It works if I do F8 over and over but Ctrl + m or Run it is not responding to. Thanks
Which one, the attached workbook, or the code I posted two posts ago? Do you get an error message, or does nothing happen at all?
The very last code you posted.
No error message. Nothing happens.
Did you just copy the code or did you open the attached? The code depends on named ranges being crerated. If you just copied the code, it will not work.
Download the attached and go to the "Formulas" tab. Then select "Named Manager". You will see a list of named ranges. These ranges contain formulas that automatically grow or shrink with the number of rows in the sheet.
I downloaded the file. I got a run time error 1004: Application not defined or object-defined error for the following code.
I was wondering if C11 should be C[-11]? Also Ctrl + m does not activate the macro.![]()
Please Login or Register to view this content.
We are so close and thanks for all your help. I was wondering if there is a way to delete the duplicate rows at the end of the macro.
Thanks so very much.
It looks like it should be
But I am not really sure because I do not know your goal for the formula.![]()
Please Login or Register to view this content.
As for it not running when you do CTRL+m, simply go to the Developer Tab, click "Macros" and then highlight the desired macro from the list and click "Options". You can set the shortcut key there.
Sorry to bother you but still getting the same error with the new code. I know we are close to this being finished. Thanks
I do not get the error.
Check your named ranges. Does "SUPPLEMENT" exist in your workbook? If so, what is the formula for the range?
Supplement does exist. Below is the formula. It's looking for the initial sheet I started with. Is there a way to make it look for the active worksheet? Thanks
='After Editing'!$J$2:INDEX('After Editing'!$J:$J,COUNTA('After Editing'!$A:$A))
Not without more work than it is worth. The named ranges example was merely to show you another method to keep your code relative, in that you do not need to hard-code a specific range, but instead allow for growth. Using named ranges allows for growth in a single worksheet and allows for "self documenting code (i.e. Range("Totals") is much more intuitive than "Range("K1:K2745")). If, however, you want to make this macro flexible enough to run on any worksheet, then Named Ranges is not the way to go.
Walk me through your process, however. Why do you have multiple sheets? Perhaps there is a way to eliminate this need.
The only I have multiple sheets is for testing purposes. I do need a macro that will run on any worksheet.
The process that is happening is that invoices are being consolidated. The invoices with $50 amounts are supplemental and added to the main invoice. This leaves a duplicate invoice that ends with $50 $0 $50 at the bottom of the data area. Thanks
So, you want any supplemental entries deleted?
Yes, whatever it takes to run on any worksheet. Thanks
I can make it work on any worksheet, it will just mean reverting back to a previous version of the code (including a few minor fixes), but my question dealt with your definition of the duplicate rows and what to do with them.
So, any row that has an invoice amount of $50 or $0 is duplicate, and so should be deleted after the formula is entered into the "SUPPLEMENT" range?
Yes, exactly. thanks
it will look like $50 in column K, $0 in Column L and $50 in column M. The $50 in column K has already been transferred to the supplement and therefore the total, hence it is a duplicate. Thanks
Any row that has the combination $50 (Column K), $0 (Column L) and $50 (Column M) should be deleted.
Whizbang if you get me back to where this macro will work on any worksheet I will figure something out to take care of the duplicate rows. Thanks very much
Here is an updated version of the code. It includes deleting the duplicate/supplemental entries.
![]()
Please Login or Register to view this content.
Thank you so much!!!! Works great!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks