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