Hi - stuck on an issue here
I am looking to search through a column and any cells that do not have an
entry whose first three characters are ORD or 'B'ORD or GBP etc.
If the entry in the cell does not start with any of these 3 characters, i
would like to copy that cell and paste its contents into another place, on
the same row, 10 cells to the left of the copied cell.

eg: If column AG2 is "GBP STERLING" macro will move on..... If AG3 reads
"ORD 64" the macro will move on, but if AG4 is "BLK GOL" that entry will be
copied, and pasted into cell W4. The macro will continue down column AG
until there is no data.

Stumped by this one - can anyone help?

Hi,

entry whose first three characters are ORD or 'B'ORD or GBP etc.

The second of your arguments isn't 3 characters long and etc isn't very
helpful so I've gone with just 3 characters and ignored etc.

Try this

Sub Versive()
lastrow = Cells(Cells.Rows.Count, "AG").End(xlUp).Row
Set MyRange = Range("AG1:AG" & lastrow)
S = "ORD,BOR,GBP"
v = Split(S, ",")
For Each c In Range("AG1:AG" & lastrow)
If IsError(Application.Match(CStr(Left(c.Value, 3)), v, 0)) Then
c.Offset(, -10).Value = c.Value
End If
Next c
End Sub


Mike

"Withnails" wrote:

Hi - stuck on an issue here
I am looking to search through a column and any cells that do not have an
entry whose first three characters are ORD or 'B'ORD or GBP etc.
If the entry in the cell does not start with any of these 3 characters, i
would like to copy that cell and paste its contents into another place, on
the same row, 10 cells to the left of the copied cell.

eg: If column AG2 is "GBP STERLING" macro will move on..... If AG3 reads
"ORD 64" the macro will move on, but if AG4 is "BLK GOL" that entry will be
copied, and pasted into cell W4. The macro will continue down column AG
until there is no data.

Stumped by this one - can anyone help?

it works but not when i have it in VBA. it asks me to define the
variable/object of c in 'Each c In Range.... etc

Any ideas?

"Mike H" wrote:

Hi,

entry whose first three characters are ORD or 'B'ORD or GBP etc.

The second of your arguments isn't 3 characters long and etc isn't very
helpful so I've gone with just 3 characters and ignored etc.

Try this

Sub Versive()
lastrow = Cells(Cells.Rows.Count, "AG").End(xlUp).Row
Set MyRange = Range("AG1:AG" & lastrow)
S = "ORD,BOR,GBP"
v = Split(S, ",")
For Each c In Range("AG1:AG" & lastrow)
If IsError(Application.Match(CStr(Left(c.Value, 3)), v, 0)) Then
c.Offset(, -10).Value = c.Value
End If
Next c
End Sub


Mike

"Withnails" wrote:

Hi - stuck on an issue here
I am looking to search through a column and any cells that do not have an
entry whose first three characters are ORD or 'B'ORD or GBP etc.
If the entry in the cell does not start with any of these 3 characters, i
would like to copy that cell and paste its contents into another place, on
the same row, 10 cells to the left of the copied cell.

eg: If column AG2 is "GBP STERLING" macro will move on..... If AG3 reads
"ORD 64" the macro will move on, but if AG4 is "BLK GOL" that entry will be
copied, and pasted into cell W4. The macro will continue down column AG
until there is no data.

Stumped by this one - can anyone help?