Posts by RichardSchollar

    Re: Separate Decimal Parts


    Assuming a floating point variable Val, then you can strip the mantissa (fractional part) into another variable FractionalVal by:


    Code
    FractionalVal = Val-Val\1


    which makes use of Integer division...

    Re: Autofilter With Protection


    Hi


    You need to have applied the Autofilter to the sheet before protection was applied to the sheet (ie so the little dropdown arrows must appear on the field headings before you protect the sheet).


    Best regards


    Richard

    Re: Sorting A List By Date Which Is In Text Format


    Quote from kuvulmaz

    Unfortunately I can not do that, because as I have mentioned in the post the dates must be in text format otherwise my pivot table does not work properly. I did a lot of search about this date vs pivot table thing and the only way to keep it woriking is having the dates in text format.


    thanks


    Why do you need to keep it as text? You have a lot more flexibility if you have the dates as true numerics.


    One option would be to add an additional column to your data where you have numeric dates (ie same as the text ones, just dates) and sort on that.


    Richard

    Re: Find String And Offset Cell String


    Wow! That's a lot of code!


    Do you get any error values returned? Where abouts in the code does it error out if so? Have you stepped thru the code line by line to see why it might not be working? You definitely want to step thru the Set rng = bits to see if they are finding the specified values.


    Post back with your findings.


    Richard

    Re: Find String And Offset Cell String


    Hi Raynon


    Not sure what you're doing with the File_array(1,1,1) element of the code, but does the following help:


    Code
    Sub test()
    Set rng = Cells.Find(What:="ggg", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False)
    If Not rng Is Nothing Then s = rng.Offset(0, -3).Value
    MsgBox s
    End Sub


    Richard

    Re: Update Screen After Data Query Refresh


    Brian


    Setting the BackgroundQuery property to False means that the query must execute before control is passed back to VBA (ie for the next line of code). Otherwise, it operate asynchronously (so the query continues execution in the background while Excel skips on to the next line of code).


    Hope this explains things:-)


    Richard

    Re: Update Screen After Data Query Refresh


    Brian


    Give the following a try:



    Richard

    Re: Conditional Summing Accross Worksheets


    Yes - put the names of the sheets that you want to summarise data from (eg sheet1, sheet2, sheet3 etc) somewhere inobtrusive on your summary sheet. Assuming you have the name Bill in A6 (of your summary sheet) use the formula that I gave above (amending A1:A5 to wherever you've placed your list of sheet names). It currently looks at the A column and every time it sees the name Bill, it will sum the corresponding value in the M column.

    Re: Conditional Summing Accross Worksheets


    Hey Dave


    Say you have the names of the worksheets you want to sum from in A1:A5 (eg on a summary sheet- obviously extend this to your requirements) and in A6 and A7 you have the names of the two salespeople, then you can use this formula to sum up the relevant M1 cells on each sheet depending on what is in A1 of each sheet:


    =SUMPRODUCT(SUMIF(INDIRECT($A$1:$A$5&"!A:A"),A6,INDIRECT($A$1:$A$5&"!M:M")))


    SUMPRODUCT


    Hope this helps!


    Richard

    Re: Formatting To Shade Only Zeros


    Hi


    You can use Conditional Formatting for this: Select the cells you want to shade (let's assume A1:A100) and go Format>Conditional Formatting.


    Change to Formula Is and type in the following formula:


    =AND(A1=0,A1<>"")


    and hit the Format button so you can select the desired shade (patterns tab).


    Click OK.


    Hope this helps!


    Richard

    Re: Add Letters To Cell Range


    Depending on what you want to use the result for (for example, if it is simply for display purposes), you might find that simply amending the Custom number format of the relevant column (Format>Cells>Number Tab>Custom) to 0"PR" to achieve what you want.


    Richard

    Re: Excel Cell double Validation


    You could use a simple formula in Data Validation by selecting the two cells (A1 & B1), choosing custom and entering the following formula:


    Code
    =(MATCH(A1,list,0)*($A$1-$B$1))<>0


    this assumes that you have list defined as a named range with your 1,2,3,4,5 etc.


    Hope this helps!


    Best regards


    Richard