Posts by Eggcel

    Re: Average, using named range to refer to data range


    ActiveCell.FormulaR1C1 = "=IFERROR(AVERAGE(B" & ActiveCell.Row - Count & ":B" & ActiveCell.Row - 1 & "),"" "")"


    ActiveCell.Formula = "=IFERROR(AVERAGE(B" & ActiveCell.Row - Count & ":B" & ActiveCell.Row - 1 & "),"" "")"

    Re: macro to open existing file or create it if it does not exist

    Please use code tags when posting code. check here to learn how

    It makes it so much easier to view your code.

    You created a function for the check so the variables do not carry over. Try something like this

    Dim fname As String
        'Change these variables as desired...
        FilePath = "c:/" 'change path here
        FileName = "My Scheduled Appointments.xlsm" 'change name here
        fname = FilePath & FileName
        Call Search(fname)

    and then the function like this

    Function Search(fname)
    Dim Newkbk As Workbook
     If Fileexists(fname) = True Then
     Call pcaddon
     Set Newkbk = Workbooks.Add
     Newkbk.SaveAs fname
     End If
     End Function

    Re: Date Picker issue in Excel 2010

    Any chance you could post your worksheet? This would help to isolate your issue. The calendar control was part of ms access and is not available in 2010. If this is what you are using, it may be why you are getting the error. If you are using DT picker, then seeing your worksheet may help isolate your problem. I had a similar issue recently switching to 2010 from 2007. I didn't care much for the DT picker and wound up creating my own custom calendar.

    Re: Error when attempting to search through only certain sheets

    When cross posting becomes an issue is when you do not specify in all of the forums that you post that you have cross posted in other forums(providing a link to the post). Those that provide help here do it voluntarily. If you have posted on another forum and don't mention it here, someone may have already answered your issue on the other forum and without knowing that, volunteers time here would be wasted instead of going to help someone else with their problems.

    Re: Average, using named range to refer to data range

    Try this

    you need to concatenate the variable into the formula and use quotes.

    Re: Selecting rows between 2 cell values

    Is the cell with "Reports Total" the last used row of the sheet? Also, if you could provide a sample sheet of data it would be easier for someone to assist you, as well as the expected result.

    Re: Count number of items that match search criteria

    You might try something like this for counting the cells with 1 on each sheet.

    Firstsheet = True
        For Each sht In Application.Worksheets
            With sht
                Set Rng = .Range("R1:R" & Cells(Rows.Count, "R").End(xlUp).Row)
                Occurrence = Application.WorksheetFunction.CountIf(Rng, 1)
                OccuranceCounter = OccuranceCounter + Occurrence

    Once you go through all the sheets then add your code to place the total in the appropriate cell

    Re: How to fetch the table from ASP web site and save in Excel

    It may be that it is not really the 12th table you are after. Try changing the item number in

    Set IeTbl = objIE.Document.getelementsbytagname("Table").Item(12)

    to lower numbers and see if that helps

    Edit: I just tried the code with a random web page and got the same error until I lowered the item number. Verify the table number you are after.

    Re: Match multiple values

    You can use the sumif function. =SUMIF(A1:A6,A1,B1:B6) assumes codes are in A1-A6 and the currency value in B1-B6. You can alter the ranges to suit your needs.

    Re: Send array argument to javascript function via Excel VBA

    Are you opposed to using this method to fire your functions? That is typically how I set it up when I automate a web page. I will simulate through vba all of the manual steps it takes to get the page I need to extract information from. The only time I found it necessary to actually call a function was with a web page that I helped another user with that had a javascript toolbar in one of the frames. I wish you luck.

    Re: Send array argument to javascript function via Excel VBA

    yes, like this

    For i = 0 To myHTMLDoc.frames.Length - 1
        Set myHTMLFrame = myHTMLDoc.frames(i).Document
        Call myHTMLFrame.parentWindow.execScript("updateForm()", "JavaScript")

    and have you tried this syntax to call the function?

    Call myHTMLFrame.parentWindow.execScript("updateForm()", "JavaScript")

    Re: Send array argument to javascript function via Excel VBA

    Something like this

    IE.document.all('your tag name').fireevent "onchange"

    As far as finding the right frame to call your function you could try something like this

    Dim myHTMLDoc As HTMLDocument 
    Dim myHTMLFrame As HTMLDocument 
     'get the main HTML Document
    Set myHTMLDoc = IE.Document 
     ' loop through frames
    For i = 1 To myHTMLDoc.frames 
        Set myHTMLFrame = myHTMLDoc.frames(i).Document 
        Call myhTMLFrame.parentWindow.execScript "updateForm", "JavaScript

    calling it in every frame to see which one is the right one.

    Re: Simple VBA loop help


    Firstly, I would like to be able to repeat the procedure containing the string so that the sheet "Post-processing" has the string of "Q'x'" repeated in the same column. As the number of rows may change on a daily basis, I have tried to repeat the string again but at the bottom of the previously inserted string by replacing (1, 1) with (X1up) - but had no such luck.

    I am not sure exactly what you meant here, but I am assuming you want to be able to run the macro again and have it add the data to the end of the row. Please clarify if otherwise. This does what I assume you meant for both items