Posts by Box Jockey

    Apologies as its a big rats nest, let me know if this helps to clarify or not! The line below is what has worked for years, but recently the report I am importing has changed to include the time stamp in the date column I am using, so trying to kill that. This is the line I was hoping to replace with something more elegant like the above, but worst case I am just going to create new formula based columns in the imported report and will copy the INT'd values from there.


    PS this is within a with (With wbOpenBook.Worksheets(1) )


    Sheet2.Range("G" & dToolRow & ":G" & dToolRow + (dExtractRow - 2)).Value = _

    .Range(.Cells(2, rFindRange.Column), .Cells(dExtractRow, rFindRange.Column)).Value

    I feel like I have done something similar to this in the past, but now I am starting to think I am remembering wrong.


    Assuming that rng1 and rng2 are identical lengths, is it possible to do something like the following? Output wise I am just getting #VALUE.


    rng1.value = [Int(rng2.value)]

    Trying to apply conditional formatting to a pivot based on the ranking of the value. It was working for most values but some would generate an #N/A. I built some test formulas outside of the pivot and I am seeing the results in the pic below.


    Both rank formulas are as follows:

    =RANK(E31,Scoring!$H$10:$J$10,0)

    =RANK(E32,Scoring!$H$10:$J$10,0)


    The sumif value is coming from the range used for ranking, and it is looking at the same dataset as the pivot table (it will be in cell H10 of the ranking range listed above). I thought maybe there was some weird rounding or decimal issue, but based on the variance in the pic it looks like they are identical too me. Not to mention it is working for most of the other cells. Any ideas what could be driving that?

    We have a winner, thank you!! :D


    So I thought all our installs were pretty consistent across the company. At least we are all on 365. Are there different versions within that which can cause this issue?

    The following code is taking row headers that appear as full month names and converts them to 3 letter months. Works fine on my machine and many others, but for a couple users it corrects the first (January to Jan) and then repeats that across all months so everything becomes Jan. Unfortunately I don't have access to their machines to test, any ideas what I might be doing wrong here?


    Code
    Set rFixRange = .Range("E" & iTopRow - 2 & ":" & "P" & iTopRow - 2)
    rFixRange.Value = Evaluate("Left(" & rFixRange.Address & ",3)")
    Set rFixRange = Nothing

    I have a worksheet with a pivot table that is functioning fine. Near the pivot filters at the top of the page, I have added a cell with list validation. It simply contains "Y, N." This drop down triggers code to remove the pivot field currently in values and replace it with another. This all works fine, but as soon as it hits Thisworkbook.Refreshall it blows the list validation out. I worked around it by just re-assigning it in the code, but does anyone know why that is happening?

    Thanks for the effort on that! I had offered a similar option (Y/N up at the top for each DOW) but there are several other similar controls lurking up there and they didn't want any more clutter. Damn you big wigs!!! :)

    No, the order of the DOW autofilter is the issue, it sounds like when using the text based DOW it cant be changed unless I sort that column, which I cant do because I need the rows to stay in date order. In the end it doesn't really matter as the filter works, I just know that as soon as the big shots pull it up, they will ignore the fact that the entire file is doing what they need and say "Why are the days of week all out of order." I am sure the will move passed it eventually :)

    The first column is dates in date order, second column is day of week. That is what I am filtering, so I can display just Friday's if I need to do some digging there. I think royUK had it, I would need to sort that underlying data in order to change the autofilter list order.

    Tried searching but I feel like I am not using the right terms. Is there a way to change the order of an Autofilter list? As pictured below the days of the week are all over the place and I am hoping I can change that without having to change the underlying data.


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tfILTER.png Views:\t0 Size:\t32.1 KB ID:\t1221766","data-align":"none","data-attachmentid":"1221766","data-size":"full","title":"fILTER.png"}[/ATTACH]