I have your solution ready for you. I've PM'd you my payment details 
Posts by trunten
-
-
I can look at this for you
Could you confirm you have paid the 10% to ozgrid?
-
Don’t know how I’ve avoided it for so long then. It would even help if it was consistent. I had code that was working fine for ages, then I hid a completely unrelated column and things just started breaking! Very confusing 😂
-
First off, appologies if this isn't the right forum for this but I wasn't sure where would be most appropriate.
Now, onto the strange behaviour of SpecialCells(xlCellTypeVisible). Not sure if this is specific to my version of excel (2016) but if you query SpecialCells(xlCellTypeVisible) on a single cell with now rows or columns anywhere on the sheet it will return just the one cell. However, if there is a column or row anywhere on the sheet is hidden then it gets very weird. For example if I run the following code:
I get the the following results:
No rows/columns hidden: $A$1
Column D hidden: $A:$C,$E:$XFD
Row 12 hidden: $1:$11,$13:$1048576
Row 12 & Column D hidden: $A$1:$C$11,$E$1:$XFD$11,$A$13:$C$1048576,$E$13:$XFD$1048576
Pretty sure nobody would expect that behaviour so if it's more wide spread than just excel 2016 it's probably something that you need to take into account!
Attached a workbook just to show what I mean.
-
No problem. glad I could help.
-
-
One question though. Could you not use SUMIFS?
-
might be able to avoid multiple loops with a bit of a hack like so:
Display MoreCode- Sub summarise()
- Dim dataSheet As Worksheet
- Dim summaryTable As Range
- Dim data, summary, d
- Dim i As Long, x As Long
- Dim k As String
- Set dataSheet = Sheets("Data")
- Set summaryTable = Sheets("Summary Table").Range("A1:C7")
- Set d = CreateObject("Scripting.Dictionary")
- summary = summaryTable.Value
- data = dataSheet.Range("A1").CurrentRegion.Value
- For i = 2 To UBound(summary)
- d(summary(i, 1) & "|" & summary(1, 1) & "|" & summary(i, 2)) = i
- summary(i, 3) = 0#
- Next i
- For i = 2 To UBound(data)
- k = data(i, 1) & "|" & data(i, 3) & "|" & data(i, 8)
- If d.exists(k) Then
- x = d(k)
- summary(x, 3) = summary(x, 3) + data(i, 7)
- End If
- Next i
- summaryTable.Value = summary
- Erase data
- Erase summary
- Set dataSheet = Nothing
- Set summaryTable = Nothing
- Set d = Nothing
- End Sub
I've also attached an example so you can see how it may work
-
Payment received. Many thanks
-
No problem. Glad I could help.
PM’d you my payment details
-
Think I'm all done. Take a look and let me know if it meets your needs
-
Great stuff. I’ll be in touch
-
I can have a look for you.
Ozgrid PayPal is [email protected]
Just let me know when you’ve paid the 10%
-
If you change the formula to this that should sort you out:
=AND(COUNTIF($B2,Sheet1!$B$3)>0,$M2<Sheet1!$D$3)
-
Formula should be kept the same, you just need to change the range that it applies to
-
any better?
-
This any good?
-
Not 100% sure what you mean. Is this what you're looking for? https://docs.microsoft.com/en-…excel.workbook.refreshall
-
Ok, so this edit now adds "late" if there's a time in column b, "left early" if there's a time in column c but also just enters any text in b/c as a fallback.
-
I'm really struggling to get at what you're asking for but decided to have a go anyway.