excel vba: Replace cell

H

huiyin9218

Board Regular
Joined
Aug 7, 2018
Messages
53
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"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]AB[/TD]
[TD]0.00E+00[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]0[/TD]
[TD]8.65E+00[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]EF[/TD]
[TD]0.00E+00[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]0[/TD]
[TD]1.85E+00[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD][/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]0[/TD]
[TD]8.65E+00[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD][/TD]
[TD]EF[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]0[/TD]
[TD]1.85E+00[/TD]
[/TR]
</tbody>[/TABLE]

I would be so grateful for your help.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
[TABLE="width: 698"]
<colgroup><col><col><col span="8"></colgroup><tbody>[TR]
[TD]AB[/TD]
[TD]AB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]8.65E+00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EF[/TD]
[TD]EF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]1.85E+00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]this macro has done it[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]ub Macro4()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]' Macro4 Macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]' Macro recorded 16/08/2018 by bob[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]'[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"] For j = 1 To 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"] If Cells(j, 2) = 0# Then Cells(j, 2) = Cells(j, 1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
huiyin9218,

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

Code: 
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
 
Upvote 0
hiker95 said:
huiyin9218,

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

Code: 
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
Click to expand...

This works for me. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

youbitto
  • Solved
Copy paste from multiple Excel and Csv files to another excel file VBA
Replies
2
Views
196
youbitto
youbitto
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
257
Peter_SSs
Peter_SSs
J
  • Solved
Selenium VBA - loop to align new data with old data in worksheet
Replies
8
Views
337
Anthony47
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
299
dshtesy
D
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
181
Tara_A_Data
T

Forum statistics

Threads
1,223,704
Messages
6,173,984
Members
452,540
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top