Simple! Here is what we are going to get! 3 Steps Only: Define a range to use as the lookup value Create the dropdown list Paste in some code Step 1: Setup Sheet2 like this and define a Named Range as _descrLookup: ( Highlight -> Right-Click -> “Define Name…” ) This is an optional step, … Read more
I use and recommend Rubberduck. Description from the website: Rubberduck is a very active open-source COM add-in project that integrates with the Visual Basic Editor to enable the features every programmer wants to have in their IDE. From unit testing to source control, from code inspections to refactorings, programming in VBA will never be the … Read more
You would call the sub as EnterCellValueMonthNumber “N23:Q23”, 1 No brackets. Or Call EnterCellValueMonthNumber(“N23:Q23”, 1) Brackets, and Call before it. Also, your Sub is expecting a Range object as the first argument and you’re supplying a string; you should change the signature of the sub to: Sub EnterCellValueMonthNumber(cells As String, number As Integer) Also, I’m … Read more
Let’s say that your time value is in cell A1 then in A2 you can put: =A1*1000*60*60*24 or simply: =A1*86400000 What I am doing is taking the decimal value of the time and multiply it by 1000 (milliseconds) and 60 (seconds) and 60 (minutes) and 24 (hours). You will then need to format cell A2 … Read more
Add the following to the top of your module: Option Private Module From MSDN: When a module contains Option Private Module, the public parts, for example, variables, objects, and user-defined types declared at module level, are still available within the project containing the module, but they are not available to other applications or projects.
=CONCATENATE(LEFT(A1,1), B1) Assuming A1 holds 1st names; B1 Last names
I don’t know if it’s entirely possible to do your ideal pattern. But I found a way to do your first way: CountIF +——-+——————-+ | A | B | +——-+——————-+ | GL15 | =COUNTIF(A:A, A1) | +——-+——————-+ | GL15 | =COUNTIF(A:A, A2) | +——-+——————-+ | GL15 | =COUNTIF(A:A, A3) | +——-+——————-+ | GL16 | =COUNTIF(A:A, … Read more
You are getting that error because you have a module with the same name as the function. Change that name to say find_Purchase and everything will be fine 🙂 See the image below…
I’m pretty sure this can be solved with the indirect() function. Here’s a simplified spreadsheet: A B C D … +——————————————————+- – – – – – – – – 1 |CITY |Q1-Q3 SALES|ANNUALIZED SALES:(Q1+Q2+Q3)*1.33| +======================================================+- – – – – – – – – 2 |Tampa | $23,453.00| $31,192.49| +——————————————————+ 3 |Chicago | $33,251.00| $44,223.83| +——————————————————+ … Read more
Have a look at Capture Autofilter state To prevent link rot, here is the code (credit to original author): Works with Excel 2010, just delete the commented line marked. Sub ReDoAutoFilter() Dim w As Worksheet Dim filterArray() Dim currentFiltRange As String Dim col As Integer Set w = ActiveSheet ‘ Capture AutoFilter settings With w.AutoFilter … Read more