Find text then Copy Adjacent Cell and Paste into Column A

[beccabu87]

Hi, I need help. I've tried searching for macros and editing them to suit with no such luck.

I need to search my worksheet and find the word "Acct", then copy the value in the cell 2 columns over into column A.

For example, if "Acct" was found in P18, then copy value from R18 and paste into A18. I need to continue doing this for the entire worksheet (about 12000 rows). "Acct" does not occur in the same column every time, so I cannot just search through a particular column.

And assistance is greatly appreciated.

 

[Fishboy]

Hi, I need help. I've tried searching for macros and editing them to suit with no such luck.

I need to search my worksheet and find the word "Acct", then copy the value in the cell 2 columns over into column A.

For example, if "Acct" was found in P18, then copy value from R18 and paste into A18. I need to continue doing this for the entire worksheet (about 12000 rows). "Acct" does not occur in the same column every time, so I cannot just search through a particular column.

And assistance is greatly appreciated.

Hi beccabu87, welcome to the boards.

Try out the following macro in a COPY of your workbook. The following code is inserted into a standard module and applied to a button to be run. You can amend the bold red range to suit your data.

Sub TEST()
' Defines variables
Dim Cell, cRange As Range
' Sets the range to check
    Set cRange = Range("B1:Q1200")
' For each cell in range
        For Each Cell In cRange
' If cell value is Acct then...
            If Cell.Value = "Acct" Then
' The value in column A on the same row equals the same value as 2 cells to the right of the Acct cell
                Range("A" & (Cell.Row)).Value = Cell.Offset(0, 2).Value
            End If
' Check next cell in range
        Next Cell
End Sub

 

[beccabu87]

This worked like a charm. Thank you!!

 

[Fishboy]

This worked like a charm. Thank you!!

You're most welcome