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

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
 
D

Don Guillett

try vba to find the last column for row 1
x=cells(1,columns.count).end(xltoleft).column
msgbox x
 
J

jctlcdc

: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"
 
D

Don Guillett

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
 
J

jctlcdc

Right, Thanks for the reply. I'll keep trying to play around with i
and see what I can come up with. Thanks again
 
J

jctlcdc

Right, Thanks for the reply. I'll keep trying to play around with it
and see what I can come up with. Thanks again.
 
J

jctlcdc

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.
 
D

Dave Peterson

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.
 
J

jctlcdc

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
 
D

Dave Peterson

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.
 
J

jctlcdc

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/
 
D

Dave Peterson

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()).
 
J

jctlcdc

That sounds good. I'm on the road right now but will be back tomorow.
I'll try it then. Thanks, again
 
J

jctlcdc

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
 
D

Dave Peterson

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))
 
Top