Posts by Sp0ns

Sp0ns · Jan 17th 2018

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

Thanks in advance,

Maarten

Sp0ns · Sep 5th 2017

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

Sp0ns · Aug 31st 2017

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:

Sp0ns · Aug 31st 2017

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 !

Sp0ns · Aug 31st 2017

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,

Maarten

Sp0ns · Aug 31st 2017

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:

Code

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByColor = cntRes
End Function

Display More

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 ?

Thanks in advance !

Maarten

Sp0ns · Jun 12th 2017

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 ?

Sp0ns · Jun 12th 2017

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 ?

Sp0ns · Apr 26th 2017

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

Another one that works like a charm
And again I'm impressed with your ease of adjustment and kindness.
Thank you, Adrian !

Sp0ns · Apr 26th 2017

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,

Maarten

Sp0ns · Apr 26th 2017

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

Works like a charm, I thank you alot !

Sp0ns · Apr 26th 2017

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.

Sp0ns · Apr 26th 2017

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

Sp0ns · Apr 25th 2017

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

Sp0ns · Apr 24th 2017

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

Sp0ns · Apr 24th 2017

Re: Algorithm / Assign Max Value

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

Code

Sub CalcLSOComplexity()
Dim nRow As Long, r As Long, cnt As Long, rCell As Range
    
Sheets("Code").Activate
     
    nRow = Range("A" & Rows.Count).End(xlUp).row
         
    For r = 6 To nRow
    
    Select Case True
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "D"), "W")
    cnt = cnt + 3
    Debug.Print cnt
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "F"), "SR")
    cnt = cnt + 3
    Debug.Print cnt
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "G"), "SI")
    cnt = cnt + 3
    Debug.Print cnt
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "D"), "D")
    cnt = cnt + 2
    Debug.Print cnt
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "D"), "Dd")
    cnt = cnt + 2
    Debug.Print cnt
Case Len(Cells(r, "J") = 0 And Len(Cells(r, "I") = 0))
    cnt = cnt + 1
    Debug.Print cnt
Case sp(Cells(r, "J"), "Vg") And sp(Cells(r, "I"), "Sy")
    cnt = cnt + 1
    Debug.Print cnt
Case sp(Cells(r, "J"), "Vg") And sp(Cells(r, "I"), "")
    cnt = cnt + 0
    Debug.Print cnt
End Select
        
    Next r
Sheets("Counts").[N32].Value = cnt
cnt = 0
End Sub

Display More

Sp0ns · Apr 21st 2017

Re: Algorithm / Assign Max Value

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

Code

Sub CalcLSOComplexity()


    Sheets("Code").Activate
     
    nRow = Range("A" & Rows.Count).End(xlUp).row
         
    For r = 6 To nRow
    
    Select Case True
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "D"), "W")
    cnt = cnt + 3
    Debug.Print cnt
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "F"), "SR")
    cnt = cnt + 3
    Debug.Print cnt
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "G"), "SI")
    cnt = cnt + 3
    Debug.Print cnt
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "D"), "D")
    cnt = cnt + 2
    Debug.Print cnt
Case sp(Cells(r, "I"), "Sy") And sp(Cells(r, "D"), "Dd")
    cnt = cnt + 2
    Debug.Print cnt
Case sp(Cells(r, "J"), "") And sp(Cells(r, "I"), "")
    cnt = cnt + 1
    Debug.Print cnt
Case sp(Cells(r, "J"), "Vg") And sp(Cells(r, "I"), "Sy")
    cnt = cnt + 1
    Debug.Print cnt
Case sp(Cells(r, "J"), "Vg") And sp(Cells(r, "I"), "")
    cnt = cnt + 0
    Debug.Print cnt
End Select
        
    Next r
Sheets("Counts").[N32].Value = cnt
cnt = 0
End Sub

Display More

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

Sp0ns · Apr 20th 2017

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

Sp0ns · Apr 20th 2017

Dear colleagues,

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

Code

Sub CalcLSOComplexity() 
Dim nRow As Long, r As Long, cnt As Long, rCell As Range 
Sheets("Code").Activate
     
    nRow = Range("A" & Rows.Count).End(xlUp).row
         
    For r = 6 To nRow
        If sp(Cells(r, "J"), "Vg") And sp(Cells(r, "I"), "") Then
            cnt = cnt + 0
            Debug.Print cnt
        End If
      
        If (sp(Cells(r, "J"), "Vg") And sp(Cells(r, "I"), "Sy")) Or (sp(Cells(r, "J"), "") And sp(Cells(r, "I"), "")) Then
            cnt = cnt + 1
            Debug.Print cnt
        End If


        If sp(Cells(r, "I"), "Sy") And (sp(Cells(r, "D"), "D") Or sp(Cells(r, "D"), "Dd")) Then
            cnt = cnt + 2
            Debug.Print cnt
        End If
       
        If sp(Cells(r, "I"), "Sy") And (sp(Cells(r, "D"), "W") Or sp(Cells(r, "F"), "SR") Or sp(Cells(r, "G"), "SI")) Then
            cnt = cnt + 3
            Debug.Print cnt
        End If
    
    Next r
Sheets("Counts").[N32].Value = cnt
cnt = 0
End Sub


Function sp(r As Range, s As String) As Boolean
      
     Dim i As Long, v
      
     sp = False
     v = Split(r, ",")
      
     For i = LBound(v) To UBound(v)
         If Trim(v(i)) = s Then
             sp = True
             Exit Function
         End If
     Next i
 End Function

Display More

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/

Sp0ns · Apr 18th 2017

Re: Percentage true value

Works, Thx !!

Posts by Sp0ns

Sum average networkdays if cells aren't empty

Setting different print areas on multiple sheets

Counting colors and updating formula

Counting colors and updating formula

Counting colors and updating formula

Counting colors and updating formula

Rounding digits

Rounding digits

Counting appearance of non-input and else count multiple input

Counting appearance of non-input and else count multiple input

Counting appearance of non-input and else count multiple input

Counting appearance of non-input and else count multiple input

Counting appearance of non-input and else count multiple input

Algorithm / Assign Max Value

Counting appearance of non-input and else count multiple input

Algorithm / Assign Max Value

Algorithm / Assign Max Value

Algorithm / Assign Max Value

Algorithm / Assign Max Value

Percentage true value