If column BI contains "N" then replace values in the row from column G:BE using the cell in column BF as find criteria and BH as replacement criteria

Knockoutpie

Board Regular
Joined
Sep 10, 2018
Messages
116
Office Version
  1. 365
Platform
  1. Windows
@DanteAmor

Hi all,

wondering if you could help me modify something Dante whipped up for me a long time ago.

instead of replacing all values with criteria in BF and replacement in BH, is it possible to only replace values for G:BE if BI contains "N"

Loop - Select row, find value, replace value - previous post

this is the code i'm currently using which replaces all values in all rows.

VBA Code:
    Dim ws1 As Worksheet
   Set ws1 = ActiveWorkbook.Sheets("Sheet1")
   Worksheets("Sheet1").Activate

  Dim i As Long
 
  For i = 4 To Range("D" & Rows.Count).End(3).Row
    If Range("BF" & i).Value <> "" And Range("BH" & i).Value <> "" Then
      Range("G" & i & ":BF" & i).Replace Range("BF" & i).Value, Range("BH" & i).Value, xlPart
    End If
  Next
 

Attachments

  • Screenshot 2024-02-08 122113.png
    Screenshot 2024-02-08 122113.png
    5.2 KB · Views: 21

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
The following looks for the BF data in the G:BE range and replaces it with the BH data:

VBA Code:
Sub macro()
  Dim ws1 As Worksheet
  Set ws1 = ActiveWorkbook.Sheets("Sheet1")
  Worksheets("Sheet1").Activate
 
  Dim i As Long
 
  For i = 4 To Range("D" & Rows.Count).End(3).Row
    If Range("BI" & i).Value = "N" Then
      Range("G" & i & ":BE" & i).Replace Range("BF" & i).Value, Range("BH" & i).Value, xlPart
    End If
  Next
End Sub
 
Upvote 1
Solution
The following looks for the BF data in the G:BE range and replaces it with the BH data:

VBA Code:
Sub macro()
  Dim ws1 As Worksheet
  Set ws1 = ActiveWorkbook.Sheets("Sheet1")
  Worksheets("Sheet1").Activate
 
  Dim i As Long
 
  For i = 4 To Range("D" & Rows.Count).End(3).Row
    If Range("BI" & i).Value = "N" Then
      Range("G" & i & ":BE" & i).Replace Range("BF" & i).Value, Range("BH" & i).Value, xlPart
    End If
  Next
End Sub
Dante that's perfect, cuts my processing time down by 95%! Thanks a bunch again!
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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