copy first cell that doesn't have a zero in it

[elfranzen]

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

 

[Brianwarnock]

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

 

[elfranzen]

what if the numbers are in row instead of col

and what is the last line of code doing?

Thanks

 

[elfranzen]

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.

 

[elfranzen]

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

 

[Brianwarnock]

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

 

[elfranzen]

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

 

[elfranzen]

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.

 

[HaHoBe]

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

 

[Brianwarnock]

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