Forum Discussion
Automatic cell filling
Hi all, I would like to automatic fill data in columns based on data present in other cells , for example I have a long list of foods ( assume 100) and different buyers (assume 2, John & Jack), f...
Sub JohnJack()
Dim i As Long
Dim k As Long
Dim j As Long
Dim maxrow As Long
Range("E4:F1000").Clear
maxrow = Cells(Rows.Count, 2).End(xlUp).Row
k = 4
j = 4
For i = 4 To maxrow
Select Case Cells(i, 3).Value
Case Is = "John"
Cells(k, 5).Value = Cells(i, 2).Value
k = k + 1
Case Is = "Jack"
Cells(j, 6).Value = Cells(i, 2).Value
j = j + 1
End Select
Next i
End SubMaybe with these lines of code. In the attached file you can click the button in cell H2 to run the macro.
An alternative could be the FILTER function if you work with Office365 or 2021.