VB Difficulty

  Craig.m 09:31 09 May 2005

Whisperer kindly gave me the following VB as a solution to an Excel problem I was having:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[A1] = Range("E" & Cells(Rows.Count, 2).End(xlUp).Row)

End Sub

It works fine getting the last entered value in column E (5). Column B (2) is where a date is entered and the data in E is not required unless there is a date entered in B. In trying to understand what appears to be a simple piece of script, I changed the 2 (column B) reference to look at 5 (Column E) and the returning cell (A1) went blank, the same with a 4 but it did work with a 3. The only thing I can think of that would be different between 2 and 3 versus 4 and 5 is that the latter two have numeric values and the first two have text or date in them.

Would anyone be able to explain why, when using fields 4 or 4 that a blank is returned?

I think VB is going to be something I will never understand to any reasonable level even though I would like to.


  pauldonovan 12:40 09 May 2005

I don't get what that is suppose to do - at the moment it just seems to be grabbing whatever value you put in cell E1 and bunging it in A1.

A bit of context will help answer this but from your post it isn't clear exactly what you want the vba to do.

  Craig.m 20:18 09 May 2005

Sorry for the delay in getting back.

I had a requirement for a cell A1 to equal the value of the last cell entry in column E. I do use the columns to the left of E so the last entry on B, C or D will coincide with the last entry in E. So it does work but as I said, changing the reference from Column B to D or E seems to mess things up.

All the spreadsheet does is have a running total against date entries, I then want the final total to be represented in one specific cell so that I can then use that figure in another spreadsheet by linking to that one cell.


  pauldonovan 08:55 10 May 2005

Now I get it...

If I have:

2 5 8
3 6 9
4 7 10

My cell A1, if I make it point to column B gives 4, C gives 7 and so on. Which is what you want?!

  Craig.m 13:25 10 May 2005

I have a cumulative mileage sheet which I have linked to a nother spreadsheet for claims. Column B is a date, C is a destination with a drop down box to select from, D is the mileage for that destination and E is the cumulative. Each time I make a journey I enter the date and then the destination then it totals it all up. A1 is the value of the last entry in Column E - however far down it may be. This way the spreadsheet that needs to know the final cumulative mileage only has to look at the one point - A1.

Whisperer devised this for me and it works but as I said it seems to look for the last entry in Column B then take the corresponding value in Column E, I was just wondering why I can change the 2 which refers to Column B, change it to a 3 (column C) it still works but as soon as it changes to a 4 or a 5 (D or E) it fails to return a value.

Whisperer did explain that the number reference was in fact the number reference to the column rather than a letter reference. Just having problems figuring why it works until the last two columns are referenced.


  pauldonovan 15:06 10 May 2005

..but if I change the logic to use 5 or 4 or 3 it still works! i.e.:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[A1] = Range("E" & Cells(Rows.Count, 5).End(xlUp).Row)

End Sub

It IS using column B, seemingly by going to the very last row in column 2 and then doing effectively an "EndUp" (try it - go to row 65536 in Column B then press the End key on your keyboard, followed by the up cursor key - it will find the last cell).

Apologies for formatting in previous post - I tried to lay out a table! Duh!

  Craig.m 21:49 16 May 2005

This is one reason why I tend to ask for a non VB solution - I didn't understand any of what you said and in just the same way, I try to edit what I thought I understood and it didn't work. I am clueless with VB, wish I wasn't, but this gave me the quick solution to my problem, fotunately it referencing the last item in column B is not an issue as it does coincide with the last item in E that I want. I just thought I would experiment to see if it would work the way I thought it might and proved my knowledge lacking in the extreme. I think the motto is if it works and doesn't need tweaking then leave it alone.

Think I will just tick this as solved and leave VB alone, to think I used to do fairly complex programmes in SAS and Basic but VB has me at a loss.


  VoG II 22:00 16 May 2005

These things are possible using sheet formulas click here

I don't understand why you need to use VBA for this.

  Craig.m 09:10 17 May 2005


I always try to get a non VB answer as I find it difficult to take the answer to something and not know how it works, with VB I can't seem to get an understanding of it. I asked a question and needed a quick solution at the time, whisperer supplied it with a VB answer which I could use and worked. Unfortunatley after putting it in place I then wanted to know how it worked and couldn't figure it out, hence this question.

I wanted one cell to equal the last cell in a column and didn't know how to tell it to go get the value.

Think i must really have a go at finding a site starting off with basics of VB.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

HP’s new Surface Pro rival is designed specifically for Adobe-using designers and artists

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?