[Solved] Add data to last empty column

[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 :

 

[Mashhura Jahan]

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

Hello bigme,
Thank you for your query. I have created the following Macro for your example data.

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

 

[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

Hello bigme,
Thank you for your query. I have created the following Macro for your example data.

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

View attachment 39
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

 

[shamimarita]

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

Dear Bigme,

Thanks for your appreciation. Regarding any Excel-related issues we are here to help you.