Jump to content


Photo

Very Specific Macro Question

macros relative references absolute references tables

  • Please log in to reply
9 replies to this topic

#1 ShawnR

ShawnR

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 08 August 2015 - 04:07 PM

I have a worksheet that I use to keep track of some inventory. I enter products onto the sheet when I receive them and I have a column for quantity so I can see how many of each product I have. When I sell a product, I keep track of that on the same sheet. I do this by subtracting the sold product from the quantity and then recording that it was sold on the same row (this record includes a customer name, and sales tax information). When I have a product that has a large quantity, like 50, if I sell 2 of that product to 25 different people, I end up duplicating that row of the table 25 times and recording the sale to each customer with their sales tax info on each row. This happens over the period of a month at a time, so I only add a few transactions each day, not all 25 at once. I know there may be better ways to manage my inventory, but that is a subject for another day.

 

So the question is this. I want to make a macro in excel that will do the following. I place the cursor on the row of the product I am selling and click on the cell in the first column. When I run the macro, it should create a blank row above the active cell. Then copy several cells from the row I started on to the new blank row just above. The quantity on the original row should be changed to 0 and then the active cell should be placed on the quantity of the new row.

 

From there I would manually change the quantity to the right number and finish the row by adding the new sale and customer sales tax information.

 

On my desktop computer, I have a keyboard that records macros and I can accomplish this task easily. I am trying to move over to a notebook and cannot make this work properly. I have tried recording a macro with relative reference and the problem seems to occur with creating the new blank row. 

 

If anyone can help out with this I would appreciate it.



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 08 August 2015 - 06:11 PM

Hi Shawn,

 

I think you should be able to achieve what you need by recording a macro with Relative References. One thing that may help could be to try recording all of the commands the macro needs by using the keyboard instead of the mouse. Changing cells using the mouse when recording macros often produces inconsistent results, so it's better to navigate and select cells using the arrow keys on the keyboard if at all possible.

 

You can see an example of selecting cells using the keyboard in the video lesson: Essential Skills Lesson 2-4 Select a range of cells and understand Smart Tags.

You can find a walkthrough of recording macros with Relative References in the video lesson: Expert Skills Lesson 9-15 Record a macro with relative references.

 

I have attached an example workbook with a recorded macro that performs a similar task:

 

Attached File  RelativeMacro.xlsm   15.36KB   74 downloads

relativemacro.png

 

The macro in this workbook will insert a new row in the area that is selected, copy the B C and D values from the row below and set the A value to 0.

 

You'll also notice that I have created a button that will run the macro when clicked. You can see how to do this in the video lesson: Expert Skills Lesson 9-16 Use shapes to run macros.

 

I hope this is what you were looking for, but please feel free to reply if there are any details that you're still having trouble with.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 ShawnR

ShawnR

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 13 August 2015 - 12:49 AM

Thanks Jonathan. I do appreciate your quick reply and your sheet performs exactly how I would want mine to perform. I looked at the code behind your macro and it is different from the way my macro turns out when I record it.

 

Let me walk through the steps I take and maybe you can identify what I am doing wrong.

 

I place the active cell on the first cell of the row I want to copy. I open the macro dropdown and select relative recording to enable it. Then I start recording the macro. I press "ctrl +" to insert a blank row above the active cell. Then I hold shift and press the right arrow several times to highlight the cells I want to copy. I press "ctrl c" to copy. I move my active cell up to the first cell of the new blank row and press "ctrl v" to paste. Then I press the right arrow key several times and the down arrow once to get to the cell I want to make "0". I enter the "0" and then press up one time to locate the active cell where I intend to edit once the macro is finished. Now that the macro is recorded I press stop.

 

I am thinking that my problem may be the keyboard shortcut I am using to insert the row. It seems like the row insert is absolute and not relative, but then the rest of the macro (copy and paste) is happening in a relative way.

 

Thanks again,

 

Shawn



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 13 August 2015 - 10:30 AM

Hi Shawn,

 

I have tested this myself using the Ctrl-+ shortcut key and it seems to work as it should, so I'm not quite sure why this isn't working for you.

 

