Posts by Sp0ns


    Column G4:G503 all contain a start date
    Column H4:H503 all contain an end date

    What formula could I use to calculate the average difference between all these dates ?



    works for the first cell but this needs to be done over all forementioned cells without using a helper column
    It should also need to check first for empty values; if one of the dates isn't filled in, there should be no result

    Thanks in advance,



    Is it possible to write a macro which sets different print areas on multiple sheets ?
    For instance, I would like to have printed (will assign the sub to a print button in a userform):

    Sheets("ItemsT4T5") = all "not empty rows", runs to A94 at max
    Sheets("ItemsSchaal") = all "not empty rows", runs to A94 at max

    Does anyone have an idea ?



    Re: Counting colors and updating formula

    Well that explains alot. Even more because I am looking for 4 different colors in two worksheets ...
    I didn't know about the slowness of UDF, I'm used to just working with subs :)

    I had been looking for the COUNTIF-version too ! :)

    Re: Counting colors and updating formula

    Thanks for your answer !
    I've added the statements to all my macros and it indeed fastens the whole procedure alot (from 30sec to 7sec).
    I still find 7 sec to be alot because i'm not running that much macros and skipping the 'colorsearch' reduces the runtime to less than a second. Is that macro so memory-intensive ?

    Still, I'll take the 7 seconds :)

    Thank you,


    I've been looking for a macro which can count specific colors in a range, making a calculation with this sum and putting the output in a specific cell. So far I've found this:

    But this function slows down my macro's a lot (and seems to slow even more over time). I thought that the Application.Volatile was slowing it down but removing it didn't solve it.

    Does anyone have a good idea for a workaround that forces a recalculation but does not slow down the progress ?
    For instance: Count all vbRed in Range C3:L44 and output in P2
    And count all Interior.ColorIndex = 33 in Range C3:L44 and output in P5

    I've also used this macro: but this also seems to slow down alot ?

    Thanks in advance !


    Re: Rounding digits

    Dear Rory,

    Thank you for your answer. Indeed, I'm using the built-in function to display two decimal places. How do you round the cell value ? Or is it possible to make your VBA so that it automatically rounds all the code to two decimals ?


    I have a question regarding the rounding in Excel.

    I'm using the code below to react to the value of a cell. The value of C26 is calculated by dividing the value of two other cells and putting the result in C26.

    If [C26].Value = 0.57 Then
        [G26].Value = "X"
        End If
    If [C26].Value > 0.57 And [C26].Value < 0.67 Then
        [G26:H26].Value = "X"
        End If

    When I enter 0.57 manually; the correct response is given (putting "X" in G26).
    When I calculate a division (4/7) with a result of 0.57XXXXX and cut to two decimals (0.57) it gives the wrong response (putting "X" in G26:H26)

    It seems that Excel is not able to "forget" the decimals behind 0.57; so it shows "0.57" but still reads and uses "0.57XXXXX".
    Is there a way to avoid this ?

    Re: Counting appearance of non-input and else count multiple input

    Dear Adrian,

    I was trying to rework this code for the next part of the macro.
    About the same, this time in col N on Sheets("Code")
    If single F (but not FMa, not FMp, not FMa-p, not CF, not FC) cnt + 0
    If single other content, cnt + 1
    If not single content, cnt + number of values in cell

    Is it possible to rewrite the given code ?

    Thanks in advance,


    Re: Counting appearance of non-input and else count multiple input

    Dear Adrian,

    One off, whe're getting there :) It scores 32 instead of 33
    I've used debug to find the difference in score and it seems to me that in number 19 (row 24) "An" is counting as a 0 but should be 1 because it is a single value which is not "A","Ad","(A)","(Ad)". It probably is counting because it does contain the letter A ? So to add to my request; it should be noted that "An" and "Ay" are possible values in col H which also contain the letter A.

    Re: Counting appearance of non-input and else count multiple input

    Thanks to the both of you for the code. I've tried both and would like to continue with Bryce's code because it comes near to what I'm trying to achieve and simply because it uses code I understand a bit better :)

    Bryce, there seems to be a problem in the code (I'm getting a score of 22 while I should get 33), maybe I wasn't clear in my request:

    If there is more than 1 value it should count the number of the values, not just n+1
    For instance:
    H,A,Ay,NC should count n+4
    Art, NC should count n+2
    H,Fi,NC should count n+3

    Is it possible to adjust the code to achieve this ?

    PS:Adrian, you also might have had a good code but I couldn't work some things out. I was missing the dim's and the transferring of the count to N33. So I added this myself but kept receiving a "1" as count and couldn't figure out why :)

    Stuck again, I'm trying to make following happen:

    If there is a single non-A Content code (so not "A", "(A)", "Ad" or "("Ad") in Col H: assign a value of 1 (so no comma's, or no A's ?)
    If there are multiple answers in Col H (split by ","): assign a value of the number of values (perhaps achieved by counting number of comma's and +1 ?)
    Check each row until empty row and add count to Sheets("Counts").Range("N32").Value

    I've tried several code using len(), .pattern, case select and such but can't integrate to a whole.

    Help would be much appreciated !

    Greetings, Maarten

    Re: Algorithm / Assign Max Value

    Thx, I rewrote to the following and works as aspected:

    Re: Algorithm / Assign Max Value

    Oh ok, I get it :)
    So this is the new code

    The only part not working is below, probably because case select won't working with searching for empty cells through "" ? It should count when there is no Vg in Col J and nog Sy in Col I. Since there is no other input than Sy or Vg, searching for empty cells is also ok.

    Case sp(Cells(r, "J"), "") And sp(Cells(r, "I"), "")
        cnt = cnt + 1

    Re: Algorithm / Assign Max Value

    Thank you for your answer.
    The problem is that more combinations of the criteria can exist; for instance:

    Sy in Col I, D in Col D and SI in Col G would trigger cnt + 2 and cnt +3
    But it should only trigger cnt + 3 because for each row it should only count the highest value possible

    Dear colleagues,

    I've reworked some code I've gotten on Ozgrid :

    This to achieve following (input from sheets.("Code")):

    1. If (Vg in col J) and (no Sy in col I): Score 0
    2. If (Vg in col J and Sy in col I) or (No Vg in col J and no Sy in col I): Score 1
    3. If (Sy in col I) with either (D in col D) or (Dd in col D): Score 2
    4. If (Sy in col I) with either (W in col D) or ( SR in F) or (SI in G): Score 3

    Scores should be calculated for each row
    col I and col J have no other input than Sy and Vg, that's why I'm searching for empty cells
    I've added the correct score in col T so you can compare, I'm calculating 41 but should get 48 as a total

    Problems are:

    - Step 2 is not counting and I don't know why
    - Each row should get the highest value applicable instead of cnt + x but I don't know how to achieve this