change last cell in column

[jj33002]

Well another question, thanks to mudraker I have this code

Please Login or Register  to view this content.

It works great for what i want to do except the cell b38 will not always be b38 it will however be the last cell in column b. any ideas?

[Bryan Hessey]

Well another question, thanks to mudraker I have this code

Please Login or Register  to view this content.

It works great for what i want to do except the cell b38 will not always be b38 it will however be the last cell in column b. any ideas?

Hi

to find the last used cell in column B try

Please Login or Register  to view this content.

and to use that would be Range("B" & iLastRow). etc

hth
---

[Paul]

Please Login or Register  to view this content.

If you update your code to what's shown above, does that work?

UPDATE: Bryan pre-copied my reply.

[jj33002]

I attatched a copy of the worksheets I have been using, for some reason when I put it in there it doesnt work, but i can take a new plain worksheet and put some dummy numbers and a formula in there and it works.
Bill test.zip

[Paul]

Can you tell me what exactly you're trying to make this code do? Are you trying to make the value from C38 on one sheet into another sheet? Just looking at your worksheet isn't making it clearer to me (maybe it's too late and my brain is asleep.)

[jj33002]

What it does is take the total in column B at the bottom of the page and at the first of the month it changes that formula into just the number value instead of a formula. That way it the total in B for let's say the month of March will not keep changing now that it is april. if the cell that gives you your total in column b didnt move then I wouldn't have this problem but sometimes it moves down a couple lets say from row 38 to 39 or 40. I hope this makes it clearer.

[Paul]

I think I found the problem..

In column B, row 54 you have the word "Total". It is making this the "iLastRow", and when the macro runs it's simply replacing "Total" with "Total". You need it to always find the last row in column B, so move "Total" to another column (C, for instance). Select all cells from B39 to B60 (or thereabouts) and click Delete just to be sure that range is empty.

Save your file and re-open it. The Workbook_Open macro should change the formula in B38 to the value, 272.96.

Let me know how that goes..

[jj33002]

Well don't I feel like an idiot know. Thanks a lot. That seemed to have fixed my problem. I will do more extensive tests tomorrow.

Thanks a lot

Josh

[Paul]

I also found another way to help, I think..

In cell B2 on sheets April and May, you have a formula pointing to cell B38 on the previous sheet. As you mentioned, the last row might not be 38 for a given month, so instead of '=March!B38', '=April!B38', etc., use this for cell B2 in April:

Please Login or Register  to view this content.

For May!B2 change the month reference to April, like so:

Please Login or Register  to view this content.

PS - If anyone reading this has better ideas to automatically change the month, by all means do share.

UPDATE: Those are array formulas.. you must press CTRL+SHIFT+ENTER after typing them, not just ENTER.

[jj33002]

All right I will try that later tonight when I get home. Thanks alot. I will let you know how it works out.

Josh

[jj33002]

I can't get that formula to work right.

Please Login or Register  to view this content.

It just gives me the value of b2. I can change the formula to b38 and it will give it to me but whenever it is in b39 it just says 0. I have been trying a few things and still no result. did I do something wrong.


Nevermind when I pushed CTRL+SHIFT+ENTER it worked. Woops.

Thanks a lot

Josh

[Paul]

After typing the formula, did you press CTRL+SHIFT+ENTER? It is required of this type of formula (array formulas).

If you're not sure, go to that cell, press F2 to activate the cell, then press CTRL+SHIFT+ENTER.

If you only press ENTER, it will return the first value in the array B2:B50, which is why you're seeing the value from B2.

[jj33002]

I really appreciate the help. And the quick responses. Maybe I will have another brain teaser for you some other time.


Josh

[Paul]

My pleasure. Glad we could help you out.