Old question, but I thought I would add to the quality of the answer by an answer I found at an excel forum. Apparently the count can also be found using. count =Len(string)-Len(Replace(string,”https://stackoverflow.com/”,””)) Full credit for the answer goes to the original author at:http://www.ozgrid.com/forum/showthread.php?t=45651
I know you asked for a non VBA way, but for completeness I’m adding another answer that also has a VBA solution, because it’s dead simple, it’s blazingly fast, and it’s generic. All you need to do is cut and paste this code into a standard code module, add a button and assign it to … Read more
The good news is that you can throw away the SUMPRODUCT function and replace it with a pair of COUNTIFS functions. The COUNTIFS can use full column references without detriment and is vastly more efficient than the SUMPRODUCT even with the SUMPRODUCT cell ranges limited to the extents of the data. In N2 as a … Read more
You may be tempted by adding your own string “Executer”: Sub StringExecute(s As String) Dim vbComp As Object Set vbComp = ThisWorkbook.VBProject.VBComponents.Add(1) vbComp.CodeModule.AddFromString “Sub foo()” & vbCrLf & s & vbCrLf & “End Sub” Application.Run vbComp.name & “.foo” ThisWorkbook.VBProject.VBComponents.Remove vbComp End Sub Sub Testing() StringExecute “MsgBox” & “””” & “Job Done!” & “””” End Sub
If the user clicks Cancel, a zero-length string is returned. You can’t differentiate this from entering an empty string. You can however make your own custom InputBox class… EDIT to properly differentiate between empty string and cancel, according to this answer. Your example Private Sub test() Dim result As String result = InputBox(“Enter Date MM/DD/YYY”, … Read more
Try using this: =ISNUMBER(SEARCH(“Some Text”, A3)) This will return TRUE if cell A3 contains Some Text.
You might be making this more complicated than you need to. As a programmer, I’m sure you appreciate that when learning a new system, a little time with the help, Google, “Dummies” books, etc. is time well spent. Excel emphasizes a very different programming style than, say, C#. So, here are some options, starting with … Read more
Whenever Excel creates a filtered list on a worksheet, it creates a hidden named range in the background in the Name Manager. This range is not normally visible if you call up the name manager. Use the below code to make your hidden named ranges visible in the name manager (prior to using it, set … Read more
The construct of INDEX() >>:<<INDEX() will work when applied to ranges. Not to arrays AFAIK. It will lead to these errors. Maybe try something like: =LET(range,A1:D2,MAKEARRAY(ROWS(range),COLUMNS(range),LAMBDA(r,c,SUM(INDEX(range,r,SEQUENCE(c)))))) This would resemble the construct of your inital formula. However, in the linked question you have mentioned that you’d like to use SCAN() in combination with BYROW(). You have … Read more
This is the basic principle, I assume that customizing this code is not what you are asking (as no details about this were provided): Sub Colors() With Range(“A1”) .Value = “Test” .Characters(2, 2).Font.Color = vbGreen End With End Sub Small description although it speaks quite for itself: the first “2” refers to the first character … Read more