excel vba : How to replace cell that contains specific text

[huiyin9218]

Hi,

How do I write a code to replace cells that contains "0" in column A with the word at the top row of each 0 group?

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]AD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]XX[/TD]
[/TR]
</tbody>[/TABLE]

Above is sample table and below is the output that i wish to obtain.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]XX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DE[/TD]
[TD]XX[/TD]
[/TR]
</tbody>[/TABLE]

I would be so grateful for your help.

 

[Peter_SSs]

Assuming those zero values are numerical values and not text and they are the only numerical values in the column & that they are not the result of formulas then try this with a copy of your workbook.

Sub Replace_Zero()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .SpecialCells(xlConstants, xlNumbers).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

If any of my assumptions are incorrect, then please clarify.

 

[huiyin9218]

Assuming those zero values are numerical values and not text and they are the only numerical values in the column & that they are not the result of formulas then try this with a copy of your workbook.

Sub Replace_Zero()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .SpecialCells(xlConstants, xlNumbers).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

If any of my assumptions are incorrect, then please clarify.

It works! Thank you!

 

[huiyin9218]

Sub Replacezero()


j = 3

lastRowA = Range("A" & Rows.Count).End(xlUp).Row

For Each Cell In Range("A2:A" & lastRowA)
If Cells(j, 1) = 0# Then
Cells(j, 1) = Cells(j - 1, 1)

End If
Next Cell


End Sub

This the code I tried to write just now, it worked for a cell. Can you teach me how to make it to continue until the end of the cell that contains data? Is it something like j+1
Sorry as I'm still a beginner in vba, hope you can teach me.

 

[Peter_SSs]

If you wanted to do it one cell at a time instead of in bulk ..
Highly recommend to always declare all your variables. You can force yourself to do that: In the vba window Tools -> Options... -> Editor tab -> Require Variable Declaration -> OK

Sub Replacezero()
  Dim lastRowA As Long
  Dim Cell As Range
  
  lastRowA = Range("A" & Rows.Count).End(xlUp).Row

  For Each Cell In Range("A2:A" & lastRowA)
    If Cell.Value = 0 Then
      Cell.Value = Cell.Offset(-1).Value
    End If
  Next Cell
End Sub

 

[huiyin9218]

If you wanted to do it one cell at a time instead of in bulk ..
Highly recommend to always declare all your variables. You can force yourself to do that: In the vba window Tools -> Options... -> Editor tab -> Require Variable Declaration -> OK

Sub Replacezero()
  Dim lastRowA As Long
  Dim Cell As Range
  
  lastRowA = Range("A" & Rows.Count).End(xlUp).Row

  For Each Cell In Range("A2:A" & lastRowA)
    If Cell.Value = 0 Then
      Cell.Value = Cell.Offset(-1).Value
    End If
  Next Cell
End Sub

I see. Thank you so much!!

 

[Peter_SSs]

It works! Thank you!

I see. Thank you so much!!

You are welcome on both counts.