Posts by anshulgupta28

    Hello Experts,

    I would like to know how can I create a dynamic named range in between a set of data in Excel such that it should count the non-empty row before a row that has particular data value in it.

    Seeking your help. Please advise. Thanks in advance.

    Thank you so much. How can we restrict the worksheet_change to run only when 'code' and 'price' column value changes.

    I tried putting if statement in line 2 like

    Thank you so much Fuff13 for introducing me to this concept. Very helpful.

    There is one doubt I would like to clear, I get an error: "Runtime Error '1004' : Method 'Range' of object '_worksheet' failed." whenever there is no value in the named range and I try to enter first value. Please let me know how I can resolve such error. Is there any error handling method for it!

    Thanks for your reply. It's working fine now.

    But I have a problem in advanced filter. It is copying duplicated data from named range. I see that 1st row data of named range "test" is compulsorily getting copied as shown in below workbook. Please help.

    Also, I would like to add the 'Price' column for all unique value in code column and display it in filtered range. How do I do that.


    • Test.xlsm

      (16.56 kB, downloaded 94 times, last: )

    Hello Expert,

    I am trying to copy the unique value from a dynamic named range to a range in a worksheet using advancedfilter method every time when value is changed or new data in added to named range. I am facing two problems.

    1. Every time when new data is entered in named range : 'Test', it gives me runtime error i.e. runtime error28 : Out of stack space
    2. In filtered result, I want to copy a sum of data corresponding to unique value of named range 'Test' present in column 'Price' of the table as shown in attached workbook. Any lead to achieve it would be helpful.

    Please help me resolve this error, and achieve above functionality. Thanks in advance:)


    • Test.xlsm

      (16.27 kB, downloaded 99 times, last: )

    Hello Experts,

    There is an Run-time error '2147024773 (8007007b) Document not saved while trying to export excel as PDF. Please help me resolve this problem. Attaching code below.

    Sorry to say but Option button is always present in userform : Form_new_invoice.

    I am doing it for my personal use and simultaneously utilizing this lockdown period for learning vba concepts.

    After many tries no luck thus I am seeking help here.

    Thank you for all your help Roy:)

    Roy, Sorry to bother you. I will try to explain the scenario.

    There are two invoices based on the type of trade. If trade is within the state its called Intrastate trade and for outside the state trade its called interstate trade. Taxes law are different for both types. Hence, I have two invoices.

    "rBtn_inter" control is the option button control present in form : "Form_new_invoice" which is the first stage where user have to choose the type of trade for which invoice has to be made. Thus, if user opted for intrastate I have to make and view the CGST invoice else if user choose interstate I have to make and view the IGST invoice respectively. Therefore, I used your code twice.

    Also, I observed while testing and debugging the same case in another workbook attached below: 'simple-invoice-template(02) (Recovered)', it throws an error when the form button is not present in the same sheet as the viewing invoice i.e. clicking on 'Preview Invoice' control in worksheet 'Blank Invoice' gives me a blank image in image control(it should the invoice present in worksheet 'blank invoice') of userform whereas It is working fine on clicking the 'click' control present in worksheet 'sheet1'. (Buttons are assigned to macro used to view invoice present in worksheet 'sheet1')

    I see it throws error - runtime error : '91' 'object variable or with block variable not set' in the line 'activechart.paste'. Please suggest what can be done here.

    Hello Roy! Thanks for your prompt reply. But It is not working for me in my workbook, I tried to test and debug but no luck.

    I am attaching my workbook.

    I will explain you a scenario a bit. There are two type of invoice designed based on type of trade happening. So, I have put a If-ElseIf condition in initialization of userform : 'Form_ViewInvoice' for viewing invoice accordingly. Request to please check and help me out to debug the problem. Thanks in advance.

    Navigating through user forms like

    Form_new_Invoice ----> Form_New_Invoice1------> Form_Additem ------ > Form_new_invoice2 ------ > Form_ViewInvoice


    • Sample.xlsm

      (139.04 kB, downloaded 90 times, last: )

    Thank you, Roy! It worked for me.

    But I am getting runtime error : '91' 'object variable or with block variable not set' after first run at line :

    (ActiveChart.Export TempFldr & Application.PathSeparator & "TempPic.bmp", "bmp").

    I can't understand why is it happening. Each object is defined and referred properly. Please suggest.