# Posts by Sp0ns

• ## Sum average networkdays if cells aren't empty

Dear,

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 ?

Quote

=NETWORKDAYS(G4;H4)

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

Maarten

• ## Setting different print areas on multiple sheets

Dear,

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("Score").Range("D1:M60")
Sheets("T4").Range("A1:P44")
Sheets("T5").Range("A1:P44")
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 ?

Greetings,

Maarten

• ## Counting colors and updating formula

Re: Counting colors and updating formula

Well that tip shaved off another 5 seconds !
Actually all of my subs are being called through an overarching sub, so that works out fine for me now

Thanks alot !
:yourock:

• ## Counting colors and updating formula

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 !

• ## Counting colors and updating formula

Re: Counting colors and updating formula

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,

Maarten

• ## Counting colors and updating formula

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: http://www.ozgrid.com/VBA/Sum.htm but this also seems to slow down alot ?

Maarten

• ## Rounding digits

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 ?

• ## Rounding digits

Dearest,

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.

Code
``````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 ?

• ## Counting appearance of non-input and else count multiple input

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

Another one that works like a charm

• ## Counting appearance of non-input and else count multiple input

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

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 ?

Maarten

• ## Counting appearance of non-input and else count multiple input

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

Works like a charm, I thank you alot !

• ## Counting appearance of non-input and else count multiple input

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

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.

• ## Counting appearance of non-input and else count multiple input

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
etc.

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

• ## Algorithm / Assign Max Value

Re: Algorithm / Assign Max Value

Please don't apologise. I'm very thankful that there are good people who devote their free time to assist me

• ## Counting appearance of non-input and else count multiple input

forum.ozgrid.com/index.php?attachment/72256/

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

• ## Algorithm / Assign Max Value

Re: Algorithm / Assign Max Value

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

• ## Algorithm / Assign Max Value

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.

Code
``````Case sp(Cells(r, "J"), "") And sp(Cells(r, "I"), "")
cnt = cnt + 1``````
• ## Algorithm / Assign Max Value

Re: Algorithm / Assign Max Value

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

• ## Algorithm / Assign Max Value

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

forum.ozgrid.com/index.php?attachment/72233/

• ## Percentage true value

Re: Percentage true value

Works, Thx !!