VBA?Macro Newbie Question//Last field in a column with a value.

  • Thread starter Thread starter jctlcdc
  • Start date Start date
J

jctlcdc

I am a newbie to programming Excel. I Have a workbook which has a
average about 40 worksheets. I keep individual attendance and payment
for my Alcohol and Drug Treatment program on these worksheets. Th
individual worksheets are linked to a master roster worksheet in
different workbook.
My question for today is how can I link the last payment in a column i
an individual worksheet to the master roster so I can tell at a glanc
when a person made their last payment? Also, how could I link th
appropriate cells to the master without doing it manually every time
enter a new client?

Thanks

johnn
 
try vba to find the last column for row 1
x=cells(1,columns.count).end(xltoleft).column
msgbox x
 
:confused: Thanks for you reply but like I said I'm a real newbie.
don't understand what you mean in your reply. Where would I put that?
For Example if the payment column in my individual sheet is Column "J
and I put a column in my master sheet that would be, say, "AL" for th
last payment date. What kind of formula or function would go in th
Master sheet in column "AL"
 
This is a macro to find the last column (by number) in row 1. copy/paste
into a macro module. I guess the easiest way for you would be to right click
on the sheet tab>view code>copy/paste>SAVE>use alt f8 to execute or assign
to a button.
The rest of your macro would be a lot more involved. It sounds like you need
professional help for this project.

Sub lastcol()
x = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox x
End Sub
 
Right, Thanks for the reply. I'll keep trying to play around with i
and see what I can come up with. Thanks again
 
Right, Thanks for the reply. I'll keep trying to play around with it
and see what I can come up with. Thanks again.
 
Hi Don,
I tried that macro and it worked but it isn't exactly what I wanted. I
wanted to be able to link the last payment date and amount from an
individual sheet to the master roster. Thanks anyway.
 
So you have a worksheet with a column that's filled with dates. The bottom most
date is the last date paid.

You can use formulas like these from a post by Aladin Akyurek:

========

If F is numeric,

=LOOKUP(9.99999999999999E+307,F:F)
will fetch the last value in F.

=MATCH(9.99999999999999E+307,F:F)
will return the position of the last value in F.

If F is of 'text' type, replace the big number in the above formulas with
REPT("z",255).

=====
Make sure you format the cell as a date.
 
Dave,
The column I am needing to find the last entry in is formatted a
currency. The date is in a different column.
I would like to find both the date and amount (2 columns) and the
link those two to a different worksheet in a different workbook.
have all of the other links made, I just don't don't how to get it t
find and automatically link the date and amount of the last payment.
Thanks for your reply and interest
 
I put the amount in column F and the date in column G (adjust it to match).

To get the last amount in the column, I used the formula by Aladin Akyurek:

=LOOKUP(9.99999999999999E+307,Sheet1!F:F)

and to get the last date, I used the other formula with =index():

=INDEX(Sheet1!G:G,MATCH(9.99999999999999E+307,Sheet1!F:F))

(both of those formulas were in different sheets of the same workbook. Yours
would look more like:

=LOOKUP(9.99999999999999E+307,[book3.xls]Sheet1!F:F)
and
=INDEX([book3.xls]Sheet1!G:G,
MATCH(9.99999999999999E+307,[book3.xls]Sheet1!F:F))
(all one cell)

Build your formula with your "book3.xls" open. Then when you have it correct,
close "book3.xls" and your formulas will adjust again to include the drive/path.
 
Okay, that works, sort of. The problem is that my actual dates an
payments don't start until row 17 and I have a lot of stuff above tha
is just client info like name, dob, admitted, etc. Is there anyway t
do it without changing all of that? I mean, just have it start th
lookup and match on row 17. What I get now for date is the client
first name which is B1 and #NA for amount.
the actual Dates start at B7 and the Amounts at I7.

Lst Pmt Dt Lst Pmt Amt
Sharron #N/
 
Do you have any data in that range (row 17 to 65536)?

If you do, then this should have worked ok.

If you don't, you could add:

=if(count(f17:f9999)=0,"",originalformulahere)

This checks for a number in F17:F9999 (adjust the range if you need more rows).

but you could adjust the range to look in the formulas, too.

=LOOKUP(9.99999999999999E+307,Sheet1!F17:F9999)
=INDEX(Sheet1!G17:G9999,MATCH(9.99999999999999E+307,Sheet1!F17:F9999))

But this adjustment won't help if you don't have data in there--you need
something like the first (count()).
 
That sounds good. I'm on the road right now but will be back tomorow.
I'll try it then. Thanks, again
 
Dave,
The formulas you gave me worked after a bit of adjustment. My nex
question would be - How can I copy the formulas to the next shee
without needing to re-enter the sheet name each time? Thanks again
you saved me boo-coos of work
 
If these formulas:
=LOOKUP(9.99999999999999E+307,Sheet1!F17:F9999)
=INDEX(Sheet1!G17:G9999,MATCH(9.99999999999999E+307,Sheet1!F17:F9999))

Are in cells in sheet1, then remove the sheet references. Then when you copy
them, they'll point at whatever sheet that you paste them into.

=LOOKUP(9.99999999999999E+307,F17:F9999)
=INDEX(G17:G9999,MATCH(9.99999999999999E+307,F17:F9999))
 
Back
Top