Posts by creamyegg

    Re: Find, Copy from Word to Paste in Excel

    Here is part of your solution. This will search for your value and copy the entire sentence the value was found in ready to be pasted to where ever you like.

    Re: Run-time Error '1004' : Autofilter Copy Paste Error

    No problem, it should look like this:

    To explain the code, it simply puts the data from the filter on Sheet 2 starting at range A4:
    .Cells(4, 1) = A4
    .Cells(rngSelection.Rows.Count + 3, rngSelection.Columns.Count) = C7
    When 'copying' data between ranges like this, we need to provide a reference to the entire range to 'paste' to. If not, then we will truncate the data (or if the range given is larger then results in #N/A values).

    Re: Run-time Error '1004' : Autofilter Copy Paste Error

    In all the years I've been writing VBA, I've never used Copy & Paste. Something like the following should help:

    With Sheets("Sheet2")
            Range(.Cells(4, 1), .Cells(rngSelection.Rows.Count + 3, rngSelection.Columns.Count)).Value = Rng.Value
        End With

    Re: Run-time Error '1004' : Autofilter Copy Paste Error

    Your best bet is something like this where we simply assign the visible cells to a Range object and then test if the Range object was set.

    Re: Execution branches unexpectedly to UDF when stepping through

    HI Zwana, welcome to OzGrid.

    My best guess is that upon opening the text file, All open workbooks are recalculated (as I believe is the default behaviour). As your UDF is in a seperate Workbook (and refererenced in another different Workbook), it is automatically flagged as Dirty and so will be recalculated too. Naturally this would only be obvious when stepping through the code.

    Having a play with the Application.Volatile() method in your UDF may help.

    Re: Reference a range with ThisWorkbook

    Not quite sure what you are attempting to do but you can't 'Select' a range unless the range is active. This even includes if the range is in the active workbook but NOT the active worksheet. You can of course just reference a cell using ThisWorkbook when ThisWorkbook is not active.

    For your fourth code example, try this instead:

    ThisWorkbook.Sheets(1).Cells(2,2).Formula = "=5"

    Re: ConvertAccessProject from Excel - Visible = False

    Unfortunately, that doesn't work completely either, nor does the 'ShowWindow()' API function.

    It appears these API functions do prevent the MS Access window from being displayed but for some reason, other apps still lose focus which is the most annoying thing.

    It looks as if I'm just going to have to make do with this behaviour.

    Re: ConvertAccessProject from Excel - Visible = False

    I'm now playing about with Shell() to see if this could be a solution. Does anyone know if it is possible to run the


    procedure in Excel using a Shell command? I'm thinking something like:

    VBA.Shell("MSAccess!ConvertAccessProject('" & strDbPath & "', '" & strNewDb & "', " & 12 & ")", vbHide)

    Re: ConvertAccessProject from Excel - Visible = False

    Quote from Aaron80;587476


    ~Begin of Code
    Application.ScreenUpdating = False
    ~~your code~~
    Application.ScreenUpdating = True
    ~End of Code

    Just make sure you turn screen updating to true before the user needs to give any input.

    Thanks for the suggestion but screen updating is already set to False in the calling procedure so it doesn't make any difference in this instance. Just to clarify, this is the Access application window that is shown so I wouldn't expect changing anything in the Excel VBA object model would prevent this.

    I have the following Excel function which works as expected, except for one detail. on the .ConvertAccessProject() line, the MS Access application always becomes visible again and takes focus, just for a second or two. This is obviously very annoying especially as the users should not be aware that Access is being opened at all. Any ideas on how to truely make the Access application NOT Visible?

    I've tinkered with the API but this doesn't seem to help either.

    Any help would be greatly appreciated.


    Re: Decide If A Cell Is Empty, With Filter

    I assume the problem here is that when you filter for week 22, no data is displayed so you don't want anything to be copied? If this is the case, then I think you need to test to see if the row is hidden or not. For example:

    With ActiveSheet.Range("A1").Offset(1, 0)
            If Not (.Rows.Hidden) Then
                Debug.Print .Value
            End If
        End With

    Re: Totalling Hours Per Day

    If you can have the dates going all the way down column A (for all the times for 1 date for that date to be in column A) this would be far easier. You could probably then simply use the SubTotals tool.

    Re: Something Similar To A Case Statement

    Not exactly sure what you are trying to do but I think an array formula (when entering the formula, hold down <CTRL> + <SHIFT> instead of pressing <ENTER> to add it as an array formula) maybe the way to go. For example this may do the trick:


    Here this counts the number of cells in the range A1:L1 that have values that are in the range N2:N13. For example, if N2:N13 is a,b,c,...l and A1:L1 is a,b,c,...l then 12 is returned. If A1:L1 has say x instead of b, then 11 is returned. If A1:L1 does not contain any of the letters in the range N2:N13, then 0 is returned. If you wanted to return TRUE/FALSE you could then simply add an <> operator:


    This would return TRUE if any of the values in the range N2:N13 are found in the range A1:N1.

    I hope this all makes sense and is actually what your looking for!

    Re: Worksheet Variants

    Cheers for all your input, it's exactly as I expected. Perhaps, if I get the job I will find out more and I will fill you all in in their reasoning for using Variants.

    norie, I agree that a cell is a range object (how can I not!) but what I meant was that I can sort of see that the data within a cell could be a variant (i.e. a cell's data is a sort of loose definition of what a variant is) and that it remains so until some sort of formatting is applied to the cell. It's a stretch I know but it was the only way I could sort of explain why the company would use Variants to input data into worksheet cells instead of explicitly defining the variable types.

    Hi all, this isn't a problem I have but more of a query.

    I had an interview the other day for a VBA development role. We were chatting about Excel VBA and I was asked the question,
    "So when would you use variants?"
    I pretty much gave the answer "Never" except when putting the values of a worksheet range into a variable for example:

    dim varWksValuesArray as variant
    varWksValuesArray = ActiveSheet.UsedRange

    However, I was greeted with the response,
    "We use Variants all the time here when writing to and from Excel as we have found that the performance is much better".
    This response really surprised me as I had always thought that Variants caused a performance hit. I can sort of understand why to use a variant when writing to a cell in Excel because I suppose an Excel cell is fundamentally of Variant type.

    If any of you can enlighten me any further or have experienced such a performance gain with using variants, please add your comments as I'm still a little bemused by the statement.



    Re: Using IF statement finding for #N/A values

    Bit of a quick guess before I go home for the day but try something like:

    const COLUMN_NUMBER as long = 16 'P
    If Application.WorksheetFunction.IsNA(cells(i,COLUMN_NUMBER).address) Then

    Re: Using IF statement finding for #N/A values

    The worksheet function ISNA() returns a boolean dependent on wether the cell is an #N/A error. For example as used as a worksheet formula:

    =IF(ISNA(VLOOKUP(A1,B1:B200,1,FALSE))THEN,"The value was not found", VLOOKUP(A1,B1:B200,1,FALSE))

    Re: Two Strings in One Cell

    When it goes to the debugger, what line of code is highlighted?

    Bit pedantic I know, but you'll find VBA is much more efficient without using .select. For example: