I feel that the fact you cannot undo a macro should be one of the first things taught and mentioned throughout the lessons! Good thing I found out about it now before I applied this to my real work. Thanks for the great videos I will keep working on them!
thanks for this tutorials i'm quite late for school because i need to rest in the hospital due to sickness and your videos kinda help me to learn and to catch up with todays lecture. thank you so much. Subscribed.
Hey Dan, thanks for a very prompt reply!! really appreciate it. always wanted to learn VBA. your Venture into VBA Tuts has made my Learning Curve n Experience a smoother one. Many a Thanks and Appreciation for your Kind Endeavor. Best regards Abhijit
Good point, sjsawyer! Yes, it's important to SAVE SAVE SAVE! Because if you messed up, you may have to go back quite a few steps in order to get back on track. But its totally worth it to learn! THanks for your comments! Dan
I Have used Excel for a couple years now, and VBA Has always seemed way beyond my reach, but your lessons make it easy to follow along, and it doesn't seem like such a giant mountain to overcome anymore - thanks for all your videos- they are a big help !
Absolutely! I understand completely. Don't give up on books though, I've got one on the way with a ridiculous amount of screen captures and photos, very colorful and to the point. Lots of good stuff to learn, but more importantly, lots to copy and paste for immediate use in your codes. Dan
I am enjoying your videos! I believe that the options for these "if, then" statements and also the "for, next" are endless. I am on my way to modifying the "if, then" for the use of date stamping when a cell is modified. Will be continuing through the series.. Practice makes perfect!! Thank you for your videos!
Hi Jason! Not sure what you mean by spaces, do you want extra indentions, extra space bar spaces " ", or maybe extra lines/carriage returns? Also where and why do you want them. Be glad to help, sir, not sure whatcha need. Thanks!! Dan
Wow great job man. You made it very easy, and I learned a lot. I just hope you could show us keyboard shortcuts as well as mouse options because many of us find it hard to remember so many keyboard shortcuts. Thanks. Keep up with this.
Oh my! ok, I totally gotcha now. I just indent these by hitting the tab key. You can select entire paragraphs or huge sections of data and indenting them or un-indenting them using Tab or Shift-Tab. You can also use the indent tool in one of the toolbars, I think it looks like arrow keys.
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
Hi. Do any of you guys have any idea why my f8 key doesn't work/debug in vba? Instead of debugging, pressing f8 shows me a "pc screen only;duplicate;extend;second screen only" thing on the right hand side. Also, how can I save my code in the module. It only saves the book sheet when I tried to save, but it doesn't save the module. Thanks a lot! Read more
Hi Dan, the videos are just awesome! By the way, how could I have spaces automatically generated before each line within a loop? I think that shows different levels clearly and I don't have this function by default... Thanks in advance for your help!
Hi Daniel, this really very nice video and helping, here i want to know if we run this macro at one time time then how do we know where it will end, i mean which row and column.
I would like to congratulate you for this amazing and full knowledge channel it does justice to its predecesor :) sequels sometimes are better than the 1st time around :)
Thanks for your reply! Sorry for not being clear enough. I mean in this video for instance, you wrote a for loop as below: For x=1 to 10 Cells(x,1)=Date+7 Cells(x,2)=x*15 Cells(x,3)="Pizza" Next x Those three lines in the middle start with spaces, indicating the hierarchy of codes. But in my case, all statements are aligned to the left. Why is that ? Thank you!
Hi Dan, this is my day #2 and i am already addicted :), i have a question for you though. i am trying to improvise by adding an outer loop- from your tutorial i have build 2 For-Next loops - Outer loop and inner loop, i am trying to replicate these steps for a total of 2 set columns i.e inner loop creates colmn 1 - 3 populates date, price and type 'Pizza', Outer Loop start with 1, jump 3 columns and start at 4th column repeat the inner loop
Good afternoon, Dan. I'm a fan of ExcelVbaIsFun. I saw with pleasure that you offer a free book on your youtube channel. Try to download it by following the steps and read a message "There are problems or something like that". Do you have the book freely available ?. when will it be available? I really like your channel and I'm learning a lot in it. thank you very much
Hi J.A., the link should work better now. If you visit www.excelvbaisfun.com and sign up for our free eNewsletter, you can grab the ebook 100% free. Thanks Dan
Great video, thank you! I have a question that may not be directly related to the topic, but I haven't found anything that would match what I need. How is it possible to separate in Excel 2010 into individual columns a test string that contains different languages. For example: good хороший, bad плохой, Ugly некрасивый. I need English and other language separated. Thank you!
Thank you for this valuable video. This is so very useful. I hope to hear from you how to concatenate 5 columns if columns are greater than 1? looking forward to watching your next video. God bless...
I love that you are so strict in your formatting. I do wish you were more verbose in your variable names and also like many others too brief in naming sub routines. I was like that when I began 20 years agon in C++, abd later C#, but I took Microsoft and the very long and descriptive class names as my example going forward and never looked back. I did the same with learning to type. I decided early not to abbreviate words on the idea that more typing equalled more practice. I still avoid it but if I am writing something important, I use MS Word and the autocorrect feature to auto fill short phrases like typing MSM and Word replaces it with "Main Stream Media" quotes included. In my current spreadsheet I have names like txtDate, txtServingInputOne, cboDropDownOne etc. One of these days I must make an effort to stop copy/pasting subs because it has bitten me even recently.
Hey Daniel, do you know how many lines of code, or loops in that matter, Excel can do in one second? Because I wrote a macro which is running 14 For-Next loops To 1000 and it takes quite a while.
Dear TheTornado121, please check out some of my speed enhancing tips videos. They can make extremely long tasks pretty much instant. Here you go: How to Massively Speed Up Your Workbook and Make Macros Run Faster - Excel VBA Is Fun AND How to Speed Up Your Workbook - My SECRET EXCEL WEAPON AND A BONUS Type and Program WAY WAY FASTER Using this Little Known Program - FREE!!
hey Daniel... thankx for ur tutorials.... it helped me alot... but i a m stuck with writing a criteria code for if command i have a code that filters #N/A's... i want a if command code for removing the filter if there is no #N/A... can u pls help me with this code
Before i saw your video vba looks like a spider web. But after seeing ur video it become a piece of cake...i like ur videos and your approach and i would like to do this in more advance way...kindly help in doing that...thank you..
I'm working on some more advanced lessons. I'm actually coming out with an Advanced Report Generation paid course on Udemy.com. and a book. Glad you like, hunker!
Hi, how can you select the cell by column range and row range. For example my textbox1 value is august 3 and my textbox2 value is product 3 where the columns are august 1, august 2 and august 3 and my rows are product 1, product 2 and product 3. If I run the code the selected cell must be D3 because thats the value of my textbox1 and textbox2.. Hope you understand my sample. Thanks
Hi, I am trying to learn VBA with your videos and the videos are very good for beginners. I am trying to run a vba code that I have created, but every time I run the code the formula in the excel is being lost. When I am re-entering the data and running the program again formula is not being picked up and hence end result is not changing. I tried to lock the formula but its not working. Please help.
Hi, I created a VBA that will copy and paste entire rows if the if statements are met and copied to a specific tab. I created the command button to assign the macros. The data gets copied to the new tabs as designed. However, each time I click on the button it duplicates the data in the desire tabs. How can I create or add to my VBA not to duplicate the same data? Thanks
Hi. Do any of you guys have any idea why my f8 key doesn't work/debug in vba? Instead of debugging, pressing f8 shows me a "pc screen only;duplicate;extend;second screen only" thing on the right hand side. Also, how can I save my code in the module. It only saves the book sheet when I tried to save, but it doesn't save the module. Thanks a lot!
You have Date + 7, and the dates returned are 2/24/20143, so it shows 7 days from today. How about if I would like to have the date running at +7 for each row? like 1 Jan 2015, then 8 Jan 2015, 15 Jan 2015, etc? Thank you :)
Hi Dan. I hope you can help me. I have developed an Excel file that keeps cube information. I was asked to make a map to show the location of the cube. I made a user form with a map on it and labeled all of the cubes. I have more than one map that are unique by the first letter (i.e. A1100-1200, B1100-1200, etc). What I am trying to do is have another user form with a textbox when you enter the cube name and press the "map" button it will bring up the correct map. Each userform is a different letter (A, B, C). So if you enter A1100 in the text box, I need userfrom A to show. Can you help me with this?
Good day Mr. Dan! why do I get an error message of Run-time error "424": Object required for this command line Dim Lastrow As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Master") Lastrow = ThisWorkbook.Sheets("Master").Cells(Row.Count, 1).End(xlUp).Row - This is where the error message comes...
Hi, thanks a lot for your great work ! keep it up ... now in day i am watching your video and doing some small macro in office .. :) i need 1 help for below situation ... we are getting approx 200 line item data .. like as below Raw data code cost center amount AA XYZ 40 BB OPQ 40 and we need to split it code wise with defined % for defined cost center like as below Report Code Cost center amt AA AAA 10 AA BBB 10 AA CCC 10 AA DDD 10 BB MMM 20 BB NNN 20 i.e. for code AA i am inserting 3 blank row below > then deleting existing cost center (XYZ) and inserting new cost center > for first row AAA with 25% for second row BBB with 25% for 3rd row CCC with 25% and for fourth row DDD with 25% like wise for for code BB with 50% i have 7 types of code ... so i want to know just concept .. you reply will be appreciated thanks in advance !!! sam
Sure, see, my problem is that I receive the information on a table (matrix), and the information for every matrix is for just one day of the year, so in a .xsl file I have an entire year, in other words 365 sheets (one for each day), the objective of my macro is to be able to recollect the data from every single sheet, and copy it onto an unique sheet made just for gathering all the information, the thing is: 1. Since there are 365 sheets, my intention is to make a FOR o a WHILE LOOP to make the macro go through all the pages without making 10 thousand lines of repetitive code; 2. The data that has to be recollected are only 2 values, one of which is only a date that must be set in 11 merged cells. And the second one is a matrix of 6 by 11 that also has to be copied (I tell you the dimensions because my intent is to make some sort of integer variable that every time the loop cycles around gets summed 11 rows down to avoid having overwritten cells); 3. The process I've made is in this way (most likely it is the wrong approach, and it's not the code because I haven't got VBA code learnt, but its my way of solving my iteration ): go to sheet1 select D6 (which is the date), and copy it go to recollection sheet, paste the value in A2, and merge A2:A12 go to sheet1 select matrix E5:J16 and copy it go to recollection sheet, paste the values on B2 This is what needs to be looped, by my lack of knowledge I assume into 3 variables (sheets, date, matrix) the sheet just needs to go to the next one so I thought some sort of code of this sort: sheet1 = x, x++ (for every cycle completed). For the placement of the date inquiry result something like: date1 = y, y +11. To make the next sheet copy the date on the right cell (11 rows below the last one). And for the final variable, I thought the problem could be solved by doing the same as to the date1 but making a variable for the matrix. I have very little knowledge of coding but I do have some sort of idea of the works of it, not enough to be creative, so I respectfully beg for your help in this. Thank you for your time!
Hi Ed, Hmmm. Check out my other vid on looping through each worksheet in a workbook. This should help you out. th-cam.com/video/-75HKRrIMI8/w-d-xo.html Thanks Dan
it most certainly did help! thanks, I managed to write some sloppy code which won't run, if you'd be so kind as to review it, it is very short and very badly done, but please help me out, here it is : Sub WSLoop() Dim ws As Worksheet y = 2 For Each ws In ThisWorkbook.Sheets 'to avoid recolecting data from the recollection sheet If ws = "Data" Then GoTo label1 'selection of the date on first sheet ThisWorkbook.Sheets.Cells("B3").Select 'copy selection Copy.cell 'going to the sheet where all the information will be ThisWorkbook.Sheets("Data").Select 'selecting the adecuate cell and copying the date there ThisWorkbook.Sheets("Data").Cells(y, 1) = Paste 'going back to the first worksheet to select the matrix ThisWorkbook.Sheets.Cells("C4:D6").Select 'copying selection Copy.cell 'going back to the sheet where all the information will be ThisWorkbook.Sheets("Data").Select 'selecting the accurate position for the matrix ThisWorkbook.Sheets("Data").Cells(y, 2) = Paste 'adding the value expected for the next date y = y + 11
Next ws label1 End Sub I think it is only a matter of correct expression to make it run, hope you can help, and many thanks!
hmm. lets try something like this: Thisworkbook.sheets("Data").cells(y,2) = ws.range("b3") 'ws is the current sheet in the loop, so we need row y on data sheet to take b3 on that ws y = y +1 'next row! Thanks Dan
I really just want to do 1 specific workbook with VBA and don't have the time to learn it. Is there anyway I can just send you my workbook and you can do it for me? What I want to do is not that hard in my mind, I just can't write it out in VBA
Not sure but I believe, We have added in "Dates+7" so I guess dates should change accordingly but dates are remain same in video and my excel too :-( but I really enjoy this video as beginner..thanks
Try this instead: For x = 1 To 10 Cells(x, 1) = Date + x - 1 Next x *First cell("A1") starts with today's date, and the dates increment by 1 after every row.
You had me at tacos... now you've completely taken me over with pizza you son of an onion! :p hehe. Really enjoying your videos as I recently started getting into VBA, but started ahead and need these basics to start building upon.
You're very welcome. Please let me know if you have any specific questions, projects you're working on, or video ideas/topics for future vids. Thanks! Dan
Hi ExcelVbalsFun can you help me with this code?! I want to find "jas" in the range "J7:M40" i managed to do that. But now i want it to offset to the next selection/range "N7:Q40" and do the same seach. And this for 744 times. With the same offset (0 ,4) of colums every time. Hope you can help me! Dim wb As Workbook Dim ws As Worksheet Dim FoundCell As Range Set wb = ActiveWorkbook Set ws = ActiveSheet Blad3.Activate Range("J7:M40").Select Do Selection.Offset(0, 4).Select Const WHAT_TO_FIND As String = "Jas" Set FoundCell = Selection.Find(what:=WHAT_TO_FIND) If Not FoundCell Is Nothing Then
Blad5.Activate Range("D1").Select ActiveCell.End(xlDown).Select ActiveCell.Offset(1, 0).Select Selection = FoundCell.Row Else Blad5.Activate Range("D1").Select ActiveCell.End(xlDown).Select ActiveCell.Offset(1, 0).Select Selection = 0 End If Loop
Please can anyone help me. I have in sheet1 B1=PW, B2=MB, B3=VC and in Sheet2 I have D3=PW, D4=MB, D5=VC. I created the button macro to run when I select B1 in Sheet1 and I press the macro button to go automaticly to sheet2 to D3, the match name for PW...but when I select B2 in sheet1 and I press the macro button it is going to sheet2 and select D3 again instead of D4 which is the match to MB in sheet1..And again if I select B3 in sheet1 and I press the macro button I go to Sheet2 and select D3 again instead of D5. I dont know how to write the VBA to match when I select a cell in Sheet1 and press the macro button to go in Sheet2 and to be selected automaticly the matched cell with the name as same in cell in Sheet1...Thank you all
For y = 1 To 9 Step 3 'Outer Loop jump by 3 columns x = 1 'j = 1 Cells(x, y).Select j = ActiveCell.Column For x = 2 To 10 'Inner Loop populate cells with condition Cells(x, j) = Date + 7 ' example of date function to move by 7 days Cells(x, j + 1) = x * 15 If Cells(x, j + 1) > 100 Then Cells(x, j + 2) = "Pizza" Else Cells(x, j + 2) = "Do-nut" End If Next x ' end of inner loop Next y Q) is i m not able to get the code to copy headers i.e. date, price & type for2nd iteration
Mike, you are my hero. That you would watch any of my videos is an absolute honor! Dan
Just to say thank you to each video to appreciate your work and the way you teach.
I feel that the fact you cannot undo a macro should be one of the first things taught and mentioned throughout the lessons! Good thing I found out about it now before I applied this to my real work.
Thanks for the great videos I will keep working on them!
thanks for this tutorials i'm quite late for school because i need to rest in the hospital due to sickness and your videos kinda help me to learn and to catch up with todays lecture. thank you so much. Subscribed.
Hi nareshobula, the feeling is mutual. I'm glad you're learning a lot and THANKS for being so appreciative! Dan
Your videos are very easy to understand and self learn . Thank you for the amazing series on VBA
Hey Dan,
thanks for a very prompt reply!! really appreciate it.
always wanted to learn VBA.
your Venture into VBA Tuts has made my Learning Curve n Experience a smoother one.
Many a Thanks and Appreciation for your Kind Endeavor.
Best regards
Abhijit
Hey Dan,
thanks,this is a grt initiative by you,was looking for this kind of VBA tut....
Keep it up
Regards
Sujit.
Simple, clear and concise..kindly keep posting such excellent videos. Thank you for all your effort
Thanks Surya! Dan
Good point, sjsawyer! Yes, it's important to SAVE SAVE SAVE! Because if you messed up, you may have to go back quite a few steps in order to get back on track. But its totally worth it to learn! THanks for your comments! Dan
I Have used Excel for a couple years now, and VBA Has always seemed way beyond my reach, but your lessons make it easy to follow along, and it doesn't seem like such a giant mountain to overcome anymore - thanks for all your videos- they are a big help !
You are so welcome! Glad this helps, PERALTASTONES, you CAN do it! It's fun.
Thanks so much for these lessons. I am finding them so much more useful than trying to learn VBA from books.
Absolutely! I understand completely. Don't give up on books though, I've got one on the way with a ridiculous amount of screen captures and photos, very colorful and to the point. Lots of good stuff to learn, but more importantly, lots to copy and paste for immediate use in your codes.
Dan
You are so good to share this kind of knowledge one another.
luv rai thanks, luv rai!!
I am enjoying your videos! I believe that the options for these "if, then" statements and also the "for, next" are endless. I am on my way to modifying the "if, then" for the use of date stamping when a cell is modified.
Will be continuing through the series.. Practice makes perfect!!
Thank you for your videos!
Camper You're welcome!
Keep it up, you'll be conquering those workbooks in no time.
Dan
your videos are very easy to understand. Great tutorials! subscribed.
thanks for sharing
my interest in learning macros increased by watching your videos thank you
Hi Jason! Not sure what you mean by spaces, do you want extra indentions, extra space bar spaces " ", or maybe extra lines/carriage returns? Also where and why do you want them. Be glad to help, sir, not sure whatcha need. Thanks!! Dan
you explain really good and give the most important things in a very understandable way. thank you very much
Wow great job man. You made it very easy, and I learned a lot. I just hope you could show us keyboard shortcuts as well as mouse options because many of us find it hard to remember so many keyboard shortcuts. Thanks. Keep up with this.
Sure thing, What mouse options or keyboard shortcuts would you like, Ahmed? Thanks! Dan
Oh my! ok, I totally gotcha now. I just indent these by hitting the tab key. You can select entire paragraphs or huge sections of data and indenting them or un-indenting them using Tab or Shift-Tab. You can also use the indent tool in one of the toolbars, I think it looks like arrow keys.
I am so grateful. An excellent way of sharing valuable knowledge.
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
Hi.
Do any of you guys have any idea why my f8 key doesn't work/debug in vba? Instead of debugging, pressing f8 shows me a "pc screen only;duplicate;extend;second screen only" thing on the right hand side. Also, how can I save my code in the module. It only saves the book sheet when I tried to save, but it doesn't save the module.
Thanks a lot!
Read more
No Abhijit, I learned with just intermediate Excel knowledge. Dan
Ok I see. I think you talked about that in your next video of the list;
Thanks Dan. You are amazing
Thank you! Very helpful to a business person like me with conceptual understanding of Programming languages
So so glad it helps, Aiman!
My pleasure and learning opportunity!!
Awesome video, learnt a lot
great video thank you!!! I learn everyday with your videos
Thank you so much for these videos, although I have no idea what this language can be used for, but u explanation is perfect! :)
Hi Dan, the videos are just awesome!
By the way, how could I have spaces automatically generated before each line within a loop? I think that shows different levels clearly and I don't have this function by default...
Thanks in advance for your help!
Dan. any way you could make a video to show us an if then statement with a for each loop to select multiple pivot items in a pivot field?
Hi Daniel, this really very nice video and helping, here i want to know if we run this macro at one time time then how do we know where it will end, i mean which row and column.
I would like to congratulate you for this amazing and full knowledge channel it does justice to its predecesor :) sequels sometimes are better than the 1st time around :)
Francisco Valenzuela Thanks!!
Thanks for making these videos!
Thanks for your reply! Sorry for not being clear enough.
I mean in this video for instance, you wrote a for loop as below:
For x=1 to 10
Cells(x,1)=Date+7
Cells(x,2)=x*15
Cells(x,3)="Pizza"
Next x
Those three lines in the middle start with spaces, indicating the hierarchy of codes. But in my case, all statements are aligned to the left. Why is that ? Thank you!
Awesome Video!!!!
Your videos are helping me a lot!! thanks :D
So glad they're helping, Carlos!
Hi Dan, this is my day #2 and i am already addicted :), i have a question for you though. i am trying to improvise by adding an outer loop- from your tutorial i have build 2 For-Next loops - Outer loop and inner loop, i am trying to replicate these steps for a total of 2 set columns i.e inner loop creates colmn 1 - 3 populates date, price and type 'Pizza', Outer Loop start with 1, jump 3 columns and start at 4th column repeat the inner loop
Hi Dan, You are awesome.
Hey guys, these videos are great! Problem I'm having: running excel 2003. Does anyone know why this exact code doesn't work with this version?
Good afternoon, Dan.
I'm a fan of ExcelVbaIsFun.
I saw with pleasure that you offer a free book on your youtube channel.
Try to download it by following the steps and read a message "There are problems or something like that".
Do you have the book freely available ?.
when will it be available?
I really like your channel and I'm learning a lot in it.
thank you very much
Hi J.A., the link should work better now. If you visit www.excelvbaisfun.com and sign up for our free eNewsletter, you can grab the ebook 100% free.
Thanks
Dan
Great video, thank you! I have a question that may not be directly related to the topic, but I haven't found anything that would match what I need. How is it possible to separate in Excel 2010 into individual columns a test string that contains different languages. For example: good хороший, bad плохой, Ugly некрасивый. I need English and other language separated. Thank you!
Thank you for this valuable video. This is so very useful.
I hope to hear from you how to concatenate 5 columns if columns are greater than 1?
looking forward to watching your next video. God bless...
I love that you are so strict in your formatting. I do wish you were more verbose in your variable names and also like many others too brief in naming sub routines. I was like that when I began 20 years agon in C++, abd later C#, but I took Microsoft and the very long and descriptive class names as my example going forward and never looked back.
I did the same with learning to type. I decided early not to abbreviate words on the idea that more typing equalled more practice. I still avoid it but if I am writing something important, I use MS Word and the autocorrect feature to auto fill short phrases like typing MSM and Word replaces it with "Main Stream Media" quotes included.
In my current spreadsheet I have names like txtDate, txtServingInputOne, cboDropDownOne etc. One of these days I must make an effort to stop copy/pasting subs because it has bitten me even recently.
What is the use of Date + 7 since no change in the output in the A column?
Hey Daniel, do you know how many lines of code, or loops in that matter, Excel can do in one second? Because I wrote a macro which is running 14 For-Next loops To 1000 and it takes quite a while.
Dear TheTornado121, please check out some of my speed enhancing tips videos. They can make extremely long tasks pretty much instant. Here you go: How to Massively Speed Up Your Workbook and Make Macros Run Faster - Excel VBA Is Fun AND How to Speed Up Your Workbook - My SECRET EXCEL WEAPON AND A BONUS Type and Program WAY WAY FASTER Using this Little Known Program - FREE!!
hey Daniel... thankx for ur tutorials.... it helped me alot...
but i a m stuck with writing a criteria code for if command
i have a code that filters #N/A's... i want a if command code for removing the filter if there is no #N/A... can u pls help me with this code
Thank you, sujit! Will do.
Dan
Thank you
Thank you for guideing us
Thank you Sir
You're so welcome!!
Before i saw your video vba looks like a spider web. But after seeing ur video it become a piece of cake...i like ur videos and your approach and i would like to do this in more advance way...kindly help in doing that...thank you..
I'm working on some more advanced lessons. I'm actually coming out with an Advanced Report Generation paid course on Udemy.com. and a book. Glad you like, hunker!
nice😊😊😊
Hi, how can you select the cell by column range and row range. For example my textbox1 value is august 3 and my textbox2 value is product 3 where the columns are august 1, august 2 and august 3 and my rows are product 1, product 2 and product 3. If I run the code the selected cell must be D3 because thats the value of my textbox1 and textbox2.. Hope you understand my sample. Thanks
Thank you.
Hi, I am trying to learn VBA with your videos and the videos are very good for beginners. I am trying to run a vba code that I have created, but every time I run the code the formula in the excel is being lost. When I am re-entering the data and running the program again formula is not being picked up and hence end result is not changing. I tried to lock the formula but its not working. Please help.
Hi Dan,
does learning VBA require some kind of Programming Knowledge?
Best Regards
Abhijit
How do I get the two screens on the same view.
make it smaller :D
Starting a new channel for Piano Lessons, learn by Chord and play by ear! Check it out here: How to Play Piano by Chord - Beginning Chords - C
Hi, I created a VBA that will copy and paste entire rows if the if statements are met and copied to a specific tab. I created the command button to assign the macros. The data gets copied to the new tabs as designed. However, each time I click on the button it duplicates the data in the desire tabs. How can I create or add to my VBA not to duplicate the same data? Thanks
Hi.
Do any of you guys have any idea why my f8 key doesn't work/debug in vba? Instead of debugging, pressing f8 shows me a "pc screen only;duplicate;extend;second screen only" thing on the right hand side. Also, how can I save my code in the module. It only saves the book sheet when I tried to save, but it doesn't save the module.
Thanks a lot!
How to input formula for eg vlookup
You have Date + 7, and the dates returned are 2/24/20143, so it shows 7 days from today. How about if I would like to have the date running at +7 for each row? like 1 Jan 2015, then 8 Jan 2015, 15 Jan 2015, etc? Thank you :)
x as an iterator and increment for each loop, so you can use "Date + 7 * x". Try it!
Hi Dan. I hope you can help me. I have developed an Excel file that keeps cube information. I was asked to make a map to show the location of the cube. I made a user form with a map on it and labeled all of the cubes. I have more than one map that are unique by the first letter (i.e. A1100-1200, B1100-1200, etc). What I am trying to do is have another user form with a textbox when you enter the cube name and press the "map" button it will bring up the correct map. Each userform is a different letter (A, B, C). So if you enter A1100 in the text box, I need userfrom A to show. Can you help me with this?
Good day Mr. Dan!
why do I get an error message of
Run-time error "424":
Object required
for this command line
Dim Lastrow As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Master")
Lastrow = ThisWorkbook.Sheets("Master").Cells(Row.Count, 1).End(xlUp).Row - This is where the error message comes...
Hi, thanks a lot for your great work ! keep it up ... now in day i am watching your video and doing some small macro in office .. :) i need 1 help for below situation ...
we are getting approx 200 line item data .. like as below
Raw data
code cost center amount
AA XYZ 40
BB OPQ 40
and we need to split it code wise with defined % for defined cost center like as below
Report
Code Cost center amt
AA AAA 10
AA BBB 10
AA CCC 10
AA DDD 10
BB MMM 20
BB NNN 20
i.e. for code AA i am inserting 3 blank row below > then deleting existing cost center (XYZ) and inserting new cost center > for first row AAA with 25% for second row BBB with 25% for 3rd row CCC with 25% and for fourth row DDD with 25%
like wise for for code BB with 50%
i have 7 types of code ... so i want to know just concept ..
you reply will be appreciated thanks in advance !!!
sam
Man could you help me out, how can I do what you are doing but for worksheets instead of cells?
Hi Ed,
I'm not sure if I understand what you're asking. Can you explain a bit more please?
Thanks
Dan
Sure, see, my problem is that I receive the information on a table (matrix), and the information for every matrix is for just one day of the year, so in a .xsl file I have an entire year, in other words 365 sheets (one for each day), the objective of my macro is to be able to recollect the data from every single sheet, and copy it onto an unique sheet made just for gathering all the information, the thing is:
1. Since there are 365 sheets, my intention is to make a FOR o a WHILE LOOP to make the macro go through all the pages without making 10 thousand lines of repetitive code;
2. The data that has to be recollected are only 2 values, one of which is only a date that must be set in 11 merged cells. And the second one is a matrix of 6 by 11 that also has to be copied (I tell you the dimensions because my intent is to make some sort of integer variable that every time the loop cycles around gets summed 11 rows down to avoid having overwritten cells);
3. The process I've made is in this way (most likely it is the wrong approach, and it's not the code because I haven't got VBA code learnt, but its my way of solving my iteration ):
go to sheet1
select D6 (which is the date), and copy it
go to recollection sheet, paste the value in A2, and merge A2:A12
go to sheet1
select matrix E5:J16 and copy it
go to recollection sheet, paste the values on B2
This is what needs to be looped, by my lack of knowledge I assume into 3 variables (sheets, date, matrix) the sheet just needs to go to the next one so I thought some sort of code of this sort: sheet1 = x, x++ (for every cycle completed). For the placement of the date inquiry result something like: date1 = y, y +11. To make the next sheet copy the date on the right cell (11 rows below the last one). And for the final variable, I thought the problem could be solved by doing the same as to the date1 but making a variable for the matrix.
I have very little knowledge of coding but I do have some sort of idea of the works of it, not enough to be creative, so I respectfully beg for your help in this.
Thank you for your time!
Hi Ed,
Hmmm. Check out my other vid on looping through each worksheet in a workbook. This should help you out.
th-cam.com/video/-75HKRrIMI8/w-d-xo.html
Thanks
Dan
it most certainly did help! thanks, I managed to write some sloppy code which won't run, if you'd be so kind as to review it, it is very short and very badly done, but please help me out, here it is :
Sub WSLoop()
Dim ws As Worksheet
y = 2
For Each ws In ThisWorkbook.Sheets
'to avoid recolecting data from the recollection sheet
If ws = "Data" Then GoTo label1
'selection of the date on first sheet
ThisWorkbook.Sheets.Cells("B3").Select
'copy selection
Copy.cell
'going to the sheet where all the information will be
ThisWorkbook.Sheets("Data").Select
'selecting the adecuate cell and copying the date there
ThisWorkbook.Sheets("Data").Cells(y, 1) = Paste
'going back to the first worksheet to select the matrix
ThisWorkbook.Sheets.Cells("C4:D6").Select
'copying selection
Copy.cell
'going back to the sheet where all the information will be
ThisWorkbook.Sheets("Data").Select
'selecting the accurate position for the matrix
ThisWorkbook.Sheets("Data").Cells(y, 2) = Paste
'adding the value expected for the next date
y = y + 11
Next ws
label1
End Sub
I think it is only a matter of correct expression to make it run, hope you can help, and many thanks!
hmm. lets try something like this:
Thisworkbook.sheets("Data").cells(y,2) = ws.range("b3") 'ws is the current sheet in the loop, so we need row y on data sheet to take b3 on that ws
y = y +1 'next row!
Thanks
Dan
I really just want to do 1 specific workbook with VBA and don't have the time to learn it. Is there anyway I can just send you my workbook and you can do it for me? What I want to do is not that hard in my mind, I just can't write it out in VBA
Zach Lakin I give up...5 months of compile errors
Not sure but I believe, We have added in "Dates+7" so I guess dates should change accordingly but dates are remain same in video and my excel too :-( but I really enjoy this video as beginner..thanks
Try this instead:
For x = 1 To 10
Cells(x, 1) = Date + x - 1
Next x
*First cell("A1") starts with today's date, and the dates increment by 1 after every row.
You had me at tacos... now you've completely taken me over with pizza you son of an onion! :p
hehe. Really enjoying your videos as I recently started getting into VBA, but started ahead and need these basics to start building upon.
You're very welcome. Please let me know if you have any specific questions, projects you're working on, or video ideas/topics for future vids. Thanks! Dan
Hi ExcelVbalsFun can you help me with this code?!
I want to find "jas" in the range "J7:M40" i managed to do that.
But now i want it to offset to the next selection/range "N7:Q40" and do the same seach. And this for 744 times. With the same offset (0 ,4) of colums every time.
Hope you can help me!
Dim wb As Workbook
Dim ws As Worksheet
Dim FoundCell As Range
Set wb = ActiveWorkbook
Set ws = ActiveSheet
Blad3.Activate
Range("J7:M40").Select
Do
Selection.Offset(0, 4).Select
Const WHAT_TO_FIND As String = "Jas"
Set FoundCell = Selection.Find(what:=WHAT_TO_FIND)
If Not FoundCell Is Nothing Then
Blad5.Activate
Range("D1").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection = FoundCell.Row
Else
Blad5.Activate
Range("D1").Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Selection = 0
End If
Loop
Hi gotta watch at 1.25 speed and the audio is not good, has a bit of crackling but good stuff none the less.
love
Please can anyone help me.
I have in sheet1 B1=PW, B2=MB, B3=VC and in Sheet2 I have D3=PW, D4=MB, D5=VC.
I created the button macro to run when I select B1 in Sheet1 and I press the macro button to go automaticly to sheet2 to D3, the match name for PW...but when I select B2 in sheet1 and I press the macro button it is going to sheet2 and select D3 again instead of D4 which is the match to MB in sheet1..And again if I select B3 in sheet1 and I press the macro button I go to Sheet2 and select D3 again instead of D5. I dont know how to write the VBA to match when I select a cell in Sheet1 and press the macro button to go in Sheet2 and to be selected automaticly the matched cell with the name as same in cell in Sheet1...Thank you all
this video made me hungry
Awesome! That was the plan. :)
Thanks
Dan
For y = 1 To 9 Step 3 'Outer Loop jump by 3 columns
x = 1
'j = 1
Cells(x, y).Select
j = ActiveCell.Column
For x = 2 To 10 'Inner Loop populate cells with condition
Cells(x, j) = Date + 7 ' example of date function to move by 7 days
Cells(x, j + 1) = x * 15
If Cells(x, j + 1) > 100 Then
Cells(x, j + 2) = "Pizza"
Else
Cells(x, j + 2) = "Do-nut"
End If
Next x ' end of inner loop
Next y
Q) is i m not able to get the code to copy headers i.e. date, price & type for2nd iteration
Good thing I had pizza today!
WHY CAN'T YOU INPUT THE DATA BY THE USUAL EXEL WAY
INSTEAD OF BY USING FORMULAS ?
+rcx8666c because using formulas is faster that repetitive data entry? s you can learn how to do it easier? you know, the whole point of these videos?