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

    Code
    1. =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"))))))))

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


    AFAGAHALAMANAO
    Row 2A

    ug/L

    JAR 1
    A

    ug/L

    JAR 2
    A

    ug/L

    JAR 3
    B

    g/L

    JAR 1
    B

    g/L

    JAR 2
    B

    g/L

    JAR 3
    A

    Final

    ug/L
    Row 343251001.360.0112.00?



    Code
    1. =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


    A
    ug/L
    JAR 1
    A
    ug/L
    JAR 2
    A
    ug/L
    JAR 3
    B
    g/L
    JAR 1
    B
    g/L
    JAR 2
    B
    g/L
    JAR 3
    A
    Final
    ug/L
    43 25 100 1.36 0.01 12.00 ?


    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


    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


    Code
    1. [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:


    Hi good people.


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


    Code
    1. <tr><th class="t13RegionTitle">Find Isolation Lists</th>
    2. <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>
    3. </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


    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"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [/tr]


    [tr]


    [td]

    2996

    [/td]


    [td]

    Tree

    [/td]


    [td]

    7710

    [/td]


    [td]

    OUTDOOR CONSUMABLES

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2997

    [/td]


    [td]

    Grass

    [/td]


    [td]

    7710

    [/td]


    [td]

    OUTDOOR CONSUMABLES

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2998

    [/td]


    [td]

    Dirt

    [/td]


    [td]

    7720

    [/td]


    [td]

    GENERAL CONSUMABLES

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2999

    [/td]


    [td]

    Stones

    [/td]


    [td]

    7730

    [/td]


    [td]

    TOOL CONSUMABLES

    [/td]


    [td]


    [/td]


    [/tr]


    [/TABLE]


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

    [tr]


    [td]

    [TABLE="width: 873"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [/tr]


    [tr]


    [td]

    2996

    [/td]


    [td]

    Tree

    [/td]


    [td]

    7710

    [/td]


    [td]

    OUTDOOR CONSUMABLES

    [/td]


    [td]

    100

    [/td]


    [/tr]


    [tr]


    [td]

    2998

    [/td]


    [td]

    Dirt

    [/td]


    [td]

    7720

    [/td]


    [td]

    GENERAL CONSUMABLES

    [/td]


    [td]

    250

    [/td]


    [/tr]


    [tr]


    [td]

    2999

    [/td]


    [td]

    Stones

    [/td]


    [td]

    7730

    [/td]


    [td]

    TOOL CONSUMABLES

    [/td]


    [td]

    100

    [/td]


    [/tr]


    [tr]


    [td]

    2997

    [/td]


    [td]

    Grass

    [/td]


    [td]

    7710

    [/td]


    [td]

    OUTDOOR CONSUMABLES

    [/td]


    [td]

    150

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/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"]

    [tr]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [/tr]


    [tr]


    [td]

    2996

    [/td]


    [td]

    Tree

    [/td]


    [td]

    7710

    [/td]


    [td]

    OUTDOOR CONSUMABLES

    [/td]


    [td]

    100

    [/td]


    [/tr]


    [tr]


    [td]

    2997

    [/td]


    [td]

    Grass

    [/td]


    [td]

    7710

    [/td]


    [td]

    OUTDOOR CONSUMABLES

    [/td]


    [td]

    150

    [/td]


    [/tr]


    [tr]


    [td]

    2998

    [/td]


    [td]

    Dirt

    [/td]


    [td]

    7720

    [/td]


    [td]

    GENERAL CONSUMABLES

    [/td]


    [td]

    250

    [/td]


    [/tr]


    [tr]


    [td]

    2999

    [/td]


    [td]

    Stones

    [/td]


    [td]

    7730

    [/td]


    [td]

    TOOL CONSUMABLES

    [/td]


    [td]

    100

    [/td]


    [/tr]


    [/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"]

    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [/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.



    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.


    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.



    Regards
    Dejamls