Find Last Filled Cell, Negate it, determine another cell and subtotal a column

**kerl** · 07-30-2007, 01:59 PM

Friends,

Here's what I'm trying to do. I need to find the last value in column K, negate its value (multiple by -1).

Now after negating it, if the value is

>0, corresponding cell in column I should be 10
<0, corresponding cell in column I should be 20

And then I want to do a subtotal for column K and pop up a msg stating the subtotal. I don't want to store the subtotal anywhere on the sheet, just needs to be in the msg box.

Any help would be great appreciated !!!

**Leith Ross** · 07-30-2007, 03:36 PM

Hello Kerl,

Here is a macro to do what you want. The subtotal starts at K2.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**kerl** · 07-30-2007, 03:59 PM

Thanks Leith Ross! The code works flawlessly

BTW, if I'm using the following code for the user to locate a file on the PC, how do I give the command to close the file?

Please Login or Register  to view this content.

I tried doing the following but it won't work.

Please Login or Register  to view this content.

**Leith Ross** · 07-30-2007, 04:12 PM

Hello Kerl,

Simply close the Workbook...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**kerl** · 07-30-2007, 04:27 PM

even that doesn't work and gives me a runtime error (snapshot attached). And it highlights the line

Please Login or Register  to view this content.

**Leith Ross** · 07-30-2007, 06:50 PM

Hello Kerl,

Since you're getting a "subscript out of range" error, you need to check the value of your variable. I am guessing sFileName is not formed properly or possibly empty.

Sincerely,
Leith Ross

Find Last Filled Cell, Negate it, determine another cell and subtotal a column

LinkBack

Thread Tools

Rate This Thread

Display

Last Cell, Negate, Subtotal / Passing File Reference in User Input

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions