Posts by widgetwonka

Re: Run time error '13': Type Mismatch

Not sure what is wrong with that code. It would be faster to use the filter/hidden cells method though. It is well covered on the internet.

You might have better luch declaring garbage3 as an integer.

Re: Bypass or Accept Internet Explorer Dialogue Box When Exporting Webform Results

Thank your for your reply. Unfortunately, it did not work. The link executes a script which sends the form for processing and returns the workbook. Following the hyperlink opens a blank instance of the results form, but not a workbook.

Re: Excel dynamic list query

Vic,

In sheet1, I put Language in column A and Book title in column B.

For example:

A B
1 French French1
2 Spanish Spanish1
3 French French2

I then made a tab named French. This sheet has one column, A, named Book.
The result should be:

A
1 Title
2 French1
3 French2

There are two ways to approach the reference to "French." The first is to type it in a cell and reference it. The second is to use a formula to find the name of the worksheet. Which you choose is your preference.

Reference method:
I typed French into cell E1. In cells A2:A15, I used the following formula:
{=IFERROR(INDEX(Sheet1!$B$1:$B$15,SMALL(IF(Sheet1!$A$1:$A$15=French!$E$1,ROW(Sheet1!$B$1:$B$15),"Finished"),ROW()-1)),"")}

The brackets around the formula indicate that it is an array formula. To enter one of these, enter everything but the brackets. Then, when you are done entering the formula, hold down control + shift and then press enter. That will tell Excel that you are using an array.

The formula breaks down as follows:

IFERROR - tells Excel to suppress errors (since you will want to copy this formula down, this is an easy way to do it).
INDEX - finds the cell in the array
SMALL - dictates which position in the array to return
IF... - creates the array of French books by comparing the language in sheet1 for each line to the reference language. If they match, it records the row of that record in the array.
ROW() -1 - Tells the small formula what position to use, based on the row in the French tab that the formula is on. Since I referenced the array from A1, I need to subtract one to compensate for the data starting in A2

Formula Method:
{=IFERROR(INDEX(Sheet1!$B$1:$B$15,SMALL(IF(Sheet1!$A$1:$A$15=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256),ROW(Sheet1!$B$1:$B$15),"Finished"),ROW()-1)),"")}

The breakdown is the same, except that
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
is used to find the sheet name. There is some parsing going on in it to find just the sheet, since otherwise, it will bring back the entire path.

To use this formula:
I ran my formulas down 15 lines. Just change 15 to whatever number you want. Be sure to do it for all instances of 15. Then, copy the array and paste it down as many times as you like. A special note: you can't copy and then paste an array over itself. Excel will tell you that you are trying to change part of an array. So, just copy A1, then select A2:Awhatever you want, and paste the array. When pasting arrays, you do not need to use the control + shift + enter method.

I hope this gets you what you need.

Firstly, I should state that my knowledge of the Microsoft Internet Controls and MSHTML libraries is low. I started using them recently to try to automate the downloading of excel reports from a webform. As such, this has been a learning experience, and my code definitely reflects that.

The task:
I have a webform which is all scripted in Java. After initializing the page, a javascript determines what tab to display, etc. The result is that the page requires a generous amount of navigation to get to the input elements and to the export link (My guess is there is a better way for this as well, but that is not in scope for this post). I want to fill in the form, then click the link to export the results to excel.

The problem:
I have been able to fill in the form and get it to fire the export link. I am stuck here.

After I export the report, IE (I am restricted to IE at work...) opens a new window and then an Excel dialogue box opens with options to open, save, or cancel the document sent from the server. The file name of the export is not constant, and I don't know how to get the path of the export to feed to Excel.

I know methods to access the workbook after I open it, but I am stuck on how to actually open the workbook. Any help would be appreciated.

Obviously, posting the URL would be the best way, but it is restricted, so I cannot.

The code:

Sub positionsDownload()
 
Dim appIE As InternetExplorer
Dim doc1 As HTMLDocument
Dim doc2 As IHTMLElementCollection
Dim doc3 As IHTMLElementCollection 
Dim doc4 As HTMLFrameElement
Dim doc5 As IHTMLElementCollection
Dim doc6 As HTMLFrameElement
Dim doc7 As HTMLDocument 
Dim doc8 As HTMLFormElement
Dim doc9 As IHTMLInputElement
Dim doc10 As IHTMLTextAreaElement
Dim doc11 As HTMLFrameElement
Dim doc12 As HTMLDocument
Dim doc13 As HTMLLinkElement
Dim wbCount As Integer
Dim newWb As Integer
Dim wsCount As Integer
Dim downloadedWb As Workbook
Dim userDate As String
Dim userCusip As String
 
Application.DisplayAlerts = False
 
'specify user inputs here: parameterize after the rest of the code is working...
userDate = "11/30/2011"
userCusip = "248019AG6"
 
wbCount = Workbooks.Count
newWb = wbCount + 1
wsCount = ThisWorkbook.Sheets.Count
 
'setting the URL that has the webform...
sURL = "https://xxx/search_tools.epl"
Set appIE = New InternetExplorer
 
'open the webform and display to user
appIE.Navigate sURL
appIE.Visible = True
 
'loop until ready
Do While appIE.Busy
    Loop
 
Do Until appIE.readyState <> READYSTATE_COMPLETE
    DoEvents
    Loop
   
'the JScript does not seem to fire the readystate complete...so wait 10 seconds to be sure
Application.Wait (Now + TimeValue("00:00:10"))
 
