Posts by BCP

    Re: Call Form from Excel worksheet button

    Thanks Cytop! That worked.

    I had been testing "Frm_Employees.Show", not realizing I needed to drop the "Frm_". It also was very useful to place the Show command within its own sub. so I could choose it from the button's macro list.


    I am accustomed to calling subs from buttons on an Excel Worksheet. Now I have a form with a combo box and it doesn't appear possible to assign the Form's Initialize sub to a worksheet button, because the sub isn't visible in the list of available macros for the button. I don't want the form to appear automatically when the worksheet is open. I want the form to appear when the user selects a particular button. How can that be done? What am I missing?

    Please advise.



    Re: Dir function

    Quote from cytop;670674

    Comment out the

    On Error Resume Next

    and see what happens when it runs...

    Thanks for your help and suggestions cytop.

    I fixed the script by installing a file open counter and a related patch to "refresh" the Excel VBA Dir () function. Otherwise, it returned a null string, i.e. "". Other changes I made first that didn't solve the problem included changing the Sub to a Public Sub and I dimmed StrFile at the local level (within the only sub it is used in).

    Re: Dir function

    Quote from cytop;670659

    How about mentioning the actual Error number/message...?

    There isn't an error message. It doesn't execute the line, then the "yellow highlight" goes to the previous sub and quits. I have seen that kind of behavior before when the VB Editor can't evaluate something.


    When I step-through my code below, it always opens the first file in the directory "C:\Pyramid Files", but when it comes back to the Pyramid Files sub after fully processing the first file via various other subs, the VB Editor apparently doesn't like something about this line: StrFile = Dir(), since it quits after "snapping-back" to the previous sub Initialize(). I have also tried StrFile = Dir, but that doesn't work either. I did Dim Strfile in the General Declarations. When I set Watches for Dir and Dir(), I get the value "Invalid procedure call or argument" for both, as if the directory function lost the value. I can't determine why this is happening. Can you see what I am missing?


    I'm getting a Subcript Out of Range error for an array based on an Excel spreadsheet, and I can see that it is because there is a blank in the cell that the array is attempting to retrieve the element from. I have been trying to get past that error with no success.

    For Example:

    If cwlaArray(r, 3) = "" Then]

    returns "Subscript Out of Range" rather than True. I can't use an "On Error Resume Next" since I don't want the next lines of script executed.

    Here is the part of my script where the problem occurs. The bolded line is where the error occurs. I would like it to exit the If and For statements.

    For r = 2 To UBound(cwlaArray, 1)
        'Test whether the Customer Code in the CWLA File equals that in the Pyramid file
        [B]If cwlaArray(r, 3) = PyrCC[/B] Then
           ClientCode = cwlaArray(r, 1)
           PyrRow = ecell.Row
           Fname2 = "c:\Client Code Files\" & ClientCode & ".xlsx"
           Exit For
        End If
    Next r

    Any help is much appreciated.


    Hello, I keep stepping-through my code and can't find the answer to the above error message. It runs fine until it get to the line with the Vlookup. Can you see the problem I can't see?


    Using Excel VBA code, I am building a 67,500 row spreadsheet that is growing constantly larger as I add columns to the right. I often open a workbook, copy a spreadsheet of interest into my dedicated macro workbook, close the non-macro workbook, and process information from the added spreadsheet. After processing the information from the added spreadsheet, I delete it.

    As I build, many columns are inserted, V-lookups are added, and formulas are converted to values. There is no problem, until I get to a certain point where the conversion of formulas to values slows down to a crawl - and that is a generous description! The worksheet slows down dramatically when I attempt to convert 2 consecutive columns from formulas to values. At that point, the spreadsheet has 37 columns and 67,500 rows. I have all of my code in 1 module with 25 subroutines. Should I break the code into a 2nd module, or is there some other problem you see or approach you can suggest?

    Thanks for any suggestions!


    Re: Excel VBA replace function using not equal to or not. Is it possible?

    Quote from JasperD;650785

    what do you mean with "not X" - what are you trying to do specifically?

    Hi Jasper,

    I mean that out of a column list of say 10 values, I only want to retain 1 X value in that column, so I want to essentially say "replace not X with a blank".


    Re: Multiply each value in a column by 1

    I found an approach on ozgrid at
    that I recorded and modified to suit. The code worked for me, and finally, my V-Lookups work! I needed to modify a column that had numbers, text and mixed text and numbers to numbers - at least where there were numbers - so the multiplication by 1 gave me true numbers in that column that I needed to be referenced by the V-Lookups:

    Range("BG1").Value = 1
    PODtlRpt.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
            SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False

    Note that range BG1 is a few columns to the right of my last data column.

    Here is the code I wrote to multiply a column of values by 1:

    Dim PODtlRpt As Range
       Set PODtlRpt = WSF.Range(Cells(2, 9).Address, Cells(LastRowF, 9).Address)PODtlRpt.Value = PODtlRpt.Value * 1

    I am getting a Type Mismatch Error. Any idea why and how to restate it?




    I know what Type Mismatch means, but I haven't been able to figure out the cause of the Type Mismatch error I am getting for this line of code:
    Name1(s) = CurCell.Offset(s, 2)

    In the Immediate Window, I get a Vendor Name for CurCell.Offset(s,2) and I Dimmed Name1 As Variant (for an Array), so that seems to be sufficient to me, but I nevertheless get the "Type Mismatch" error. Can you point me in the right

    Re: Find is returning a string value rather than a range

    Thanks Stephen R!

    Given your input, I was able to get to the information I was seeking using the following code:

    ...but I still have to heed your advice to test whether the Find return value is nothing, since that happened with y=2. I commented-out your suggested revision and will work with that next. Thanks again.


    Here is my subroutine that is returning a string value instead of a range:

    When I hover my cursor over the "aCell" in the Find statement when I step-through the code, I see a string that contains an alphanumeric location string rather than a range.

    I was under the impression that ".Find" always returns a range, but in this case
    it doesn't. Does anyone know why or how a value rather than a range is being returned?



    Re: 2010 Outlook VBA - Accessing non-default folders

    Thank you Greg! That appears to work, subject to testing by my internal customer, but I am confident she will have success also.

    I have searched for that answer on the web from many different perspectives, but never hit on an answer that was that closely related to my question.


    I have viewed many threads on this topic, but I just can't get this to
    work for me: Please note that the folder "USPS Compass JE" is on the same
    folder hierarchy level as Inbox

    Dim olapp As Outlook.Application
    Dim olNameSpace As Outlook.Namespace
    Dim olfolder As Outlook.MAPIFolder
    Set olapp = CreateObject("Outlook.Application")
    Set olNameSpace = olapp.GetNamespace("MAPI")
    Set olfolder = olNameSpace.Folders("USPS Compass JE")

    When I look at the folder's properties, it's location is:
    \\Mailbox - Pelkey, Brian

    When I step through the code, I get an error message for the last
    coded line that "An object cannot be found".

    I also have tried:

    Set olfolder = olNameSpace.Folders("Mailbox - Pelkey,Brian/USPS Compass JE")

    AND also the GETFOLDER method which didn't appear to be valid.

    Thanks for any informed help offered.