| Free Excel Training Videos | |
Macro in Excel for inventory solution | |
| How to creata a macro in Excel for inventory solution: Today a finance person approached me. He had overheard me saying that I train people in Microsoft Excel while I was having lunch at the Coffee shop in the Savitri Cinema Complex in Greater Kailash 2. He wanted to learn macros in Excel. He had 2-3 hours time in the next few days! Could I give him one trial class. This guy was really a personality. I told him that a trial class was not possible and learning macros in Excel in 2-3 hours without any knowledge of Visual Basic for Applications would be difficult. He insisted that he wanted at least one class. OK, I said but this class is going to cost you. No problem he said. After 2 or 3 days I received a call that he would like to take a class tomorrow and whether I would be available at 11 a. m. I was free and I told him to come. When he arrived I started to explain to him what macros are and how we should start. He said he had a specific problem and whether I could help him find a solution. Of course, I said. I would not like to tell my real thoughts at that moment. He wrote his data which included product name, lot no, purchase price, projected closing balance, etc. I wrote a macro using a 'do while loop'. I could make out that he did not understand a word. But when we ran the macro he was happy with the result, Next he wanted to know how to create a 'button' which when clicked would produce the same result. Although I could see that he understood nothing, he insisted that I go ahead. I had no problem because I thought I could create a video in the process and share with all people who have a basic understanding of macros or those who wish to learn macros. But the solution here is not for somebody who has never used a macro or Visual Basic for applications. Relax and watch the video and don't forget to send me your comments please. The macro code: Private Sub MYBUTTON_Click() 'WE FIRST DEFINE THE STARTING ROW OF OUR ACTION Row = 2 'NEXT WE DEFINE A VARIABLE TO CAPTURE THE TOTAL total = 0 'NOW WE RUN THE LOOP BECAUSE WE HAVE LOTS OF DATA ROWS 'WE ALSO DEFINE WHEN THE LOOPING SHOULD STOP A$ = InputBox("Enter the item no.") Do While Cells(Row, 1) <> "" If Cells(Row, 1).Value = A$ Then total = total + Cells(Row, 8).Value End If Row = Row + 1 Loop Worksheets(2).Activate erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Worksheets(2).Cells(erow, 1) = A$ Worksheets(2).Cells(erow, 2) = total End Sub | |
| Macro in Excel for inventory solution |