vba help - Find specific string and replace top cell value

[Mallesh23]

Hi Team,

Need vba help to find "Agst Ref" or "New Ref." if found via loop
Fill Top cells Values.
Highlighted those cells value in Red Color.


Input Data is in ColumnA.
Expected Output ColumnB.

Below is a Table.

Book2
	A	B
1	Input Columns	Expected Output
2	xyz Limited	xyz Limited
3	Agst Ref	xyz Limited
4	Agst Ref	xyz Limited
5	Agst Ref	xyz Limited
6	Agst Ref	xyz Limited
7	Union Bank of India	Union Bank of India
8	PQR Paints Ltd.	PQR Paints Ltd.
9	New Ref	PQR Paints Ltd.
10	Union Bank of India	Union Bank of India
11	Interest of Cash Credit - Bank	Interest of Cash Credit - Bank
12	Union Bank of India	Union Bank of India
13	ABC Limited	ABC Limited
14	Agst Ref	ABC Limited
15	Union Bank of India	Union Bank of India
16	TDS A.Y. 2021-22	TDS A.Y. 2021-22
17	New Ref	TDS A.Y. 2021-22
18	Venkys (India) Ltd.	Venkys (India) Ltd.
19	Agst Ref	Venkys (India) Ltd.
20	Agst Ref	Venkys (India) Ltd.
21	Agst Ref	Venkys (India) Ltd.
Sheet1

Thanks
mg

 

[mse330]

Hi mg,

Try below code ...

Sub test()

Dim c As Range

For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
   If c.Value = "Agst Ref" Or c.Value = "New Ref" Then c.Offset(-1).Copy c
Next

End Sub

 

[Mallesh23]

Hi mse330,

Perfect ! thanks for your help, it is giving correct output.

Need one more help,

Output is showing in Same Column, Can we take it into Column B.





Thanks
mg

 

[mse330]

Try now ...

Sub test()

Dim c As Range

Columns(1).Copy [B1]

For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
   If c.Value = "Agst Ref" Or c.Value = "New Ref" Then c.Offset(-1).Copy c
Next

End Sub

 

[Mallesh23]

Hi mse330,

I already tried this it has worked ! ok will continue this logic as its giving correct output.


Thanks for your help! ?



Thanks
mg