Forum Discussion

dweatherley99's avatar
dweatherley99
Copper Contributor
May 05, 2022
Solved

Excel formula help - populating cells between two cells that contain '1' by row

Hi all,   Currently I am working with a very large dataset which looks like this (see photo attached). I am trying to find a way to automatically fill cells with a '1' in rows after the first ...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    May 05, 2022

    dweatherley99 

    Sub one()

Dim j As Integer
Dim i As Integer
Dim z As Integer
Dim k As Integer

For i = 2 To 11

For j = 7 To 1 Step -1

If Cells(i, j).Value = 1 Then
z = j

For k = 1 To 7

If Cells(i, k).Value = 1 And k < z Then
Cells(i, k + 1).Value = 1

If k = z Then
Exit For

Else
End If

Else
End If

Next k

Else
End If

Next j
Next i

End Sub

    Maybe with this code. You can click the button in cell I3 in the attached file in sheet "Tabelle1" to start the macro.

dweatherley99's avatar
dweatherley99
Copper Contributor
May 06, 2022
Thanks for the formula, Patrick. I did manage to achieve my goal by using VBA which the above commenter assisted me with, but I have taken note of your formula for future reference. Much appreciated!

Resources