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

preview_player
Показать описание


Fantastic Developer Tools:
Рекомендации по теме
Комментарии
Автор

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!

KevinPGA
Автор

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

philipclarke
Автор

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

ExcelVbaIsFun
Автор

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:)

karaavelilavlini
Автор

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. 

uajf
Автор

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

shihabkhan
Автор

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..

theseventhfinger
Автор

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!

saat
Автор

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

ExcelVbaIsFun
Автор

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

cjuwells
Автор

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. . .

ExcelVbaIsFun
Автор

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

victorbarlian
Автор

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

MohammadTaha
Автор

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?

michaelmartin
Автор

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

ExcelVbaIsFun
Автор

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

alfietankokleong
Автор

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

DavidStone
Автор

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

archanaaa
Автор

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

ExcelVbaIsFun