lol! Thanks Kevin! Yes, I don't think any of us would know Excel worth a crap without Mike (ExcelIsFun channel). Just learning Vlookup from him is enough to get everybody hooked! Thanks for the comment and the "Like". Dan

Thank you so much for your kind and encouraging words as I embark to put together a similar Excel VBA course for my company.

That's awesome!

Andrew, You're very welcome. I'm planning on buckling down and doing a series on making reports on raw data to help the community build even more awesome skills. Thanks, Dan

Hi Ben, So glad this is helpful! Thank you for the positive comments! Keep up the good work, my friend! Thanks Dan

Thank you andrew!!

Hi , is it possible to get the workbook for follow up the examples screened in the videos

Thanks Mohammad, I appreciate the comments and the "Like".

You're welcome Dan

Hi Dan, don't you need to declare what counter is before you use it?

I was thinking the same thing. I agree with you on principal that one should ALWAYS assign a variable to a known value before using it. I.e. in this case, I would've liked to see a 'counter = 0' line prior to the counter being iterated in the for-loop (where it is first 'used', i.e. appears on the right-hand side of the '='). HOWEVER, technically you do NOT need to initialize this variable as VBA will interpret it as an Integer, which will ALWAYS have a default value of the number 0 when first used. Note here that VBA knows this variable is an Integer even without a 'Dim counter As Integer' line because Daniel added the variable to the known data in Cell(x, 3), which is an Integer and so defines the counter variable based on this context (at least I am 95% sure this is how it works).More info on this topic can be found at: stackoverflow.com/questions/7626967/vba-variable-array-default-values. Lastly I note that DEFINING all variables with a 'Dim ... As ...' line is also good practice, however Daniel notes in a previous video that this level of rigor is not required and I would agree; I think it somewhat more important to explicitly initialize variables than define them, if only for readability (at least as far as VBA goes).

No prob bob!

Very welcome, John!

Maybe! not sure, i'll have to look into it! Good question. Dan

Ah, it was mentioned before in the comments... Nevermind :)

Here is the rough pseudo-code for your request: ' Assumes your data spans row 2 to row 'lr' (lastRow) ' See Dan's video above for how to set 'lr' for rr = 2 to lr Cells(rr, 5) = Worksheetfunction.NextworkDays(Cells(rr, 4), Cells(rr, 3)) next rr Alternatively you could place the ACTUAL function in the cells instead of the calculated value, which would mean it would update dynamically if column C or D changed (not sure if this is what you want). To do this, simply change the line in the for-loop to: Cells(rr, 5).Text = "=NETWORKDAYS(..., ...)"

I feel you modern. Just bear in mind that this video is geared towards newbies/non-programmers (which I think is great!) and thus might take some work to warm them up to Arrays...and the more 'down to earth' the content is (i.e. the easier it relates to how they would manually manipulate the Excel file), the easier it will be for newbies to absorb :).

I am sure you could use that as well. But the 'selection'-command allows you to change the categories 'on the fly'. Otherwise you'd have to manually change them in the code every single time for different queries.

Thanks orlando, I've been planning on it for some time. Thanks for your support! God bless. Dan

If you omit to declare a variable (using Dim and specifying what type of variable it is), the default variable type will be of type Variant. A Variant is a special data type that can contain any kind of data. So, in his example, he didn't declare "counter". I personally would have declared it just to avoid confusion. Ok, so he didn't declare it, so it is automatically a Variant. When VBA encounters the code "counter = counter + dbsheets.cells(x,3)", it does this: "nothing = nothing + 1067", so at that point it knows that the variable counter will be used to store numbers. If there's more than one hit in the loop, the next time it will do "counter = counter + whatever", so let's say "counter = 1067 + 300". At that point, counter will keep on incrementing. If you were to just say counter = dbsheets.cells(x,3), then counter would only take the value of the last hit in the loop, but that's not what we want, we want the sum!

@@faustprivate Awesome explanation. I understand now. Thank you!

@@faustprivate " If there's more than one hit in the loop, the next time it will do "counter = counter + whatever", so let's say "counter = 1067 + 300". At that point, counter will keep on incrementing." But how does VBA know that "counter" on the right side of the equation is referring to the sum of all the previous "counters"? It's like saying x = x+3. It doesn't make sense at first sight. Do you have any link where similar instances are presented? Thank you

Can you elaborate on this request? When you say 'report', is it an Excel workbook? What format is it in (columns and rows). If not a workbook, what sort of structure does it have? (assuming text file) Fundamentally though, you would simply right your 'false' check and not take any action on any other value. I.e.: If (myVal = false) Then Return Else ' Do nothing End If

Yes, your video just shows a loop through where there is something on each consecutive line. What would happen if there was a break and there was nothing and the rest of your information was another four lines down after the break. Your loop would only look if there was not break in that loop.

