Search and Replace with Dynamic Columns (VBA)

[CakeisaDie]

I run a report every week and have some Manual Adjustments I need to make frequently that also change frequently. I'm looking to automate this but I really don't understand VBA too well. There are also a bunch more rows being requested now so using a simple if formula is getting too heavy on my computer.

I found this solution which works for changing things in the same column
and this solution which does an offset but has a fixed output

I am imagining that mixing these 2 solutions and making some sort of reference would do what I need it to do but I'm struggling to make them work.

I'm also looking to copy paste the original value into the last column in this case I.

Data Used for Pivot Table A:E + I is what I'm looking for as a result of the helper cells.

 

[sijpie]

Hi CakeisaDie, your post isn't very clear on what you want to automate.
I would suggest that

1. you post example data with which you start

2. then explain what this manual adjustment is that you want to automate:

• Which data does it look at,
• what is the formula to make the adjustment

3. Then show in a next screenshot the result

4. Lastly a screenshot of how you want to present the data finally.

 

[CakeisaDie]

I'm not sure how to explain it easier but let me try.
Start with this data.

VBA Test.xlsm
	A	B	C	D	E	F	G	H	I
4	Year	Salesperson	Item	Trans Date	Invc Dt	Manual Adjustment Identified	Which Column to Change (Offset?)	New Value	Original Value
5	2023	DS	Cake	5/18/2023	05/18/23	Manual Adjustment	5	12/31/2023
6	2023	DS	Cake	5/18/2023	05/18/23
7	2023	DS	Cake	5/18/2023	05/18/23
8	2023	DS	Cake	5/18/2023	05/18/23	Manual Adjustment	3	Cookies
9	2023	DS	Cake	5/18/2023	05/18/23
10	2023	DS	Cake	5/18/2023	05/18/23
11	2023	DS	Cake	5/18/2023	05/18/23	Manual Adjustment	1	Lillian
12	2023	DS	Cake	5/18/2023	05/18/23
Sheet1 (2)

With the end result looking like this (where the colored cells are what was changed)

VBA Test.xlsm
	A	B	C	D	E	F	G	H	I
1	DATA used for Pivot Table					Helper Cells
2
3							Row Remains the Manual Adjustment Field
4	Year	Salesperson	Item	Trans Date	Invc Dt	Manual Adjustment Identified	Which Column to Change (Offset?)	New Value	Original Value
5	2023	DS	Cake	5/18/2023	12/31/23	Manual Adjustment	5	12/31/2023	5/18/2023
6	2023	DS	Cake	5/18/2023	05/18/23
7	2023	DS	Cake	5/18/2023	05/18/23
8	2023	DS	Cookies	5/18/2023	05/18/23	Manual Adjustment	3	Cookies	Cake
9	2023	DS	Cake	5/18/2023	05/18/23
10	2023	DS	Cake	5/18/2023	05/18/23
11	Lillian	DS	Cake	5/18/2023	05/18/23	Manual Adjustment	1	Lillian	Lillian
12	2023	DS	Cake	5/18/2023	05/18/23
Sheet1 (2)

My inclination is that I could use one of the solutions in the links, if I could make the fixed offset of "-42" and Fixed "To-Be-Deleted" to refer to cells rather than just the fixed value or Make the AN AP G AN AP's reference instead of a fixed column.

What I've been doing has been having multiple helper cells and using formulas to create a new column for each change and then overwriting the change. I think Power Query can do this, but I think VBA is more likely to be cleaner and Formulas are getting too heavy now.

Sub SearchReplace()
Dim i As Long
Dim Fnd As Range

Set Fnd = Range("AQ1")
For i = 1 To Application.CountIf(Range("AQ:AQ"), "Cancelled")
Set Fnd = Range("AQ:AQ").Find(Range("Cancelled").Value, Fnd, , xlWhole, , , False, , False)
Fnd.Offset(, -42).Value = "To-Be-Deleted"
Next i
End Sub

Sub Picture2_Click()

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

 

[sijpie]

sorry for the delay, busy with holls and other things...

Put this code in the code module for the sheet:

1. Right click on the sheet name tab
2. select 'View code'
3. the VBA editor will open with the module opened
4. paste the following code in the module

How it works:
once the user has entered a value in say G3 and H3, the macro will put the value from the appointed cell in I3
then it will put the value from H3 into the appointed cell
then it will add the 'Manually adjusted' label in F3

The macro will check if the column number in G3 is a valid number

It doesn't matter in which order Gx and Hx are filled out.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vPrev As Variant, vNew As Variant, vOff As Variant
    Dim iTC As Integer, bErr As Boolean
    
    If Not Intersect(Target, Range("G:H")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            'check which column has been changed, and set variables accordingly
            Select Case Target.Column
                Case 7  '"G"
                    vOff = Target
                    vNew = Target.Offset(0, 1)
                    iTC = 0
                Case 8  '"H"
                    vOff = Target.Offset(0, -1)
                    vNew = Target
                    iTC = -1
            End Select
            'check if both columns have been filled, else do nothing
            If Len(vOff) = 0 Or Len(vNew) = 0 Then Exit Sub
            'check if offset column contains a number <=5
            If (Not IsNumeric(vOff)) Or vOff > 5 Then
                MsgBox "This field can only contain a numeric offset (1 - 5)", _
                        Title:="Error " & Target.Offset(0, iTC).Address
                Target.Offset(0, iTC).Select
                Exit Sub
            End If
                
            'Everything is OK, so do the changes
            With Target.Offset(0, iTC)
                .Offset(0, 2) = .Offset(0, vOff - 7)  'put old value in column I
                .Offset(0, vOff - 7) = .Offset(0, 1)  'put new value in required column
                .Offset(0, -1) = "Manual adjustment"    'add lable that row has been adjusted
            End With
                
        End If
    End If
    
End Sub