excel vba: Replace cell

[huiyin9218]

Hi,

How do I write a code to replace cell that contains specific value. For example, when "0.00E+00" is detected, I would like the cell on the left to replace "0.00E+00"

	Column A	Column B
Row 1	AB	0.00E+00
Row 2	0	8.65E+00
Row 3	EF	0.00E+00
Row 4	0	1.85E+00

<tbody>
</tbody>

When "0.00E+00" is detected in column B, cell on the left will replace it. Sample table is shown above and output I wish to obtain is shown in table below.

	Column A	Column B
Row 1		AB
Row 2	0	8.65E+00
Row 3		EF
Row 4	0	1.85E+00

<tbody>
</tbody>

I would be so grateful for your help.

 

[oldbrewer]

AB	AB
0	8.65E+00
EF	EF
0	1.85E+00
				this macro has done it
				ub Macro4()
				'
				' Macro4 Macro
				' Macro recorded 16/08/2018 by bob
				'
				'
				For j = 1 To 4
				If Cells(j, 2) = 0# Then Cells(j, 2) = Cells(j, 1)
				Next j
				End Sub

<colgroup><col><col><col span="8"></colgroup><tbody>
</tbody>

 

[hiker95]

huiyin9218,

Here is another macro solution for you to consider that will run in the active worksheet.

Sub huiyin9218()
' hiker95, 08/16/2018, ME1067229
Dim c As Range
Application.ScreenUpdating = False
With ActiveSheet
  For Each c In .Range("B1", .Range("B" & Rows.Count).End(xlUp))
    If c = 0# Then
      c = c.Offset(, -1)
      c.Offset(, -1).ClearContents
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

 

[huiyin9218]

huiyin9218,

Here is another macro solution for you to consider that will run in the active worksheet.

Sub huiyin9218()
' hiker95, 08/16/2018, ME1067229
Dim c As Range
Application.ScreenUpdating = False
With ActiveSheet
  For Each c In .Range("B1", .Range("B" & Rows.Count).End(xlUp))
    If c = 0# Then
      c = c.Offset(, -1)
      c.Offset(, -1).ClearContents
    End If
  Next c
End With
Application.ScreenUpdating = True
End Sub

This works for me. Thank you!

 

[huiyin9218]

Thank you for all your ideas.

 

[hiker95]

This works for me. Thank you!

huiyin9218,

Thanks for the feedback.

You are very welcome.