lundi 19 juillet 2021

paste values if cells match

In the previous post you guys helped me to find out a solution in order to copy-paste cells.

By now I've got a slight different problem.

Here is it; I've got 2 different sheets;

  • worksheets("Food")
  • Worksheets("Numbers")

In worksheets("Food"), I've got the following board;

| Fruits            | Vegetables    |
| --------          | --------------|
| Banana            | Carrots       |
| Peach             | Spinachs      |
| Pineapple         | Cauliflowers  |

In worksheets("Numbers"), I've got this;

| Fruits        | Numbers   |
| --------      | --------- |
| Banana        | 9         |
| Apple         | 2         |
| Orange        | 3         |
| Peach         | 7         |
| Pineapple     | 5         |

I'd like to search for each fruits from worksheets("Food") if they do exist in worksheets("Numbers"). If yes, then automatically insert a new column in worksheets("Food") between column Fruits and Vegetables named "Numbers". After, picked up numbers beside each found fruits in worksheets("Numbers") and paste it in worksheets("Food") beside the matching fruit in the new created column.

Like this;

| Fruits     |*Numbers*      |Vegetables
| --------   |-------------- |------------
| Banana     |**9**          |Carrots
| Peach      |**7**          |Spinachs
| Pineapple  |**5**          |Cauliflowers  

I've been trying to run a code doing this process but as I run it nothing happens ( no error occurs neither)... Here is it;

Sub Add_Fruits_Numbers()

Dim lastlineFood As Long
Dim lastlineRef As Long
Dim j, i, compteur As Integer
Dim x As Long, rng As range

lastlineRef = Worksheets("Numbers").range("A" & rows.Count).End(xlUp).row

For j = 1 To lastlineRef
    lastlineFood = Worksheets("Food").range("A" & rows.Count).End(xlUp).row
    
        For i = 1 To lastlineFood
        If range("A" & i).Value = Worksheets("Numbers").range("A" & j).Value Then
        
            Set rng = Worksheets("Numbers").range("A1", range("A1").End(xlToRight))
                For Each cell In rng
                    If cell.Value = "Fruits" Then
                        cell.EntireColumn.Offset(0, 1).Insert (xlShiftToRight)
                    End If
                Next cell
            
        
            Worksheets("Food").range("A" & i).Offset(, 1).Value = Worksheets("Numbers").range("A" & j).Offset(, 1)
        End If
        Next i
        


Next j
End Sub

I'd heavily appreciate your help once again, thank you !

Aucun commentaire:

Enregistrer un commentaire