Find text then Copy Adjacent Cell and Paste into two seperate cells

[articfox]

Hello, just hoping to get some help with this. I’ve two words that have different values associated with them, I just want to findthe words and copy the values from the adjacent cells into separate cells for example Cell R43, R45, R47 etc for the value associated with Wait and a separate cell S42, S44, S46 etc for the value associated with resume, see below. What I’m finding is that it’ll copy the values multipletimes into column R and S and repeat the last value over and over again when I just want it to be sequential.


Thanks for any help. Cheers



Sub Search()

' Defines variables
Dim Cell, cRange As Range

' Sets the range to check
Set cRange =Range("E3:E303")
' For each cell in range
For Each CellIn cRange
' If cell value is "Wait" then...
IfCell.Value = "Wait" Then
' The value in column E on the same row equals the samevalue as 1 cells to the right of the beings cell
Range("R34:R0" & (Cell.Row)).Value = Cell.Offset(0,6).Value
End If
' Check next cell in range
Next Cell

' Sets the range to check
Set cRange =Range("E3:E303")
' For each cell in range
For Each CellIn cRange
' If cell value is "Resume" then...
If Cell.Value = "Resume" Then
' The value in column E on the same row equals the samevalue as 1 cells to the right of the beings cell
Range("S34:S0" & (Cell.Row)).Value = Cell.Offset(0,6).Value
End If
' Check next cell in range

Next Cell

End Sub


Thanks again

 

[mumps]

Your code is a little confusing. It searches a range in column E for the words "Wait" and "Resume". Then it assigns the value in column K (Cell.Offset(0,6).Value) to column R for "Wait" or column S for "Resume". In your post you said

find the words and copy the values from the adjacent cells

The "adjacent cells" would be in column F not column K. Could you please clarify in detail.

 

[jmacleary]

Hi there. It looks like you are pasting into row 34 onwards each time - try replacing

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("R34:R0" & (Cell.Row)).Value = Cell.Offset(0,6).Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][/FONT]

with

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("R" & (Cell.Row)).Value = Cell.Offset(0,6).Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][/FONT]

and

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("S34:S0" & (Cell.Row)).Value = Cell.Offset(0,6).Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][/FONT]

with

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("S" & (Cell.Row)).Value = Cell.Offset(0,6).Value[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][/FONT]

 

[articfox]

Hi there. It looks like you are pasting into row 34 onwards each time - try replacing

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("R34:R0" & (Cell.Row)).Value = Cell.Offset(0,6).Value[/COLOR][/SIZE][/FONT]

with

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("R" & (Cell.Row)).Value = Cell.Offset(0,6).Value[/COLOR][/SIZE][/FONT]

and

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("S34:S0" & (Cell.Row)).Value = Cell.Offset(0,6).Value[/COLOR][/SIZE][/FONT]

with

[FONT=Times New Roman][SIZE=3][COLOR=#000000]Range("S" & (Cell.Row)).Value = Cell.Offset(0,6).Value[/COLOR][/SIZE][/FONT]

Thanks it worked a charm. Cheers