Fill empty cells in a row between two value containing cells

[likexl]

Hello,

How one can achive solution in the following situation:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]STATUS
[/TD]
[TD]DAY1
[/TD]
[TD]DAY2
[/TD]
[TD]Day3
[/TD]
[TD]Day4
[/TD]
[TD]DAy5
[/TD]
[/TR]
[TR]
[TD]H1
[/TD]
[TD]PASSIVE
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]H2
[/TD]
[TD]ACTIVE
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]H1
[/TD]
[TD]ACTIVE
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD]500
[/TD]
[TD][/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]H3
[/TD]
[TD]ACTIVE
[/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD][/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]H4
[/TD]
[TD]ACTIVE
[/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










After executing vba code following is expected:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]STATUS
[/TD]
[TD]DAY1
[/TD]
[TD]DAY2
[/TD]
[TD]Day3
[/TD]
[TD]Day4
[/TD]
[TD]DAy5
[/TD]
[/TR]
[TR]
[TD]H1
[/TD]
[TD]PASSIVE
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]H2
[/TD]
[TD]ACTIVE
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]H1
[/TD]
[TD]ACTIVE
[/TD]
[TD]100
[/TD]
[TD]100
[/TD]
[TD]500
[/TD]
[TD]500
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]H3
[/TD]
[TD]ACTIVE
[/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD]200
[/TD]
[TD]200
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]H4
[/TD]
[TD]ACTIVE
[/TD]
[TD]100
[/TD]
[TD]200
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











Id can be duplicate, but id active will be unique.
Value in the empty cell filled will based on previous.

Thanks.

 

[hippiehacker]

this should get you started

Sub likexl()
'count columns to proceed
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
'loop through rows
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
 'loop column in each row
  For j = 1 To lcol
   'check if last cell has been reached
   If Cells(i, j).Column <> lcol Then
    'copy values to empty cells
    If Cells(i, j + 1) = "" Then Cells(i, j + 1) = Cells(i, j)
   End If
  Next j
Next i
End Sub

 

[likexl]

Dear Hippiehacker,

Thank u very much for your reply.

this should get you started

In most cases "Investment of time solves the stuff" - sometimes not!

so, I also got one working solution with - try n error. I am giving it here so that one can modify according to needs :

Dim count As Long
                    count = dayin + 3
                    Do
                    count = count - 1
                    Loop Until ActiveCell.Offset(0, count - 1).Value <> ""
                    
                    count = count - 1
                    fillvalue = ActiveCell.Offset(0, count).Value
                    
                    Do
                    ActiveCell.Offset(0, count + 1).Value = fillvalue
                    count = count + 1
                    Loop Until ActiveCell.Offset(0, count + 1).Value <> ""

This will fill between two values, this can be repeated for other gaps as well.
Thanks again.