Posts by Rob Xaos

Re: Macro paste special - Compile Error

Lee,

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

Grca,

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

Private Sub CheckBox1_Click()


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

Re: Superscripting Alphabets in Selected Cells

cowboy123

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:

Selection.Information(wdActiveEndAdjustedPageNumber)

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: http://support.microsoft.com/d…spx?scid=kb;en-us;Q319832

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

Quote

What to Do If You Experience These Problems

If you have an existing project that no longer works because of one of the problems in this article, here are a few things you can do to fix it:

• Make sure that you set Option Explicit at the top of each file in your project. This setting makes sure that VB generates a compile-time error and that VB does not automatically use a zero value for constants that it does not recognize.
• If the project is relatively simple, or if the Office Automation code is brief, remove the Office type library from your list of references, and then recompile the project. You then receive errors about unknown types for each object declaration that you used for an Office type. Change these to type Object. You may also have to change any New statements to use CreateObject instead. If you use any enumeration values or constants from the Office type library, you have to replace them with their numeric value. After you have finished that, the only compile errors that you receive are for unqualified methods. Find each error, and then qualify the call with the application or document object, as appropriate.
• If the program is complex, try to find the unqualified calls by explicitly forcing the remote Office application to quit after the code runs for the first time. If the code explicitly creates a new instance each time, it should be able to run even if the previous instance was ended. To do so, follow these steps:
  
  • Run the code one time to make sure it runs the first time (even if unqualified).
  • Check the Processes list in the Task Manager window, and then quit the instance of the Office application that you started during the first run.
  • Rerun the code with VB set to Break on All Errors. If VB breaks and displays Error 462 (or Error -2147417848) during the second run, that line probably contains an unqualified method or property call.

  
  

NOTE: Unqualified calls may appear as parameter values to a function or a property that is itself fully qualified. Therefore, examine carefully the parameters that you are passing, especially when you use a named-argument syntax. After you find an unqualified object or function that is not native to Visual Basic, qualify it with the variable name of the Application object that you used to start the Office application.

Display More

Re: Using Variables in Networkdays

Try:

NETWORKDAYS(D1,D2,IF(C1="location1",INDIRECT("holidaylist1"),INDIRECT("holidaylist2")))

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

Re: Using Variables in Networkdays

Are you calling Networkdays from VBA or as a formula in Excel?

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

Ahhh, ok. Now its clear!

Are the new workbooks being loaded as .xlsx in 2003 Compatibility mode? If you open one manually in 2003 does it give any warnings during opening?

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

Try:
=COUNTIF($L$8:$L$20,G25)

and
=SUMIF($L$8:$L$20,G25,$K$8:$K$20)

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: UDF stopped change event from working

A UDF called from a cell in a Workbook cannot directly modify the workbook other than by returning a value to the cell from which it was called. I believe the issue is with the way you are using the UDF rather than the Change event.

See: http://www.ozgrid.com/VBA/Functions.htm#UDFIntro for the limitations of a UDF.

Re: Link Date in Excell cell to Outlook Calendar

Ziggy12,

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:

Try:

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

L7B

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:

=B1
=C1

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.