Nov 11 2018 12:40 PM
Hi
i have a worksheet in Excel where i need to count cells (with names, omitting blanks), that have not been hyperlinked (the hyperlinked cells are colored green) and transfer result to a list. I need it to count how many times a name appears.
Basically it is a list of names and how many times they have not submitted paperwork.
I have attached a small example to make it easier to understand
I have tried a number of functions, but just cant get it to work, so hoping someone can help
Thanks in advance
Phil
Nov 11 2018 01:41 PM
SolutionSee the attached example.
You can get what you need, by using UDF and COUNTA function.
I have put as an example in T2 this =COUNTA(B2:Q2)-HyperlinkFound(B2:Q2)
and the UDF embedded in the workbook is this
Public Function HyperlinkFound(Rng As Range) HyperlinkFound = Rng.Hyperlinks.Count End Function
Nov 20 2018 12:40 PM
Hi Jamil
thanks for this, it works a treat. However, can i have it either to auto calculate or be ran from button click (Active X button). I have tried to link it to a button, but cant get it to run
thanks
Phil
Nov 20 2018 02:03 PM
Hi Philip,
I have attached the version that you can populate the formula via macrobutton.
Nov 22 2018 11:20 AM
Hi Jamil
thanks for that. However, i tried the button and it didn't work. No worries though.
When i attached the original doc i had all "same" name one one row, where as actually the names differ.
What i have tried to do (but cant!) is count (as originally describer) the names without hyperlinks. You gave me the code for that - thanks. But what i actually need to count is the whole sheet, as names will not necessarily be in same row, which makes it difficult to accurately count the cells without hyperlinks for a particular person. I have attached a doc which shows it better, thanks and i hope you can help as i am stumped!
Nov 26 2018 10:57 AM - edited Nov 26 2018 10:58 AM
Hi Philip,
here is the modified UDF.
I have also embedded this into the workbook. you can run the macro.
Function myCountIf(rng As Range, criteria) As Long Counter = 0 For Each cell In rng.Cells If cell.Value = criteria And cell.Hyperlinks.Count = 0 Then Counter = Counter + 1 End If Next cell myCountIf = Counter End Function
Nov 26 2018 01:32 PM
Hi Jamil
you're a genius - i have tried numerous codes etc, but not been able to get this. (The only way i was able to get this was through creating a macro to sort into fill and no fill cells [for hyperlinks] and then use custom sort and functions for the names to get the totals - but this is too long a function and it slowed my doc down), yet what you have shown me .......this is exactly what i need. Thank you so much, you have helped me no end. I can now eventually finalise my doc, thanks to you :thumbs_up::thumbs_up::thumbs_up:
Nov 26 2018 02:48 PM
Dec 07 2018 01:02 AM
Hi Jamil
just one last thing - i need to have the calculation box (with the names and numbers on) and the macro button on a separate sheet (say, sheet 2). I have tried, but cant get it to work - could you help again please.
thanks in advance
Philip
Dec 07 2018 07:25 AM
Hi philip,
here it the attachment as per your latest request.
Dec 11 2018 02:09 PM
Dec 11 2018 03:18 PM
Nov 11 2018 01:41 PM
SolutionSee the attached example.
You can get what you need, by using UDF and COUNTA function.
I have put as an example in T2 this =COUNTA(B2:Q2)-HyperlinkFound(B2:Q2)
and the UDF embedded in the workbook is this
Public Function HyperlinkFound(Rng As Range) HyperlinkFound = Rng.Hyperlinks.Count End Function