Posts by Rob Xaos

    Re: Macro paste special - Compile Error


    Please take note of the correct addition of [noparse]


    [/noparse] tags - see the link in my signature for more details of using tags. Also, please revise your title (by using Edit Post>Go Advanced) to better represent the actual problem you are experiencing. Your title describes a compile error but the description of your problem does not.

    Thank you.

    Re: Equal cell value (whole time) checkbox unchecked


    You must not create multiple threads for the same problem. Your other threads have now been closed. Until now you have not mentioned a command button. It is not possible to provide a solution unless you explain your problem clearly.

    You require code for the Command button, not for the Checkbox.

    Private Sub CommandButton1_Click()
        If CheckBox1 Then
            [8:8].EntireRow.Hidden = True
            sheet2.Cells(1, 1).Value = 0
            [8:8].EntireRow.Hidden = False
            sheet2.Cells(1, 1).Value = sheet1.Cells(8, 2).Value
        End If
    End Sub.

    Re: Equal cell value (whole time) checkbox unchecked

    I'm sorry but your description does not make it clear what the problem is. Can you explain what you want to happen when Checkbox1 = True and when Checkbox1 = False.

    As it is written your code would be easier to understand if written as follows:

    Re: Superscripting Alphabets in Selected Cells


    Ozgrid forum rules permits one question (or close follow-ups) per thread, by the original poster only. Please start your own thread and provide a link back to this thread if required.

    Thank you.

    Re: Clear dependent cells after update

    Changing a validation drop-down will trigger a Worksheet_Change event. You can use that to process clearing of the 'lower order' drop-downs. Without a bit more detail its not really possible to give you an example in code. However the basic technique is to test Target (which is a parameter passed in by Excel when the Worksheet_Change event occurs) to see if its address or range corresponds to the cells you are checking, then run the appropriate code to clear your drop-downs. The Worksheet_Change event will reside in the code sheet for the Worksheet containing your drop-down cells.

    Re: Print current and next page using VBA

    The property you want is:


    Which gives you the number of the current page. You can simply add 1 to this to get the next page and then use wdPrintFromTo or wdPrintRangeOfPages.

    Re: Intermitent Macro Run Time Error '-2147417848(80010108) Automation Error Object..

    I had a little dig around for you and found this thread that may be relevant:…spx?scid=kb;en-us;Q319832

    Towards the bottom there are some suggestions to avoid these issues:

    Re: Using Variables in Networkdays



    Where C1 is the reference to your 'Cell 1' where you select your location.

    Re: operations with large numbers

    The problem will be that once you have imported the number into Excel 'as a number' then the digits will already be lost. You have not said where the data is being imported from but I think you will need to import them initially as text, that will at least preserve every digit.

    You can look at Converting Large Numbers to Binary to see if the UDF there is of use.

    Re: Copy lines with countif ans sumif problems



    When using $ to flag a cell identifier as absolute, you need to use it for each part of the identifier so if you require L8:L20 to be completely absolute (ie none of the index parts change when copied) then you need to use $ in front of the Column and Row index of both parts of the range reference.

    Re: Intermitent Macro Run Time Error '-2147417848(80010108) Automation Error Object..

    It only matters which machine is running them. Filesearch does not exist under 2007 so that part of the code will not work when run on a machine with Excel 2007 even if the Workbook is run in compatibility mode.

    Unless you (or the creator of the macro) have added one of the Filesearch user-class implementations then the code as shown cannot run on a machine with 2007. If there is one of the user-class implementations included with the above VBA code then the problem may stem from that.

    Re: Link Date in Excell cell to Outlook Calendar


    Ozgrid forum rules allow only one question (or very close follow-ups) by the original poster only per thread. Please start your own thread for your question and if it helps to explain your problem then link back to a previous thread.

    Thank you

    Re: Intermitent Macro Run Time Error '-2147417848(80010108) Automation Error Object..

    With Application.FileSearch

    Filesearch is no longer supported by 2007. I cannot explain why you seem to suggest that it works for some 2007 users but FileSearch cannot work with 2007 unless it is replaced with a user-class.

    There are a number of threads suggesting alternatives for Filesearch on Ozgrid and a search simply for 'Filesearch 2007' should give you a list.

    Application.FileSearch not working in MS Office 2007 Has a link to another site with a thread that contains a user-class replacement. However you may want to look at some of the other threads as they may contain a simpler replacement.

    Re: Save entire workbook as web page without interactivity but with autorepublish

    My understanding is that Autorepublish is for the whole Workbook when it is saved. The Autorepublish is part of the Save process after you have set it up to Publish. If it is not republishing when you save then it suggests something may be wrong with how the Publish has been set up or with Excel's access permissions to the web-page.

    Re: Calc Difference between two date/times (workdays only) shown in days hh:mm or hh:


    AA2 =IF(M2-E2<1,"",NETWORKDAYS(E2,M2,holidays)-IF(M2-E2<1,2,1))&" "&TEXT(M2-E2,"h:mm")

    This should work providing you have a named range called 'holidays' with your list of public holidays and your call raised and action taken hours are always within your working day. If however your call is raised say at 08:00 but answered at 23:00 the same day then that would show as 15:00. The formula does not take into account the working hours. The NETWORKDAYS function assumes weekend to be Saturday & Sunday.

    Re: Countif formula for multiple cells and sheets


    The posting of your code does not correctly use [noparse]


    [/noparse] tags (see the link in my signature for correct usage of tags) and the newline characters used have not translated to the forum which makes it impossible for me to easily add the tags for you.

    You will need to Edit Post on your post above to correct this.

    Re: How to add every nth column in a table

    S3: =SUMPRODUCT(($B$2:$Q$2=S$2)*($B3:$Q3))

    You can then Autofill that across your 4 columns and then down as far as required. It requires the data header columns on the left to exactly match the ones in your monthly summary.

    As a side note I would recommend that you do not use Merge and Center for your date headings. Instead repeat the date for each column. Later if you wish to try to analyse for a specific date you will not be able to do so easily with the date headings the way they are. If you want a neat 'heading' then construct that in a separate row, you can always hide the row with the multiple dates.

    Try entering the following two formula in any cell and you will see an illustration of the problem:


    The first returns your date from B1 (1-Oct)
    The second returns 0

    If you want to try to look up products against dates then using Merge and Centre in this way makes it hugely difficult.