I have 24 fields that auto populate based on time I would like to return the value of the last hour meter. so I need a code that looks at the cells and returns the last value that is not zero.

exampe


24
36
20
10
0
0
0
I would like the cell to copy the 10 value
I know I can do this if about 4 cells worth of nested if statement but would rather not.

Thanks

The code below assumes your data in cilA and copying to Col B , amend as needed

Sub lastnonzero()
Dim i As Integer

For i = Sheets("sheet1").Range("a65536").End(xlUp).Row To 1 Step -1

If Cells(i, 1) <> 0 Then
GoTo foundit

End If

Next i
foundit:
Cells(i, 1).Copy Cells(i, 2)

End Sub


Brian

what if the numbers are in row instead of col

and what is the last line of code doing?

Thanks

Dim LastMeter As Integer

For LastMeter = Sheets("summary").Range("AB1008").End("E1008").Row To 1 Step -1

If Cells(LastMeter, 1) <> 0 Then
GoTo foundit

End If

Next LastMeter
foundit:
Cells(LastMeter, 1).Copy Cells(LastMeter, 2)

this is what I have I figure i need to change Row to something else and still don't know what the last line does exactly.

This is what I have tried and it didn't work

Dim LastMeter As Integer

For LastMeter = Sheets("summary").Range("AB1008").End("E1008").Columns To 1 Step -1

If Cells(LastMeter, 1) <> 0 Then
GoTo foundit

End If

Next LastMeter
foundit:
AD1008.Value = LastMeter

As I said the last row of code copies the data from the A col to the B col.

Not done it with data in 1 row but try

For LastMeter = Sheets("summary").Range("AB1008").End(xltoleft).column To 5 Step -1

You are searching from your last column to column E which is column 5

Brian

This is what I got and it returns a "5" in the cell but there are no 5's to return in this range

Dim LastMeter As Integer

For LastMeter = Sheets("summary").Range("AB1008").End(xlToLeft).Column To 5 Step -1

If Cells(LastMeter, 1) <> 0 Then
GoTo foundit

End If

Next LastMeter
foundit:

Sheets("summary").Select
Range("AD1008").Value = LastMeter

when i step through it it looks like to goes to foundit right away

now it is returning a 28 but still no 28s in the range.

Hi, elfranzen,

what is returned inside that procedure is the counter (as AB1008 happens to be located in Column 28 where the search range starts).

Dim lngColCounter As Long

With Sheets("summary")
For lngColCounter = .Range("AB1008").End(xlToLeft).Column To 1 Step -1
' If Len(.Cells(1008, lngColCounter).Text) > 0 Then
If Not IsEmpty(.Cells(1008, lngColCounter).Value) And .Cells(1008, lngColCounter).Value <> 0 Then
.Range("AD1008").Value = .Cells(1008, lngColCounter).Value
Exit Sub
End If
Next lngColCounter
End With
Ciao,
Holger

The Format of Cells is Cells(row,column) therefor now that we are looking at rows rather than columns the code becomes

Dim LastMeter As Integer

For LastMeter = Sheets("summary").Range("AB1008").End(xlToLeft).Column To 5 Step -1

If Cells(1008,LastMeter) <> 0 Then
GoTo foundit

End If

Next LastMeter
foundit:

Range("AD1008")= cells(1008,LastMeter)

Brian