copy and paste cell with a certain clause

user_15181793 0
Viewed 42

I'm creating an excel macro that will help me feel up my entire table with the right description related to that specific opt.

At the start I have got a table that looks like this:

Product     |OPT    |    DESC_EN                 |    DESC_ITA  
____________________________________________________________________
red car     |a      |    en description opt a    |    NULL  
white car   |a      |    en description opt a    |    it description opt a          
green car   |a      |    en description opt a    |    NULL          
red car     |b      |    en description opt b    |    NULL          
green car   |b      |    en description opt b    |    it description opt b  
red car     |c      |    en description opt c    |    NULL          
white car   |c      |    en description opt c    |    NULL          
green car   |c      |    en description opt c    |    it description opt c

As you can see I'm missing some translation in the last column of the table, but as the description refers to the optional and not to the product itself,so I can take the same description from another product but with the same opt.

Example: as you can see the WHITE CAR has got the italian description for the opt a. So i can take the description from the opt a of the WHITE CAR and use it also for the opt a but for the RED CAR. The same with the GREEN CAR soo on.

In the end my table will look like this:

 Product    |OPT    |    DESC_EN                 |    DESC_ITA  
____________________________________________________________________
red car     |a      |    en description opt a    |    it description opt a      
white car   |a      |    en description opt a    |    it description opt a          
green car   |a      |    en description opt a    |    it description opt a          
red car     |b      |    en description opt b    |    it description opt b          
green car   |b      |    en description opt b    |    it description opt b  
red car     |c      |    en description opt c    |    it description opt c          
white car   |c      |    en description opt c    |    it description opt c          
green car   |c      |    en description opt c    |    it description opt c

This is the macro that i'm creating :

Sub Test3()
  Dim Var As String
  Dim found As Boolean
  Dim row_number As Integer
  Dim col_number As Integer
  
  Var = "NULL"
  found = False
  cRow = 1
  cCol = 0
  
  Range("B2").Select
  Do Until IsEmpty(ActiveCell)
    
     If Cells(cRow, 3) = Var Then
        found = True
        
        'here is where i should copy and paste the value
        
        Exit Do
     End If
     ActiveCell.Offset(1, 0).Select
     cRow = cRow + 1
  Loop
  
  If found = True Then
     MsgBox "Value found "
  Else
     MsgBox "Value not found"
  End IfEnd Sub

This is what I came up with in hours, for some reason I can't make a code that takes the value from the cell that has got the translation for that opt and paste it in the cell where the translation is missing for that specific opt. I've made a bool variable to check if my code gets the right value and it should so the only thing that is missing is that damn piece of code that should copy the description into the cell matches the opt from which he copied it.

Is this possible ? I'm taking the wrong path? I should use something different?

excel-formula vba excel
1 Answers

You've got some logical errors in your code here. For starters you've redefined your variable "Var" as empty string ("") on line 8 instead of "NULL". So your if condition isn't checking for "NULL" it's checking for empty string. I would rename this variable too so it's more readable.

Secondly, that "Exit Do" will jump out of the loop as soon as the condition is met (ActiveCell == "NULL") so you probably want that removed.

Lastly, your indexes are off by 1: Rows and Columns start at index 1 for their first member. For example, Cells(1,1) would be of value "Product".

Edit - Sorry forgot one more thing. When you're doing string comparisons it's always good to TRIM your input of whitespace. In this case the inputs are the cells.

user_46412320
Related