Challenge for the Excel VBA Guru's

[MNBridge]

I'm trying to create a function that would look up the last value inputed in a column.

See example below.

Any help is appreciated
Thanks


Spacing didn't work out very well but I hope it's understandable

98 37 16 71 98
77 96 88 48 80
16 15 37 78 69
6 12 79 69
30 44 62 98
48 4
90 68
18

Trying to create a function that looks up the last input and enters as it's value

The desired results would be
30 90 62 78 18

[MNBridge]

I'm thing if I could figure out how to use the cell the function is in as a reference (which I can't).

Then I could use the Offset Property to keep going up the column until it finds a value

Public Function LastInput()
'

'

'
y = 0
ActiveCell = Selection
For x = 1 To 100
y = ActiveCell.Offset(-x, 0)
If y <> 0 Then LastInput = y: End
Next x
End Function

[Moderator1]

I don't have an answer for the function, but I did edit your first post to fix the spacing. :P Don't use tabs with "code"; use spaces.

[cubedbee]

For stuff like this I use a formula in the form of

Offset(A1, Count(Range)-1, 0)

where A1 is the first cell of the column with data entered, and Range is the entire area of the column where data can be entered.

This formula works only if no rows are skipped when inputting data into the column and all rows after the end of the input are left blank. Count will count anything with numbers, but not text or blank space.

[shluffer]

Function lastval(y)

Dim a As Integer

a = Range(y, Selection.End(xlDown)).Count
lastval = y.Offset(a - 1, 0)


End Function

only works if no skipped cells in the columb. The function looks like "=lastval(c3)" where c3 is the first cell in the columb of numbers.

[MNBridge]

Thanks all -- There are skipped cells.

[whisper]

Thanks all -- There are skipped cells.

Try this then:

Function lastvalue(a_cell As Excel.Range) As Variant
Dim x_cell As Excel.Range, theresult As Variant
Set x_cell = Worksheets(a_cell.Worksheet.Name).Cells(65536, a_cell.Column)

Let theresult = x_cell.End(xlUp).Value

lastvalue = theresult

End Function

I set x_cell equal to the last row in Excel in the column that the a_cell in the formula is in. Then, it moves up to the 1st filled in cell and returns the value. If your table doesn't go all the way to the bottom of the sheet, you may want to make a simple modification to the program.

[Old Actuary]

For column A, assuming the values start at A1, use an array formula and enter in A101:

Index(A1.A100, max(if(a1.a100<>"",row(a1.a100),0)),1)

use {cntrl}{shift}{enter} to make it an array formula

[Brad Gile]

Thanks all -- There are skipped cells.

This seems to work:

Public Function LastVal(ByVal R As Range) As Variant
Dim J As Long
Dim N As Long

For J = 1 To R.Rows.Count
If Not IsEmpty(R(J)) Then N = J
Next J

LastVal = R(N)


End Function

Brad
"Karate ni sente nashi. There is no first strike in karate."-Gichin Funakoshi
"Neither is there a second!"-Brad Gile

[Bama Gambler]

Function LastValue(WhereToLook As Range)

Dim LastCell As Variant
Dim c As Variant

For Each c In WhereToLook
If c Like "*" Then
LastCell = c
End If
Next c

LastValue = LastCell
End Function

[MNBridge]

Thanks all

I think I got it to work. Now I just need to find out if the cells are indeed blank or have zero values.

I think they may have zero values

[Dr T Non-Fan]

Fix the data source.
That's because I'm not a guru.

[Old Actuary]

You don't need to check. All the formulas that tried to deal with blank cells will work if there are no blank cells - i.e., if you have 0's.

[mchung]

Dear all,

Excel VBA Programming essential for actuarial trainees?

Michael

[Dr T Non-Fan]

No.
It is valuable, but as you can tell from the relatively few people who know anything about VBA, it is not essential for an actuary.
However, I'll wager that the employers of these actuaries are glad for the extra bit of computing power these folks provide.

[SamChevre]

Dear all,

Excel VBA Programming essential for actuarial trainees?

MichaelI work in a department of about 25--of those people, 2 can program reasonably well in VBA. I would say the in-demand skills are (in order of amount used):
Excel (vlookups, pivot tables, charts)
Access--basic queries and import/export
APL--ability to fix/add simple code
E-Z-Trieve
PTS
APL--ability to write whole programs
Access--ability to build relational databases and work from SQL code
VBA
SAS

[Dr T Non-Fan]

It is essential for an actuarial department to have several "experts" at many different facets of PC use and data retrieval. Oh, and actuarial experience, too. Sometimes that's needed.

To have every actuarial employee be an expert on everything is ideal, but impractical.