If a cell contains certain keyword, then move values from 1 cell to another (same row)

[zack8576]

I need to modify a bunch of excel files, for any row that contains the word "Skimmer", I need to move the value from column B (same row) to in front of Skimmer (always in column K)
like this: (1)Skimmer, (2)Skimmer, (3)Skimmer. (The value in column B does not need to be removed)

see below for example file, code is also below, this is not working for me....
any help is appreciated, dropbox link for the example file is also below

Sub AssignStructureIDToSkimmer()
   With Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
      .Value = Evaluate(Replace(Replace("If(" & .Offset(, 10).Address & "=""Skimmer"","" ""&@,if(@="""","""",@))", "@", .Address), "#", .Offset(, 9).Address))
   End With
End Sub

2022120031 SKIMMER.csv

Shared with Dropbox

www.dropbox.com

 

[DanteAmor]

Try this:

Sub AssignStructureIDToSkimmer()
  With Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("=IF({1},IF(@=""Skimmer"",""(""&" & .Offset(, -9).Address & "&"")""&@,@))", "@", .Address))
  End With
End Sub

 

[Peter_SSs]

@DanteAmor
I think that will fill the blank cells in column K with 0 (at least it does for me)
Modified suggestion

Sub AssignStructureIDToSkimmer()
  With Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("=if(@=""Skimmer"",""(""&" & .Offset(, -9).Address & "&"")""&@,if(@="""","""",@))", "@", .Address))
  End With
End Sub

 

[DanteAmor]

I think that will fill the blank cells in column K with 0 (at least it does for me)

It doesn't happen for me.

Before:

Dante Amor
	A	B	C							J	K
1
2		1									Skimmer
3		2									a
4		3									b
5		4									Skimmer
6		5									c
7		6									d
8		7									Skimmer
9		8									e
10
11
12
13
Hoja3

After:

Dante Amor
	A	B	C							J	K
1
2		1									(1)Skimmer
3		2									a
4		3									b
5		4									(4)Skimmer
6		5									c
7		6									d
8		7									(7)Skimmer
9		8									e
10
11
12
13
Hoja3

 

[zack8576]

@DanteAmor
I think that will fill the blank cells in column K with 0 (at least it does for me)
Modified suggestion

Sub AssignStructureIDToSkimmer()
  With Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("=if(@=""Skimmer"",""(""&" & .Offset(, -9).Address & "&"")""&@,if(@="""","""",@))", "@", .Address))
  End With
End Sub

Thank Peter for the quick response, your code works perfectly for me.

Try this:

Sub AssignStructureIDToSkimmer()
  With Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("=IF({1},IF(@=""Skimmer"",""(""&" & .Offset(, -9).Address & "&"")""&@,@))", "@", .Address))
  End With
End Sub

Your solution works flawlessly, on the 4 files I've tested...Peter's code works perfectly as well...
I wonder if there is a way to select both of your answers as solution.....

 

[zack8576]

@DanteAmor
I think that will fill the blank cells in column K with 0 (at least it does for me)
Modified suggestion

Sub AssignStructureIDToSkimmer()
  With Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("=if(@=""Skimmer"",""(""&" & .Offset(, -9).Address & "&"")""&@,if(@="""","""",@))", "@", .Address))
  End With
End Sub

both yours and Dante's work perfectly, any way I can select 2 solutions for 1 question ?

 

[Peter_SSs]

It doesn't happen for me.

You don't have any blank cells in column K in your sample.

Before:

23 01 05.xlsm
	A	B	C							J	K
1
2		1									Skimmer
3		2									a
4		3
5		4									Skimmer
6		5
7		6
8		7									Skimmer
9		8									e
10
Skimmer

After:

23 01 05.xlsm
	A	B	C							J	K
1
2		1									(1)Skimmer
3		2									a
4		3									0
5		4									(4)Skimmer
6		5									0
7		6									0
8		7									(7)Skimmer
9		8									e
10
Skimmer

Having said that, since the OP says both codes work perfectly, perhaps those blank cells in the OP's sheet are not actually blank.

I wonder if there is a way to select both of your answers as solution.....

No, you can only mark one. So, if you are not getting any 0 values showing up in what appear to be blank cells in column A in your first image from either code, then mark Dante's post since he was first in.

 

[DanteAmor]

You don't have any blank cells in column K in your sample.

You're right, my macro puts zeros.
Here my code with the correction pointed out by Peter:

Sub AssignStructureIDToSkimmer()
  With Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("=IF({1},IF(@="""","""",IF(@=""Skimmer"",""(""&" & .Offset(, -9).Address & "&"")""&@,@)))", "@", .Address))
  End With
End Sub

 

[Peter_SSs]

Here my code with the correction pointed out by Peter:

Does your version actually require the IF({1}...) part of the formula? Mine doesn't, and therefore the OP's shouldn't (also using 365), require that extra calculation.
Without that, our codes are identical apart from the order.

 

[DanteAmor]

Does your version actually require the IF({1}...) part of the formula?

It doesn't require it. My previous version of excel required that part and out of habit I always put it