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



## zack8576 (Jan 4, 2023)

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 (Jan 4, 2023)

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 (Jan 4, 2023)

@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 (Jan 4, 2023)

Peter_SSs said:


> 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 AmorABCJK121Skimmer32a43b54Skimmer65c76d87Skimmer98e10111213Hoja3

After:
Dante AmorABCJK121(1)Skimmer32a43b54(4)Skimmer65c76d87(7)Skimmer98e10111213Hoja3


----------



## zack8576 (Jan 4, 2023)

Peter_SSs said:


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


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


DanteAmor said:


> Try this:
> 
> 
> ```
> ...


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 (Jan 4, 2023)

Peter_SSs said:


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


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


----------



## Peter_SSs (Jan 4, 2023)

DanteAmor said:


> It doesn't happen for me.


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

Before:

23 01 05.xlsmABCJK121Skimmer32a4354Skimmer657687Skimmer98e10Skimmer

After:

23 01 05.xlsmABCJK121(1)Skimmer32a43054(4)Skimmer65076087(7)Skimmer98e10Skimmer

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



zack8576 said:


> 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 (Jan 5, 2023)

Peter_SSs said:


> 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 (Jan 5, 2023)

DanteAmor said:


> 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 (Jan 5, 2023)

Peter_SSs said:


> 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  😅


----------



## zack8576 (Jan 4, 2023)

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


----------



## Peter_SSs (Jan 5, 2023)

DanteAmor said:


> It doesn't require it.


Cheers. Thanks for the clarification.


----------



## zack8576 (Jan 5, 2023)

Peter_SSs said:


> 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.


thanks Peter, BTW what does the IF({1}....) do anyways?


----------



## Peter_SSs (Jan 5, 2023)

It is a way to force a calculation to work over an array of cells but is not required for you. Therefore the code in post #3 would be a slightly simpler way for you to go as it eliminates one calculation step.
Not that important either way though.


----------



## zack8576 (Jan 5, 2023)

Peter_SSs said:


> It is a way to force a calculation to work over an array of cells but is not required for you. Therefore the code in post #3 would be a slightly simpler way for you to go as it eliminates one calculation step.
> Not that important either way though.


Thank you Peter, for all the help !


----------

