Excel VBA Basics #10 - Looping through a database and analyzing cells based on criteria

Fantastic Developer Tools:

[KevinPGA]

I hope you never take these videos down. I learn something new EVERY time I watch them. VBA is so deep, how in the world did we figure out software like this before the internet?!?!?!?!? Thanks Daniel!

[philipclarke]

Hi Dan,
I love your simple and straight forward style. I have an estimating spreadsheet that is the data to produce a PDF summery report. Column 1 is category (eg: Building, Painting), column 2 is description, column 6 is line total. The logic is to record each line where columns 6 Line Total is greater than zero (hide all others), then store category, description and line total as variables (and copy to new worksheet). Then when printing the worksheet to PDF, group each category together and calculate sub total. EG: 3 lines for building selected at $200, $300, $400 with the building category sub total of $900 printed in the report. Then move onto the next category etc. I also want to auto size the rows and remove any blank rows and columns from the PDF report so I don't get multiple empty pages. Many thanks, Phil

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[karaavelilavlini]

Thank u. Sorry for confusion on days and months. Actually in France, 1/4/2013 means 1st of april:) In the USA, it is 4th of January, Iguess~ I've already found it by increasing quarterly. but the logic was this. Thank u again for the videos, which are very helpful and ur post-video mentoring:)

[uajf]

Thanks very much for posting this series it's really helpful.  There were plenty of examples of one-off VBA tricks and ideas before but a progressive series like yours is so much more useful to people like myself who are trying to build up their skills. 

[shihabkhan]

Very helpful videos. Thanks a lot for cutting through the bulshit and keeping your videos relevant :P
Appreciated

[theseventhfinger]

Thanks so much for this lesson
i am just wondering what is the name of this workbook you are working at?

I cannot find it in the dropbox you provided..

[saat]

Hi Dan, great videos as usual. I took your course on Udemy and enjoyed it as well. A small request: can you upload the code you type in each video as either an attachment, or place all lines of code pertaining to each group of videos in your play list in one document? That would help a lot. Thanks a gain, you rock!

[ExcelVbaIsFun]

I'm not sure if I follow: Depends what you have i set to and what j is set to. for example:

Sub Test()
i=2
j=4
cells(i, j)="blah"
End Sub

so in that example the cell we're referencing is row 2 column 4 aka "D2", so cell D2 would have the word "blah" in it. If you want i or j to be a different reference, you have to tell it to refer to some other area. Let me know if you'd like further explanation, i know its tricky at first! Dan

[cjuwells]

Hi Dan. Enjoying videos, also been looking at Mikes. Just wondering if you could advise title of this workbook as couldn't find it on dropbox, will type a few rows just so I can follow along

[ExcelVbaIsFun]

I can make a video, but it sure helps if you already know the name(s) of the workbook(s), the location of the workbook(s) and the sheet(s) you want to extract into your new or existing workbook. If you want ALL sheets from each workbook it becomes easier. A loop would be in order, eg.
for each ws in thisworkbook.worksheets
blah blah do this do that
next ws. Please gimme the details and i'm sure we can come up with a lesson in the Tips N Tricks section to review this. Thanks! Dan

[ExcelVbaIsFun]

Most of the initial lessons can be found on a wb named "ExcelVBAIsFun lesson 9.xlsm" For some reason, I just stuck with this one for awhile. . .

[victorbarlian]

Hi Dan, just wondering do you have the file for this particular video ? Many Thanks !

[MohammadTaha]

this macro performs something like SUMIFS function>>>> that's very very good thank you

[michaelmartin]

Hey I cannot access your worksheets so I can follow along? Anyway you help so I can go through all of your videos and follow along?

[ExcelVbaIsFun]

i meant for it to put a space after x=2 to whatever. didn't work. Dan

[alfietankokleong]

Good vid.  The approach would be much faster if you simply dump it to an array and use application.match

[DavidStone]

can you elaborate a little more on the logic behind "counter"?

[archanaaa]

Hello sir, y u used counter in for loop. What is d use of that? I dint get

[ExcelVbaIsFun]

Geronimo, thank you and thank you!! and you're welcome. lol. Daniel