Standard Excel Function or Needs VBA?

[hellcat83]

Hi

I have a scenario that I have no idea how to solve using standard functions.

I would like to take a range of cells with will over time be filled with data, and then in another cell display the most recent entry in the initial range.

For example, A1:A10 are initially empty, but over time will be filled with numbers. I would like to find a formula to go in cell B1, that allows me to display the most recently entered value in the A1:A10 range.

I've seen some people suggest that this can't be done using standard functions, but I thought I'd try for ideas to the contrary before I dive into learning VBA to do it.

Cheers
Jim

 

[hellcat83]

I see what you mean, but I want it to find the most recent entry in each row and place that entry into another cell in the same row. Effectively I want to repeat the same operation for as many rows as there are data, but not transfer data from one row to another.

As I said, I can't see why the original code you posted wouldn't work if I could just wrap it in a for loop, as long as I could get the cell references right inside it.

 

[VoG]

Maybe this then: copy to the first available row in column B

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
End Sub

 

[hellcat83]

I was thinking something more like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Row As Integer
For Row = 1 To 3
FirstCol = "A" & Row
SecCol = "D" & Row
OutCol = "F" & Row
If Not Intersect(Target, Range("FirstCol:SecCol")) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
End Sub

Problem is that there appears to be an error in the If Not line, but I normally only code in Matlab and F so can't really see where its gone wrong

 

[VoG]

Maybe (untested)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Row As Integer
For Row = 1 To 3
    FirstCol = "A" & Row
    SecCol = "D" & Row
    OutCol = "F" & Row
    If Not Intersect(Target, Range(FirstCol & "," & SecCol)) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
End Sub

 

[hellcat83]

Nope, not outputting anything in the F column.

I am trying to use the loop in the right way though aren't I?

 

[VoG]

Working here. If you are trying to check columns A:D not A and D it should be

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Row As Integer
For Row = 1 To 3
    FirstCol = "A" & Row
    SecCol = "D" & Row
    OutCol = "F" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol)) Is Nothing Then Range(OutCol).Value = Target.Value
Next Row
End Sub

 

[hellcat83]

Ok, its working now and the codes were exactly the same, but for the tabs on the loop commands.

Anyway, thanks again for your help.

Jim

 

[hellcat83]

Back again...

I have a related, but slightly different question. After some testing I realised that for the code to work the entries had to be placed directly into the cells by the user. The problem is that the cells I want to use this on are filled when data is entered into other cells. The problem is that the raw data that's entered isn't in a format that is useful to display using this code, and the conversions aren't uniform so I can't do it after.

For example;

cells A1:D1 has data directly entered by the user. A converted version is placed in E1:H1 by excel functions I already have in there. I want to display the most recent entries into E1:H1 in J1.

This code works but only if I look in A1:D1. Can this be done?

 

[VoG]

Either use Worksheet_Change to address the precedent cells or Worksheet_Change

 

[hellcat83]

Hi

I got round the issue without, but that has hit another snag.

I now have two Worksheet_Change subs on one sheet, one to carry out the recent entry action, the other monitors three columns and adds an "e" to any entry made (it helps me later on).

The only way I could make them work at all was to have both inside one routine, with the "e" one before the recent entry (so it would pick up the added "e").

The problem now is that the recent entry routine is only picking up entries made in the "e" columns, even though it isn't written to.

Is this a problem with having both in one routine? (I'd add the code, but I don't know how to get one of those window in post things).

Cheers
Jim