subha: grab or reproduce a copy of Dan's code above and then simply look at the 'dbsheet.Cells(thing1, thing2)' lines and modify the 'thing2' as you require. If this is a little daunting, you can explore the code as you go with 'freezing' in debug and hovering over things (as Dan teaches in previous videos). I also like to 'see' the cells I am interested in on the actual spreadsheet, which you can also do when 'frozen' in debug mode. To do so, simply select them via the 'immidiate' window in the VBA editor. E.g. put that window side-by-side with your Excel sheet and then type 'dbsheet.Cells(thing1, thing2).Select', and then hit ENTER. You don't even have to retype it each time you want to use it, just click back to that line in the immediate window and hit ENTER again. Hope that helps!

by default, if you don't add the statement "Option Explicit" at the top of your module, VBA will let you type code with variables that weren't specifically declared before. So you can start any line with let's say "whatever = " or "thisismyvariable = " and this will implicitely declare these randoms words as variables of type Variant. A Variant is a special data type that can contain any kind of data. he didn't declare it, so it is automatically a Variant. When VBA encounters the code "counter = counter + dbsheets.cells(x,3)", it does this: "nothing = nothing + 1067", so at that point it knows that the variable counter will be used to store numbers. I highly recommend using Option Explicit at the top of your module to force you to declare all variables and choose a data type prior to using the variables. This will save you TONS of problems in the future. :)

The short answer is that all variables are ALWAYS set to a 'default' (rather than random garbage) value in VBA. In this case, Integers get set to the numeric value 0. If curious, I have a longer tirade on this topic up above, two replies under Dan's post about his piano lesson channel.

Hi Alan, Please check out my new URL for workbooks library: excelvbaisfun.com/free-workbooks/ Sometimes dropbox links don't work well with many people trying to download... Thanks Dan

Variables will appear when u get to debugging mode, so type "F8"

and of course the values will only appear *after* the specific line of code is ran. if you have x = 2 on the third line of your module but you're debugging the macro and you are at the second line, it hasn't gone THROUGH line 3, so obviously at that point x is equal to nothing! Only have line 3 is ran, x will be equal to 2.

counter here is a random word that he made up which is not recognized by VBA. He could have typed "MySuperDooperNiceCounter" and it would have done the same thing. To make things clearer, he should have declared it at the top of the page by typing Dim Counter as Long. By not declaring it, he let VBA "guess" what he wanted to do. It's a dangerous shortcut that most beginners shouldn't take. I recommend using the "Option Explicit" statement at the top of the page in the module. That way, the statement "Counter = Counter + 123456" would have returned an error -> "Variable not defined". So to answer your question, no Counter isn't a "reserved VBA term" like xlup or xltoleft, it's just a random name that he made up for his variable which he omitted to declare (on purpose, but somehow out of laziness!!)

You can use Application.WorksheetFunction or Range.Formula Property or Evaluate Method etc. There are so many ways. But you have to tell me what exactly you are looking for with sample data.

Melishan: I do not know if there is a way to do this with VLOOKUP as you are looking to chain two 'filters' together and your first VLOOKUP will only return ONE match, not ALL matches. Rather, I would encourage you to try to modify Dan's code that he goes over in your video to do what you want; it shouldn't be as hard as it sounds. The basic execution here would go something like (looks better if you copy-paste to VBA editor): ' which column's data do you want to return when the criteria match? retCol = 1 ' Loop over each row of your output data (where you would've written the VLOOKUP) For each row R1 of outputSheet ' Where you will write the result for each row myOutCell = Cells(R1 , myOutCol) ' First Criterion myCrit1 = Cells(R1 , myCrit1Col) ' Second Criterion myCrit2 = Cells(R1 , myCrit2Col) ' Now loop through input data (that would've been first parameter in VLOOKUP). This loop is similar to Dan's in this video. For each row R2 in inputSheet ' Define inputCrit's here, like myCrit's above ... ' Look for a match of BOTH criteria If (myCrit1 matches inputCrit1) And (myCrit2 matches inputCrit2) Then myOutCell = Cells(R2, retCol) ' Found match so go to next iteration of outer loop Goto nextOutRow End If Next R2 ' End inner for-loop 'If we got here, we didn't find a match myOutCell = "No match found!" 'landmark for the 'Goto' line above nextOutRow: Next R1 ' End outer for-loop Hope that helps, let me know if you need more help hammering out the details from there!

Most people have problems because they type XIUP or X1UP, instead of XLUP. Maybe try that first? Thanks Dan

Hello Dan, this was not my case. I still can't figure out what the problem was but i downloaded the file again and went step by step and now it works just fine. Sorry about that and thanks for replying :) Brigitta

Nice work! Glad you got it working!

Yes, he could have. It's a more Advanced function and I believe that he is going step by step, with baby steps.

VBA offers multiples ways to achieving the same results. SUMIFS would work perfectly but is more Advanced, which isn't the goal of this video. But you are right :)