'set the location of the input elements
Set doc1 = appIE.document
Set doc2 = doc1.getElementsByTagName("form")
Set doc3 = doc1.all
Set doc4 = doc3.Item("tool_frame")
Set doc5 = doc4.contentDocument.all
Set doc6 = doc5.Item("content_frame")
Set doc7 = doc6.contentDocument
Set doc8 = doc7.forms(, 1)
 
'find the pos date and update to be the user specified date
For Each doc9 In doc8.getElementsByTagName("INPUT")
    If InStr(doc9.Name, "pos_date") Then
        doc9.Value = userDate
        Exit For
    End If
Next doc9
 
'find the altid and update to be the user specified CUSIP
For Each doc10 In doc8.getElementsByTagName("TEXTAREA")
    If InStr(doc10.Name, "altid") Then
        doc10.Value = userCusip
        Exit For
    End If
Next doc10
 
'set the path for the export link
Set doc11 = doc5.Item("header_frame")
Set doc12 = doc11.contentDocument
 
'find the export link via the outerHTML word export
For Each doc13 In doc12.Links
    If InStr(doc13.outerHTML, "Export") Then
        doc13.Click
        Exit For
    End If
Next doc13
 
'Application.Wait (Now() + TimeValue("00:00:10"))
 
'Set downloadedWb = Workbooks(newWb)
'downloadWb.Sheets(1).Move After:=ThisWorkbook.Sheets(wsCount)
 
Application.DisplayAlerts = True
 
End Sub

Re: calculating a weighted average from referencing formulas in other cells

This works. You can modify the ranges as needed.

=SUM(Sheet1!C1*COUNTIF(Sheet1!A1:B10,1)/SUM(COUNTIF(Sheet1!A1:B10,1),COUNTIF(Sheet2!A1:B10,1)),Sheet2!C1*COUNTIF(Sheet2!A1:B10,1)/SUM(COUNTIF(Sheet1!A1:B10,1),COUNTIF(Sheet2!A1:B10,1)))

Re: Vlookup for duplicate values

If they are all values, you can use the SUMIF formula.
=SUMIF("Range of references from sheet2","lookup reference from sheet1","Range of values from sheet2")

Re: Excel AND SAP

have you tried modifying this code:
http://www.mrexcel.com/forum/showthread.php?t=468098

Re: VLookup across Multiple Sheets

Thanks for cleaning it up Sierra. I didn't test it, so I am not surprised it needed some revision. You don't see a lot of first time posters with that level of debugging acumen. A big welcome from one Excel junky to another!

DJ,

If you go the PT route, GETPIVOTDATA is also a good option, since you can configure the lookup across multiple dimensions.

Re: OFFSET with named range

You can use the column function to remove the index specification.

=IF(INDEX(NamedRange,0,COLUMN()-9)=0,0,INDEX(NamedRange,0,COLUMN()-9))

Re: how to remove entire rows if any cell contains numbers over 1500

In its most direct form, you can use a loop statement to do this:

Sub delete1500()
Dim rng As Range




Set rng = Range("C2:C150000")




For Each i In rng




If i.Value > 1500 Then
i.EntireRow.Delete
End If
Next i




End Sub

To use it for any workbook, but in in your personal workbook in the VBE. I set the loop to end at cell 150000, but you can change that to whatever number you want.

Re: vlookup problem, if statement

formula for K12:

=IF(J12<=17,0,VLOOKUP(J12,'Redundancy Weeks Due'!$A$2:$AU$51,'Main Sheet'!F12+1))

Re: Aging Inventory

I put your values in a spreadsheet, starting in A1. Here is the formula for cell D1:

{=IF(SMALL(IF(A2=$A$2:$A$1000,$C$2:$C$1000,10000),1)<=30,"0-30Days",IF(SMALL(IF(A2=$A$2:$A$1000,$C$2:$C$1000,10000),1)<=60,"31-60Days",">60Days"))}

This is an array function. To enter it, enter everything but the brackets. Then, hold ctrl+shift while pressing enter to exit the cell. The formula creates an array, and then find the smallest value. I set the alternative value to 10000, which should be OK given your buckets.

Re: Aging Inventory

So, if the item was used in the last 30 days, the bucket is 0-30days
and if the item was uned in the last 60 days, the bucket is 0-30 days?

I am not sure I understand your question.

what about =IF(TODAY()-"Last used date" <= 30, "0-30Days",IF(TODAY()-"Last used date" <= 60,"31-60Days",">60Days")))

Re: vlookup problem, if statement

You didn't specify a lookup value in your last VLOOKUP.

Re: Sheet reference

Do it without the apostrophies if it is a one word sheet name.

Or, just hit = then navigate to the cell you want to reference.

Re: Sheet reference

='Sheet Name!'A1

Where Sheet Name is the name of the sheet and A1 is the referenced cell.

Re: UDF works but not visible

Did you declare it as a public function?

Re: Problem with COUNTA &quot;NOT&quot; counting empty cells

You can use the countblank function:

=OFFSET(INDIRECT(SUBSTITUTE($A$2;" ";"_"));0;0;COUNTBLANK(INDIRECT(SUBSTITUTE($A$2;" ";"_")&"Col"))+COUNTA(INDIRECT(SUBSTITUTE($A$2;" ";"_")&"Col"));1)

Re: Normalzing Data in Excel

You are going to have to be more descriptive. How do you want to normalize it? % of column?

I'd probably use a pivot table.

Re: Last Row in Excel

=INDEX(Sheet1!B:B,COUNT(Sheet1!A:A))