Posts by dejamls

A better example attached.

Corrected some text regarding method I used.

Methodology

$I$1 = lower value of 0.3

$K$1 = upper value of 3

MEDIAN all A Jar values if all 3 B Jar values are >0.3 AND <3.0 (within range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 1 and A Jar 3 metal values if B Jar 2 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 2 and A Jar 3 metal values if B Jar 1 value is <0.3 AND >3 (outside range)

A Jar 3 metal value if B Jar 1 and B Jar 2 value is <0.3 AND >3 (outside range)

A Jar 2 metal value if B Jar 1 and B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 1 metal value if B Jar 2 and B Jar 3 value is <0.3 AND >3 (outside range)

Display "B Jars outside value guideline" if B Jar 1, B Jar 2 and B Jar 3 values is <0.3 AND >3 (outside range)

Code in the sample

=IF(AND(AND($H3>=$I$1,$H3<=$K$1),AND($I3>=$I$1,$I3<=$K$1),AND($J3>=$I$1,$J3<=$K$1)),MEDIAN($B3:$D3),IF(AND(AND($H3>=$I$1,$H3<=$K$1),AND($I3>=$I$1,$I3<=$K$1),OR($J3<$I$1,$J3>$K$1)),($B3+$C3)/(($B3<>0)+($C3<>0)),IF(AND(AND($H3>=$I$1,$H3<=$K$1),OR($I3<$I$1,$I3>$K$1),AND($J3>=$I$1,$J3<=$K$1)),($B3+$D3)/(($B3<>0)+($D3<>0)),IF(AND(OR($H3<$I$1,$H3>$K$1),AND($I3>=$I$1,$I3<=$K$1),AND($J3>=$I$1,$J3<=$K$1)),($C3+$D3)/(($C3<>0)+($D3<>0)),IF(AND(OR($H3<$I$1,$H3>$K$1),OR($I3<$I$1,$I3>$K$1),AND($J3>=$I$1,$J3<=$K$1)),$D3,IF(AND(OR($H3<$I$1,$H3>$K$1),AND($I3>=$I$1,$I3<=$K$1),OR($J3<$I$1,$J3>$K$1)),$C3,IF(AND(AND($H3>=$I$1,$H3<=$K$1),OR($I3<$I$1,$I3>$K$1),OR($J3<$I$1,$J3>$K$1)),$B3,IF(AND(OR($H3<$I$1,$H3>$K$1),OR($I3<$I$1,$I3>$K$1),OR($J3<$I$1,$J3>$K$1)),"B Jars outside value guideline","Problem"))))))))

Apologies. Sample attached.

I believe I may have it sorted. Maybe there is a better way.

=IF(AND(AND($AL3>=$AM$1,$AL3<=$AO$1),AND($AM3>=$AM$1,$AM3<=$AO$1),AND($AN3>=$AM$1,$AN3<=$AO$1)),MEDIAN($AF3:$AH3),IF(AND(AND($AL3>=$AM$1,$AL3<=$AO$1),AND($AM3>=$AM$1,$AM3<=$AO$1),OR($AN3<$AM$1,$AN3>$AO$1)),($AF3+$AG3)/(($AF3<>0)+($AG3<>0)),IF(AND(AND($AL3>=$AM$1,$AL3<=$AO$1),OR($AM3<$AM$1,$AM3>$AO$1),AND($AN3>=$AM$1,$AN3<=$AO$1)),($AF3+$AH3)/(($AF3<>0)+($AH3<>0)),IF(AND(OR($AL3<$AM$1,$AL3>$AO$1),AND($AM3>=$AM$1,$AM3<=$AO$1),AND($AN3>=$AM$1,$AN3<=$AO$1)),($AG3+$AH3)/(($AG3<>0)+($AH3<>0)),IF(AND(OR($AL3<$AM$1,$AL3>$AO$1),OR($AM3<$AM$1,$AM3>$AO$1),AND($AN3>=$AM$1,$AN3<=$AO$1)),$AH3,IF(AND(OR($AL3<$AM$1,$AL3>$AO$1),AND($AM3>=$AM$1,$AM3<=$AO$1),OR($AN3<$AM$1,$AN3>$AO$1)),$AG3,IF(AND(AND($AL3>=$AM$1,$AL3<=$AO$1),OR($AM3<$AM$1,$AM3>$AO$1),OR($AN3<$AM$1,$AN3>$AO$1)),$AF3,IF(AND(OR($AL3<$AM$1,$AL3>$AO$1),OR($AM3<$AM$1,$AM3>$AO$1),OR($AN3<$AM$1,$AN3>$AO$1)),"B Jars outside value guideline","Problem"))))))))

Methodology

$AM$1 = lower value of 0.3

$AO$1 = upper value of 3

MEDIAN all A Jar values if all 3 B Jar values are >0.3 AND <3.0 (within range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 2 and A Jar 3 metal values if B Jar 2 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 3 metal value if B Jar 1 and B Jar 2 value is <0.3 AND >3 (outside range)

A Jar 2 metal value if B Jar 1 and B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 1 metal value if B Jar 2 and B Jar 3 value is <0.3 AND >3 (outside range)

Display "B Jars outside value guideline" if B Jar 1, B Jar 2 and B Jar 3 values is <0.3 AND >3 (outside range)

Hi

Please can someone help me with a formula.

I have these columns

I need a formula if possible to calculate a "A Final ug/L" result based on certain conditions.

Conditions is:

1. If a particular B group JAR is below 0.3 g/L or above 3.0 g/L then the corresponding A group JAR sample should be rejected in calculations for final value.

2. If one of the three corresponding A group JAR sample is rejected then the remaining two A group JAR samples are calculated as mean value (average) in the final value.

3. If two of the corresponding A group JAR sample is rejected then the remaining one A group JAR samples is just the final value.

4. If no JARs rejected then all three A group JAR samples calculated as median (middle value) value.

5. Zero values and blanks not included in calculation.

I really appreciate any help given.

Thanks

Re: Click a IE9 Web Page button using VBA

Send keys not working.

Cheers for the assistance John.

Going to play with the code bit more.

Re: Click a IE9 Web Page button using VBA

I will try send keys see if works ok. Tab to position and enter.

Re: Click a IE9 Web Page button using VBA

First of all I took any error handling code out of my macro so it didn't exit sub.

I tried stepping through code and got Runtime error'438': Object doesn't support the property or method.

With the Ctrl+G action I got Runtime error '91': Object variable or with block variable not set.

I tried new code too and no luck.

After code line

[COLOR=#333333]evtClick.initEvent "click", [/COLOR][COLOR=blue]True[/COLOR][COLOR=#333333], [/COLOR][COLOR=blue]False[/COLOR]

I got the Runtime error '438'

Re: Click a IE9 Web Page button using VBA

That got rid of the error, cheers but...

The button still won't click. There is two instances of the class "t13Button". I've tried try (1), (2), etc.

No luck.

I also tried the class "t13RegionButtons"

Thoughts John.

Cheers

Re: Click a IE9 Web Page button using VBA

Thanks for the reply.

I'm getting a compile error with the reset button code added: (This line - Dim resetButton As HTMLInputElement) "user-defined type not defined".

Without the reset button code everything works fine.

My excel knowledge is limited. Appreciate your help.

Snippet of the code:

' Code to open web page and then click the hypertext link.
    
    Dim IE As Object
    Dim NewURL As String
    Dim sURL As String
    Dim strCountBody As String
    Dim lStartPos As Long
    Dim lEndPos As Long
    Dim TextIWant As String
    On Error GoTo errhandler
    Application.ScreenUpdating = False
                                Set IE = CreateObject("Internetexplorer.Application")
    sURL = "http://murlxora03.prod.somedomain.com.au:7001/apex/mprdc/f?p=130:1"
    With IE
    .Navigate sURL
'
' Wait
    
    Application.Wait Now + TimeValue("00:00:02")
'
' Uncomment the line below if you want to watch the code execute, or for debugging.
    
    .Visible = True
    End With
'
' Loop until the page finishes loading.
    
    Do While IE.Busy
    Loop
'
' Wait
    
    Application.Wait Now + TimeValue("00:00:02")
'
' Click Reset Quick Search button.


    Dim resetButton As HTMLInputElement
    Set resetButton = IE.document.getElementsByClassName("t13Button")(0)
    resetButton.Click
    
    Application.Wait Now + TimeValue("00:00:02")
'
' Click a hypertext link called EXCEL in Excel Format.
    
    Set ElementCol = IE.document.getelementsbytagname("a")
    For Each Link In ElementCol
    If Link.innerHTML = "Excel" Then
    NewURL = Link.getattribute("href")
    Exit For
    End If
    Next Link
    With IE
    .Navigate NewURL
'
' Uncomment the line below if you want to watch the code execute, or for debugging.
    
    .Visible = True
    End With
'
' Loop until the page finishes loading.
    
    Do While IE.Busy
    Loop

Hi good people.

I am trying to find some code to click this button in the below html code.

<tr><th class="t13RegionTitle">Find Isolation Lists</th>
<th class="t13RegionButtons" valign="bottom"><input type="button" onclick="javascript:apex.submit(&#x27;RESET&#x27;);" value="Reset Quick Search" class="t13Button" />&nbsp;&nbsp;&nbsp;</th>
</tr>

I have used code successfully for opening a Intranet URL and clicking on a specific hypertext link on IE9 Web Page, but cannot work out how to write the correct code to click this particular button.

Any help appreciated.

Cheers

Re: Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to

I have it working .

Big smiles.

Many thanks.

Re: Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to

Hi

Thanks for replying.

I used the formula including the array function.

=INDEX(Sheet2!$E$2:$E$4,MATCH(A2&C2,Sheet2!$A$2:$A$4&Sheet2!$C$2:$C$4,0),1)

Used row 2 instead of 1 because of the column headers.

But it returned in Sheet1 E2 #N/A etc.

Re: Compare two columns on sheets 1 and 2 and copy corresponding value on sheet 2 to

Quote from MrRedli;770299

can attach a excel file with what output you need

Hi

Sorry cant upload because I am at work using work PC.

But here is the scenario.

Sheet1 I want to fill column E with data from Sheet2.
[TABLE="width: 873"]

A

B

C

D

E

2996

Tree

7710

OUTDOOR CONSUMABLES

2997

Grass

7710

OUTDOOR CONSUMABLES

2998

Dirt

7720

GENERAL CONSUMABLES

2999

Stones

7730

TOOL CONSUMABLES

[/TABLE]

Sheet2 Note that Sheet2 data is not ordered the same as Sheet1.
[TABLE="width: 873"]

[TABLE="width: 873"]

A

B

C

D

E

2996

Tree

7710

OUTDOOR CONSUMABLES

100

2998

Dirt

7720

GENERAL CONSUMABLES

250

2999

Stones

7730

TOOL CONSUMABLES

100

2997

Grass

7710

OUTDOOR CONSUMABLES

150

[/TABLE]

[/TABLE]

Using Sheet1 columns A and C match Sheet2 columns A and C and return Sheet2 column E value to Sheet1 column E.

Sheet1 end result
[TABLE="width: 873"]

A

B

C

D

E

2996

Tree

7710

OUTDOOR CONSUMABLES

100

2997

Grass

7710

OUTDOOR CONSUMABLES

150

2998

Dirt

7720

GENERAL CONSUMABLES

250

2999

Stones

7730

TOOL CONSUMABLES

100

[/TABLE]

Hi I have tables on Sheet1 and Sheet2. The table on Sheet2 has many more rows of data than Sheet1.

I need to search Sheet2 columns A and C for matching values in Sheet1 columns A and C and when found copy corresponding valve in Sheet2 column E to Sheet1 column E.

Is there a formula for this or does it need vba?

Table data looks like this in both Sheets except Sheet2 will have values in the Sum of Actual column and many more rows of data;

[TABLE="width: 656"]

[TD="class: xl81, width: 100, align: center"]A[/TD]
[TD="class: xl81, width: 280, align: center"]B[/TD]
[TD="class: xl81, width: 114, align: center"]C[/TD]
[TD="class: xl81, width: 229, align: center"]D[/TD]
[TD="class: xl82, width: 150, align: center"]E[/TD]

[TD="class: xl77, width: 100"]2996[/TD]
[TD="class: xl77, width: 280, bgcolor: #fde9d9"]Tree[/TD]
[TD="class: xl76, width: 114, bgcolor: #fde9d9"]7710[/TD]
[TD="class: xl77, width: 229, bgcolor: #fde9d9"]OUTDOOR CONSUMABLES[/TD]
[TD="class: xl79, width: 150"] [/TD]

[TD="class: xl77, width: 100"]2997[/TD]
[TD="class: xl77, width: 280, bgcolor: #fde9d9"]Grass[/TD]
[TD="class: xl76, width: 114, bgcolor: #fde9d9"]7710[/TD]
[TD="class: xl77, width: 229, bgcolor: #fde9d9"]OUTDOOR CONSUMABLES[/TD]
[TD="class: xl79, width: 150"] [/TD]

[TD="class: xl77, width: 100"]2998[/TD]
[TD="class: xl77, width: 280, bgcolor: #fde9d9"]Dirt [/TD]
[TD="class: xl76, width: 114, bgcolor: #fde9d9"]7720[/TD]
[TD="class: xl77, width: 229, bgcolor: #fde9d9"]GENERAL CONSUMABLES[/TD]
[TD="class: xl79, width: 150"] [/TD]

[TD="class: xl77, width: 100"]2999[/TD]
[TD="class: xl77, width: 280, bgcolor: #fde9d9"]Stones[/TD]
[TD="class: xl76, width: 114, bgcolor: #fde9d9"]7730[/TD]
[TD="class: xl77, width: 229, bgcolor: #fde9d9"]TOOL CONSUMABLES[/TD]
[TD="class: xl79, width: 150"] [/TD]

[/TABLE]

Re: Search Folders And Create Hyperlinks To Files in Excel 2007

Hi

Could someone help to modify the 2nd code so that it;

1/ Searches sub-folders as well and
2/ pastes the hyperlink of the matching document in row B.
3/ if a matching document is not found then skips that row and continues on.

Sub CreateHLnk()
Dim ws As Worksheet
Dim rng As Range
Dim x As Long
Dim c As Range
Dim strFile As String
Dim strPath As String


Set ws = ThisWorkbook.Sheets("Sheet1")
strPath = "C:\MyDocs\"  ' change accordingly
' the following following will create an hyperelink to cell value,
' if a PDF file is existing with the same name in the directory


With ws
    x = .Rows.Count
    Set rng = .Range("A2", .Range("A" & x).End(xlUp))
    
    For Each c In rng
        strFile = c.Value
        strFile = Dir(strPath & strFile)
        If strFile <> "" Then
            .Hyperlinks.Add c, strFile
        End If
    Next
End With

Thanks in advance.

Re: VBA code to calculate number of dates in column that are after todays date

Solution I finally got was inputing formula into cell that I wanted result in and then - clicked record macro - clicked cell - clicked F2 - hit enter - stopped recording - copied recorded VBA into code.

Works!

If there is a better way then glad to hear.

' MTD Stats Data Macro
'
' Turn screen updating off.
    Application.ScreenUpdating = False
'
' Count - Sheet "Search Results" for  Column "Library Received " - Documents Received during current month.
' =COUNTIFS(SearchResults!J:J,">="&EOMONTH(TODAY(),-1)+1,SearchResults!J:J,"<"&EOMONTH(TODAY(),0)+1)
'
    Range("C17").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(SearchResults!C[7],"">=""&EOMONTH(TODAY(),-1)+1,SearchResults!C[7],""<""&EOMONTH(TODAY(),0)+1)"
    Range("C18").Select
'
' Count - Sheet "SOP's" for Column "Next Review" - Documents now due for review prior to TODAYS date.
' =COUNTIF(SOPs!L:L, "<=" & TODAY())
'
    Range("C19").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(SOPs!C[9], ""<="" & TODAY())"
    Range("C20").Select
'
' Count - Sheet "SOP's" for "Next Review" - Documents due for review in next 90 days from TODAYS date.
' =COUNTIFS(SOPs!L:L,"<="&TODAY()+90,SOPs!L:L,">="&TODAY())
'
    Range("C20").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIFS(SOPs!C[9],""<=""&TODAY()+90,SOPs!C[9],"">=""&TODAY())"
    Range("C21").Select

Re: VBA code to calculate number of dates in column that are after todays date

Hi

Still not working.

The two excel formulas I need converted to VBA if anyone can help, much appreciatted.

They work but I need them in VBA.

=COUNTIF(SOPs!L:L, "<=" & TODAY())

=COUNTIFS(SOPs!L:L,"<="&TODAY()+90,SOPs!L:L,">="&TODAY())

Re: VBA code to calculate number of dates in column that are after todays date

Hello experts

Please can you help with my coding.

I have tested it but it doesn't count properly.

The first part works but the counting for 'prior to TODAYS date' and 'next 90 days from TODAYS date' doesn't.

Sub MTD_Stats()
'
' MTD Stats Data Macro
'
' Count - Sheet "Search Results" for  Column "Library Received " - Documents Received during current month.
'
    Dim i As Long
    
    With Sheets("SearchResults")
        i = WorksheetFunction.CountIfs(.Columns("J:J"), ">=" & WorksheetFunction.EoMonth(Date, -1) + 1, .Columns("J:J"), "<=" & WorksheetFunction.EoMonth(Date, 0))
    End With
    
    Worksheets("MTD Stats").Range("C6").Value = i    ' Adjust sheet name and range to suit yourself.
'
' Count - Sheet "SOP's" for Column "Next Review" - Documents now due for review prior to TODAYS date.
'
    Dim x As Long
    Dim z As Date
    
    z = Date
    
    With Sheets("SOPs")
        x = Application.WorksheetFunction.CountIf(.Columns("L:L"), "<= & z")
    End With
    
    Worksheets("MTD Stats").Range("C7").Value = x    ' Adjust sheet name and range to suit yourself.
'
' Count - Sheet "SOP's" for "Next Review" - Documents due for review in next 90 days from TODAYS date.
'
    Dim y As Long
    Dim w As Date
    
    w = Date
    
    With Sheets("SOPs")
        y = Application.WorksheetFunction.CountIf(.Columns("L:L"), ">= & w +90")
    End With
    
    Worksheets("MTD Stats").Range("C8").Value = y    ' Adjust sheet name and range to suit yourself.
       
End Sub

Regards
Dejamls

Re: VBA code to calculate number of dates in column that are after todays date

OK

I think I got the countif VBA to work.

Dim x As Long
    Dim z As Date
    
    z = Date
    
    With Sheets("SOPs")
        x = Application.WorksheetFunction.CountIf(.Columns("L:L"), ">= & z")
    End With
    
    Worksheets("MTD Stats").Range("C19").Value = x

Appears to work fine.

Hi

I need a VBA formula that looks at a column of a large number of dates and calculates the number of dates that are >= todays date.

I have the excel formula

=countif(L:L,">="&today())

But how is that expressed in VBA?

I also need seperate VBA code to count the dates between TODAY and +90 days time.

All help appreciated.

Thanks