Here are the steps I followed:

  • Select a cell in column A.
     
  • Click Developer > Code > Use Relative References and make sure that it is lit.
     
  • Click Record Macro, enter a name for the macro and start recording.
     
  • Hold down the <Shift> key and use the right arrow key to select all of the columns I need.
     
  • Press <Ctrl>-<+>, select Shift cells down and click OK.
     
  • Enter 0 into the first cell on the new blank row.
     
  • Use the arrow keys and shortcut keys to copy and paste the values from the row below.
     
  • Stop recording.

I suspect the problem may be that you haven't correctly enabled the Use Relative References option. You can review how to do this in the video lesson: Expert Skills Lesson 9-15 Record a macro with relative references.

 

I hope you're able to get it to work, but please reply if you're still having trouble and I'll try to offer more advice.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 ShawnR

ShawnR

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 14 August 2015 - 12:29 AM

I am enabling the relative references. It is incredibly hard to tell that it is enabled, but I have crossed that bridge and I do have that part right. The biggest difference I see in your instructions and what I have tried is this. I am creating the new row first, by pressing "ctrl +" before copying the data. You, apparently are copying the data and then creating the row, which leads to the dialog box asking how to shift the data. I just tried your method and received an error. It appears that method is not allowed within a table. I have not mentioned before that this data is all stored inside of a table. I didn't think that info was relevant.



#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 14 August 2015 - 05:20 PM

Hi Shawn,

 

I have taken a look and it looks like the Table is indeed what's causing the trouble, specifically the command to insert a new table row doesn't seem compatible with relative references. This seems to be a bug in Excel rather than anything you're doing wrong.

 

You can work around this by inserting an entire worksheet row instead of just inserting cells into the Table. You can do this by right-clicking on the row number and then clicking Insert from the shortcut menu.

 

Inserting rows is also covered in the video lesson: Essential Skills Lesson 3-1 Insert and delete rows and columns.

 

I have attached another example workbook with the macro working on a Table:

 

Attached File  RelativeMacro.xlsm   16.53KB   131 downloads

RelativeMacro2.png

 

You can also see more about Tables in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#7 ShawnR

ShawnR

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 17 August 2015 - 11:52 PM

I have been trying this method that you described, Jonathan, and I am happy to report that it is working just as I expect it to now. Who would have thought that the keyboard shortcut for it would provide different results. Thanks a bunch for sticking with me and helping me to solve this issue. I can't tell you how much I appreciate it!



#8 ShawnR

ShawnR

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 01 September 2015 - 08:36 PM

Jonathan, I have one final question regarding my macro usage.

 

My macros had been working fine until today and only one major thing has changed. As we enter a new month, I move all of my unsold product onto a new worksheet (tab). So at the bottom of my screen I have tabs for each month and today I created a new tab for September by making a copy of August and then deleting all of the products with a quantity of 0. It's a sort of reset for the new month that I do so I can keep track of sales tax.

 

Anyway, the first time I ran my macro to create a new line, I got a runtime error. Do you think this is related to copying the sheet and if so, will I just have to remake the macros each month when I copy it, or is there a way to make it just work without having to redo it over and over.

 

Thanks again for your help,

 

Shawn



#9 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 866 posts

Posted 02 September 2015 - 11:24 AM

Hi Shawn,

 

I've tried doing the same thing with my example workbook, and I do get the same "Application-defined or object-defined error".

 

It seems this goes back to the data being inside a Table - since each Table has a unique name, the macro gets confused when the Table on the new sheet doesn't have the same name as the one on the original sheet.

 

I'm sorry I don't have a better answer, but it seems this is a shortcoming of macros when working with Tables. Microsoft might improve this behaviour in a future update, but for the moment I'd suggest either recording a new macro for each sheet or using a standard Range for your data.

 

I did have an idea that you might be able to get it to work by converting the Table into a Range and back again while recording the macro, but I couldn't get it to work in practice.

If you do want to try this, you can find the lesson on converting Tables into Ranges in: Expert Skills Lesson 1-12 Format a table using table styles and convert a table into a range.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#10 ShawnR

ShawnR

    Newbie

  • Members
  • Pip
  • 8 posts

Posted 02 September 2015 - 10:59 PM

Thank you for your prompt and honest answer. I just wanted to make sure I wasn't missing something. I'll just re-record the macro for now. It's not a hard macro to record at least.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users