VBA find first non 0 cell per row and update firs cell in row with that value - looping

[paulq]

Good day

I have an excel spreadsheet with about 2000 rows and 200 columns, I already have the code right to find 0 values and update with the previous value thats not 0. But i am struggling when the first three values in a row is zero to find the firts none zero value and update the first zero values with these values.

Code that works.

   Sub colb()
   Dim lRow As Long
Dim lCol As Long
    
    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Find the last non-blank cell in row 1
    lCol = Cells(6, Columns.Count).End(xlToLeft).Column

    Dim cell As Range
    For Each cell In Range(Cells(4, 4), Cells(lRow, lCol))
    If cell.Value = 0 Then
    cell.Value = cell.Offset(columnOffset:=-1).Value
    End If
   Next
  
End Sub

This gets all the zeros in sheet and changes them.

But now my first 3 cells in the row is 0.

Here is the code

Dim lRow As Long
Dim lCol As Long
    
    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Find the last non-blank cell in row 1
    lCol = Cells(6, Columns.Count).End(xlToLeft).Column
    

     ' format cells
    Dim cell As Range
            Range(Cells(4, 4), Cells(lRow, lCol)).Select
    Selection.NumberFormat = "0.0000000000"
    ' end cell change
    
    
    For Each c In Range(Cells(4, 4), Cells(lRow, lCol)).Cells
  If c.Value > 0 Then
    firstValue = c.Value
    firstAddress = c.Address
    Exit For
  End If
Next

MsgBox "The first value greater than zero is in cell " & firstAddress & _
       "; - it has value " & firstValue
       
       
     For Each cell In Range(Cells(3, 2), Cells(lRow, 5))
   


       If cell.Value = 0 Then
      cell.Value = firstValue

      End If

    Next

 

[BSALV]

something like this, i didn't test it ...

Sub colb()
     Dim lRow  As Long
     Dim lCol  As Long
     Dim i As Integer, b As Boolean
     'Find the last non-blank cell in column A(1)
     lRow = Cells(Rows.Count, 1).End(xlUp).Row

     'Find the last non-blank cell in row 1
     lCol = Cells(6, Columns.Count).End(xlToLeft).Column

     Dim cell  As Range
     For Each cell In Range(Cells(4, 4), Cells(lRow, lCol))
          If cell.Value = 0 Then
               b = False                                        'flag down
               For i = 1 To cell.Column - 1                     'loop from previous cell to 1st column
                    If cell.Offset(, -i).Value > 0 Then         '
                         cell.Value = cell.Offset(, -i).Value   'take that value
                         b = True                               'flag up
                         Exit For                               'stop loop
                    End If
               Next
               If Not b Then MsgBox "no value found for " & cell.Address, vbInformation     'flag down = msgbox
          End If
     Next

End Sub

 

[DanteAmor]

Hi and welcome to MrExcel

when the first three values in a row is zero to find the firts none zero value

If there are no values other than zero, then what should you do, leave them at that.

If you have this data, what result would you need.

varios 24mar2022.xlsm
	A	B	C	D	E	F
6	0	1	0	0	3	0
7	1	0	0	0	5	0
8	0	0	0	0	2	0
sheet12

 

[paulq]

a6 = 1
a8 = 2
b8 = 2
c8 =2
d8 = 2

Regards

 

[DanteAmor]

I'm confused, where your data really is. I checked your macros and you have different start cells:

lRow = Cells(Rows.Count, 1).End(xlUp).Row 'column A
lCol = Cells(6, Columns.Count).End(xlToLeft).Column 'row 6, I guess A6 cell

but...
For Each c In Range(Cells(4, 4), Cells(lRow, lCol)).Cells 'D4 cell

For Each cell In Range(Cells(3, 2), Cells(lRow, 5)) 'B3 cell

Fit the start cell on this line of the macro:
Set ini = Range("A6") 'Start cell

Sub replace_Zero()
  Dim i As Long, j As Long, lc As Long
  Dim ini  As Range
  Set ini = Range("A6")   'Start cell
 
  lc = Cells(ini.Row, Columns.Count).End(1).Column
  For i = ini.Row To Cells(Rows.Count, ini.Column).End(3).Row
    For j = ini.Column To lc
      If Cells(i, j).Value = 0 Then
        Cells(i, j).Value = findvalue(i, j, lc)
      Else
        Exit For
      End If
    Next
  Next
End Sub

Function findvalue(i As Long, j As Long, lc As Long)
  Dim k As Long
  For k = j + 1 To lc
    If Cells(i, k) > 0 Then
      findvalue = Cells(i, k)
      Exit Function
    End If
  Next
End Function