0

I want to say that if in the one cell (i,2) contains the text "Lala" and another cell (i,9) contains the text "Active" that it should copy the content of the cells 3 and 4.

Unfortunately my code is not working with the second condition - without it's running. So can anyone tell me how to write it correctly?

If there stands "Deactive" it shouldn't copy the content.

If Cells(i, 2) = "Lala" And Cells(i, 9) = "Active" Then         
    Range(ActiveSheet.Cells(i, 3), ActiveSheet.Cells(i, 4)).Select         
    Selection.Copy

SOLVED! Solution is:

If ActiveSheet.Cells(i, 2).Value = "Lala" And ActiveSheet.Cells(i, 9).Value = "Active" Then        
Range(ActiveSheet.Cells(i, 3), ActiveSheet.Cells(i, 4)).Select
Community
  • 1
  • 1
user3420083
  • 15
  • 4
  • I just reprogrammed your code with an example and it works as it should, even when the value in the cells are computed with a formula. – Florian Schmidinger Jan 09 '15 at 10:07
  • There's anything wrong with your If statement. What do you mean "is not working"? It doesn't enter the If when you expect that it should enter it ? – Matteo NNZ Jan 09 '15 at 10:07
  • 2
    I may suggest a couple of things: 1) reference the worksheet, so `ActiveSheet.Cells(i,2)` instead of just `Cells(i,2)`, and use the value property, so `ActiveSheet.Cells(i,2).Value`. However, if you are running the macro from the ActiveSheet, this is equivalent to your current code so you might want to specify better what you mean with "it's not working". – Matteo NNZ Jan 09 '15 at 10:10
  • What are you trying to do with the data once it's copied? Because if you are just going to "paste" it somewhere else, You don't need to copy it and paste it. You can just set the values of the destination. Check out this link: [How to Avoid using select statements](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Is the destination always going to be the same? If it changes, what causes it to change, or where relatively does it go? – peege Jan 09 '15 at 10:19
  • @matteo NNZ I just replaced Cells(i,2) with ActiveSheet.Cells(i,2).Value and now it works. Thank you! With "it is not working" I have meant that the content where a cell contains "lala" wasn't copied in the other xlsm-document. Before I only had If Cells(i, 2) = "Lala" - it worked! But then I have added the "And-statement" and nothing was copied. But like I said above, with ActiveSheet.Cells(i,2).Value it is working (content has been copied). – user3420083 Jan 09 '15 at 10:31

1 Answers1

0

'try this
'if cell contain required data then copy range

If Cells(i, 2).value like "*Lala*" And Cells(i, 9).value like "*Active*" Then         
    Range(ActiveSheet.Cells(i, 3), ActiveSheet.Cells(i, 4)).Copy
End if
Vasily
  • 5,707
  • 3
  • 19
  • 34