Hello bigme,Good Morning,
i have a table and every day i do manual vlookup every time there is a new data, please help me to create a macro so when new data arrive, i just run the macro.
thank you for the help.
example data :
View attachment 38
Sub add_data()
Set my_rng = Selection
Set rem_qty_cell = Sheet2.Range("I4").Offset(0, -1)
If Sheet2.Range("I4").Value = "" Then
Set act_cell = Sheet2.Range("I4")
ElseIf Sheet2.Range("I4").Offset(0, 1).Value = "" Then
Set act_cell = Sheet2.Range("I4").Offset(0, 1)
Else
Set act_cell = Sheet2.Range("I4").Offset(0, 2)
End If
For i = 0 To Sheet2.Range("F4:F13").Rows.Count - 1
result = Application.WorksheetFunction.IfError(Application.VLookup(Sheet2.Range("F4").Offset(i, 0).Value, my_rng, 3, False), 0)
act_cell.Offset(i, 0).Value = result
remaining_qty = rem_qty_cell.Offset(i, 0).Value - _
(Sheet2.Range("I4").Offset(i, 0).Value + Sheet2.Range("I4").Offset(i, 1).Value + Sheet2.Range("I4").Offset(i, 2).Value)
Sheet2.Range("L4").Offset(i, 0).Value = remaining_qty
If remaining_qty < 0 Then
act_cell.Offset(i, 0).Value = result + remaining_qty
Sheet2.Range("L4").Offset(i, 0).Value = 0
End If
Next i
End Sub
Hello bigme,
Thank you for your query. I have created the following Macro for your example data.
View attachment 39Code:Sub add_data() Set my_rng = Selection Set rem_qty_cell = Sheet2.Range("I4").Offset(0, -1) If Sheet2.Range("I4").Value = "" Then Set act_cell = Sheet2.Range("I4") ElseIf Sheet2.Range("I4").Offset(0, 1).Value = "" Then Set act_cell = Sheet2.Range("I4").Offset(0, 1) Else Set act_cell = Sheet2.Range("I4").Offset(0, 2) End If For i = 0 To Sheet2.Range("F4:F13").Rows.Count - 1 result = Application.WorksheetFunction.IfError(Application.VLookup(Sheet2.Range("F4").Offset(i, 0).Value, my_rng, 3, False), 0) act_cell.Offset(i, 0).Value = result remaining_qty = rem_qty_cell.Offset(i, 0).Value - _ (Sheet2.Range("I4").Offset(i, 0).Value + Sheet2.Range("I4").Offset(i, 1).Value + Sheet2.Range("I4").Offset(i, 2).Value) Sheet2.Range("L4").Offset(i, 0).Value = remaining_qty If remaining_qty < 0 Then act_cell.Offset(i, 0).Value = result + remaining_qty Sheet2.Range("L4").Offset(i, 0).Value = 0 End If Next i End Sub
You can copy this code and then modify it according to your real dataset. Then, select the new data range and run the code and you will get your desired result. I have also attached the Excel file here. I hope this will solve your problem. Please let us know if you face any problems.
Regards
Mashhura Jahan
ExcelDemy
Dear Bigme,Dear Mr. Mashhura,
Thank you for your help, i've tried and it's work fine as i expected. Your code will save me a lot of time.
Best Regard,
BigME