VBA replace if

[kdibai]

Hello guys! VBA is not my streght and I need to improve a process using it. Someone can help?


the spreadsheet has this 3 fictitious columns:


APPLE FRUIT ORIGEM1

LEMON FRUIT ORIGEM2


I need to replace the "ORIGEM1" in cell C1 to "ARGENTINA" if A1 is an APPLE. Same for the LEMON, where I need to replace C1 to "SPAIN", if A1 is a LEMON.


That's it!

 

[Hui]

Kdibai


I'd use a helper column

=If(and(C1="ORIGEM1",A1="APPLE"),"Argentina",if(and(C1="ORIGEM1",A1="LEMON"),"SPAIN",C1))

Then copy down

Then copy Column D and Paste as Values over itself

Then delete Column C

 

[kdibai]

Ty for the help, Hui! But the choice for a VBA solution is because there will be 23 conditionals (Excel doesn't allow that much conditionals in a IF formula, right?). And to automate it, as this will be performed 3 times a day, everyday.


Is there a way to do in VBA?

 

[sdsurzh]

Hi Kdibai,


Please insert a module and paste the below code. (This is fixed code as per my knowledge)


Sub findreplace()

If Range("a1").Value = "APPLE" Then

Range("c1").Value = "ARGENTINA"

If Range("a2").Value = "LEMON" Then

Range("c2").Value = "SPAIN"

End If

End If

End Sub


Thanks,

Suresh Kumar S

 

[kchiba]

Hi Kdibai,


Why not create a table of values with two columns as follows


Apple Argentina

Lemon Spain


and then use the VLOOKUP formula in the column where you have ORIGEMI1, to find APPLE and place Agentina in the cell.


This will be more dynamic and manageable then VBA


cheers


kanti

 

[kdibai]

Suresh, that's it! I just modified the range and it worked! Thanks


Kchiba, I needed something automatic and faster, that's why... The spreadsheet has a lot of sumproduct and vlookup already and it's huge. It would increase the waiting time for calculation.


Many thanks all for the interest!