Posts by Eggcel

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

change

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

to

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.http://www.ozgrid.com/forum/misc.php?do=bbcode#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
 Else
 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: Enter day of month in one cell (ex. 15), second cell calculates day of week (ex.

You say the month and year are listed in the title. Do you mean the sheet name or in a cell somewhere on the sheet?

Assuming the year and month are in A1 and the you enter 15 in cell B2 place this formula in C2
=IF($B2<>"",DATE(YEAR($A$1),MONTH($A$1),DAY($B2)),"")

the ranges can be modified to suit your needs

Re: Error when attempting to search through only certain sheets

n in this line

With Sheets(sn).Cells(1).Resize(n, 6)

is equal to zero

in that case go with

With Sheets(sn).Cells(1).Resize(, 6)

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

Something like this then

Dim Newkbk As Workbook
fname = filepath & Filename
If Fileexists(fname) = True Then
  Workbooks.Open fname
Else
  
  Set Newkbk = Workbooks.Add
 Newkbk.SaveAs fname
End If
End Sub

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

Here is a function to check if file exists

Function Fileexists(fname) as boolean
   If Dir(fname) <> "" then _
   Fileexists = True _
   Else Fileexists = False
End Function

found here http://www.tek-tips.com/faqs.cfm?fid=1936

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

Try this

Sub Average()
    Dim Count As Long
    Dim R As Range
    
   
    With Sheets(1)
       Count = .Cells(Rows.Count, "B").End(xlUp).Row
       If .Cells(Count, 2).HasFormula = False Then ' see if cell already contains a formula(such as if you are running macro for the first time)
        .Cells(Count + 1, 2).Formula = "=AVERAGE(B5:B" & Count & ")"
       Else
        .Cells(Count, 2).Formula = "=AVERAGE(B5:B" & Count - 1 & ")"
       End If
    End With
   
   
      
      
End Sub

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

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

Can you post the code that you have tried, minus any personal info, of course. This may help someone to help you revise it to make it work.

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: formula to separate numbers and text

forum.ozgrid.com/index.php?attachment/42564/Something like this?

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

is levelStr elsewhere in the code?

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")
    
Next

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
Next

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

Re: Simple VBA loop help

Quote

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

Option Explicit
Sub RecordLoop2()
     
    Dim a, b(), c, d()
    Dim i As Integer, x As Integer, j As Integer, y As Integer
    Dim lrow As Long, mrow As Long
     'write the values of column D to array a
    lrow = Sheets("Input").Cells(Rows.Count, "D").End(xlUp).Row
    a = Sheets("Input").Range("D1:D" & lrow).Value
    
     'set b to equal size of a
    ReDim b(1 To UBound(a, 1), 1 To 2)
    
     'loop through a from end to beginning and write values to b
    For i = UBound(a, 1) To LBound(a, 1) Step -1
        x = x + 1
        b(x, 1) = "Q" & a(i, 1)
        b(x, 2) = "seconds"
    Next
     'Writes the values of b to Post-processing sheet modify _
    the column and/or row number To suit your needs
    If Sheets("Post-processing").Range("B1") = "" Then
     mrow = 1
    Else
     mrow = Sheets("Post-processing").Cells(Rows.Count, "B").End(xlUp).Row + 1
    End If
    With Sheets("Post-processing").Cells(mrow, 2).Resize(UBound(b, 1), UBound(b, 2))
        .Value = b
         
    End With
     
     
End Sub