Absolute vs relative coding

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.

Please Login or Register  to view this content.

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.

And this:

Please Login or Register  to view this content.

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.

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.

I received an out of memory message on Line 52, Col 1

Please Login or Register  to view this content.

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.

Thanks

Try this:

Please Login or Register  to view this content.

Like I said, I didn't test it. I just typed it out in the VBE then copy/pasted it here.

I'll post later today with your code using relative ranges.

Thanks. I appreciate it very much.

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:

Please Login or Register  to view this content.

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.

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.

Please Login or Register  to view this content.

I was wondering if C11 should be C[-11]? Also Ctrl + m does not activate the macro.

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

Please Login or Register  to view this content.

But I am not really sure because I do not know your goal for the formula.

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