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.

Option Explicit


Public Sub GetSentence(ByRef strLookFor As String)
    Dim rng As Range
    Dim blnFound As Boolean
    
    Set rng = ThisDocument.Range
    
    With rng.Find
        .Text = strLookFor
        
        If .Execute Then
            Call rng.Expand(wdSentence)
            rng.Copy
        End If
    End With
        
End Sub

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

No problem, it should look like this:

With Sheet1
        On Error Resume Next
        Set rng = .Range("AP1:AP" & lastrow).AutoFilter(Field:=1, Criteria1:=Sheets("Week 1").Range("K2").Sheet("Data Dump Tab").Range("A8", Cells(lastrow, lastcolumn))).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    End With
     
    If Not (rng Is Nothing) Then
        With Sheets("Sheet2")
            Range(.Cells(4, 1), .Cells(rngSelection.Rows.Count + 3, rngSelection.Columns.Count)).Value = rng.Value
        End With
    End If

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.

Dim rng As Range
    
    With Sheet1
        On Error Resume Next
        Set rng = .Range("AP1:AP" & lastrow).AutoFilter(Field:=1, Criteria1:=Sheets("Week 1").Range("K2").Range("A8", Cells(lastrow, lastcolumn))).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        If Not (rng Is Nothing) Then
            rng.Copy
        End If
    End With
    
    Set rng = Nothing

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: Wildcard in medianif array function.

Try this array formulae:
=MEDIAN(IF(NOT(ISERR(SEARCH(F5,$B$5:$B$11,1))),$C$5:$C$11,"x"))

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

Access.Application.ConvertAccessProject()

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

Thanks for the suggestion Aaron80 but that doesn't work either. In fact, it causes an exception in Excel: "The command or action 'Echo' isn't available now".

Re: ConvertAccessProject from Excel - Visible = False

Quote from Aaron80;587476

Try

Code

~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.

Public Function ConvertMdbToAccdb(ByRef strDbPath As String) As Boolean
'################################################################################
' Description      : Function to convert an mdb database to an accdb.
'                       This is essential as it removes the link to the mdw security
'                       database.
'                       Requires a reference to the Microsoft Access 14.0 Object Library
'
' Input Parameters:
' Name         Purpose
' strDbPath    The path of the database to convert
'
' Return Value     : True if the conversion was successful
'################################################################################
    Dim objAccess       As Access.Application
    Dim objFso          As FileSystemObject
    Dim strNewDb        As String
    Dim strCodeLocation As String


    On Error GoTo ErrorHandler
    
    strCodeLocation = "Convert mdb to accdb database."
    
    strNewDb = Left$(strDbPath, Len(strDbPath) - InStrRev(strDbPath, ".") - 1) & "accdb"
    
    'Delete any existing accdb database
    Set objFso = New FileSystemObject
    
    If objFso.FileExists(strNewDb) Then
        Call objFso.DeleteFile(strNewDb, True)
    End If
    
    Set objAccess = New Access.Application
    With objAccess
        .Visible = False
        Call .ConvertAccessProject(strDbPath, strNewDb, acFileFormatAccess2007)
        .Quit
    End With
    
    strDbPath = strNewDb
    
    ConvertMdbToAccdb = True
    
ConvertMdbToAccdb_Exit:
    Set objFso = Nothing
    Set objAccess = Nothing
    Exit Function


ErrorHandler:
    Call MsgBox(m_cstrModuleName & ".ConvertMdbToAccdb (" & strCodeLocation & ")" & vbCr & _
                "Error: " & Err.Description)
    Resume ConvertMdbToAccdb_Exit
End Function

Any help would be greatly appreciated.

Mark

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:

=COUNT(SEARCH(N2:N13,A1:L1,1))

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:

=COUNT(SEARCH(N2:N13,A1:L1,1))<>0

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.

Cheers,

Mark

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:

Public Sub CombinedLength1()
    
    Const COLUMN_NUMBER As Long = 11 'K
    Const START_AT_ROW As Long = 10
    Const END_AT_ROW As Long = 509
    
    Dim lngRow As Long
    
    For lngRow = START_AT_ROW To END_AT_ROW
        Cells(lngRow, COLUMN_NUMBER).Value = Cells(lngRow, COLUMN_NUMBER).Offset(0, -6).Value & _
                                            " " & Cells(lngRow, COLUMN_NUMBER).Offset(0, -5).Value
    Next lngRow
    
End Sub