simply amazing! I work at a company that's has no idea about anything in vba, however were a large company. My projects blow my bosses away when I reduce work processes by huge numbers. I'm seen as some mad genius of computers/excel/etc. These tutorials are so easy to learn! Changed my life more than any other education videos!!!!
I am trully satisfied with WiseOwlTutorials!!! I have seen lots of Loops tutorials and I am sure here you can find find better videos much much better then Indian's, Brazilian (my people) etc... Thank you so much WiseOwlTutorials for your always amazing videos. You have a gift from God for helping people learn something new and usable in life.
***** Please please please , watch my comment on the top of the list,,, I've a problem with some nested loops... if you can't do anything no problem, just tell me what do you think ;) regards from Buenos Aires
Thanks Raphael, glad you found it useful! We have a more up to date version of this information which you can see here www.wiseowl.co.uk/online-training/excel-vba/conditions-loops/ Just in case you find it useful!
I understand in 3 minutes a key and important concept thanks to your simple explanation! Clear as usual and above all explained from simple concepts to get a little to one of the more complex ones. It's really a pleasure but above all an honor for me to be a student of really exceptional teacher like you! At any time of the day, when I have available time, I can not wait to learn and perfect a further argument thanks to your knowledge, which is really great!Thank you for all that you taught me.
This is exactly to the last minute what i was looking. Firstly i looked for do until empty cell. Then i figured i would need to copy that information to other sheets. Bang! You show that as well :) Exactly exactly what i was looking. Thanks. Gonna watch all tutorials and subscribe.
thank you wiseowl, at first I thought all the programming stuff is actually boring, (I took a course on VBA, SAS, R), but after watching your videos my thinking has changed, after your VBA videos I will move to learning R. Thanks.
I would like to put 1000 like for any of your videos but unfortunately I can't. You are really the best teacher I've ever seen, not only because of the great competence in matter but also and above all for the teaching method. Thank you very much!
Hi Vijay, we certainly do! You might find it useful to bookmark these two playlists so that you can easily find all our VBA tutorial videos: ua-cam.com/play/PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5.html This is the original VBA playlist we created which covers the basic topics in parts 1 to 19 and then covers lots of advanced topics as well. ua-cam.com/play/PLNIs-AWhQzckV9rAM3yv8ym4pioIMA0UR.html This covers the basics in more detail and with more up to date videos and there are links to the Wise Owl website where you can download the files and see a written version of each tutorial. Both playlists contain videos on For loops and For Each loops. I hope that you find it useful!
WiseOwlTutorials I've just started learning VBA and really think these set of training videos are the best:) Just some quick questions, when should I use each type of loops, for each, do while and for next and whenever I add a module does it apply to all open sheets and workbook? Hope these questions wouldn't trouble you. Thank you once again:)
Hello, thanks a lot for the tutorial, it is very well structured and explained. Could you please help me with the formats while I create the loop? If in stead of length of the movie, i have a date, and want to sort out the database, comparing given dates with today's date, how can i do that? Thanks in advance :-)
Hi Andrew, wonderful tutorial again. I just wanted to point you out one little thing: in all the videos the volume is not very high. It's alright to listen to them at home but if you're outdoors sometimes it is a bit low. It would be awesome if you'd record them a bit up so that we could have a bit more of scope up the volume. Thanks a lot again
Fantastic videos! I tell everyone about Wise Owl. However, I wish this video talked a bit more about the rules for the IF/Then structure within nested loop statements. I had to create such code and had a hard time making it work due to If/Then must be within a loop. (It is harder than what it sounds like!) I finally got it working but I have no idea why it does work compare to the previous codes. I really need to understand the rules and see some examples. Still, I learn from Wise Owl.
I spent a lot of time on this. Loops were always hard to understand, I had not used them however I will use them more.just one thing. I did have a couple of blank worksheets so I renamed them for the copy and paste routine. My code copied but into cell A3. It took me ages to figure it out. I had the active cell as A3 on one of the sheets at some other point. :) hahaha
HI Andrew, First of all, I want to thank you for the tutorials that you provided on youtube and as well as on your website. Secondly, I would like to ask your help regarding part of my project regarding loops with fixed number of iterations. When I write the script to give value for my For statement e.g. For i = a to b, whenever I create a value for a and b I am receiving an error message. The case is that I want to write a code to run a series of serial numbers using a userform. The idea is to use a batch to generate the series say 123-128 for a quantity of 6. I want to loop it meaning I have to first enter the 123 and then search it using the find last row method and use the value by copying it to a temporary cell within a sheet i have created and from there +1 that will be the value of the next cell down then loop the process 6 times. Apparently when I give value to my variables using the range.value I receive an error. Please find below my code that I modeled from your code that I got from your website. Please kindly comment. And thank you. =) Apologies if seems flooding. StockInventoryTemplate.Activate Range("SIPONumberLine").Value = SIPONumberTextBox.Text Range("SIPurchaseInvoiceDateLine").Value = SIPurchaseInvoiceDateTextBox.Text Range("SIPurchaseInvoiceNumberLine").Value = SIPurchaseInvoiceNumberTextBox.Text Range("SIProductNameLine").Value = SIProductNameTextBox.Text Range("SIQuantityLine").Value = SIQuantityTextBox.Text Range("SISerialNumber1Line").Value = SISerialNumber1TextBox.Text Range("SISerialNumber2Line").Value = SISerialNumber2TextBox.Text Range("SILoggedByLine").Value = SILoggedByTextBox.Text Application.DisplayAlerts = False InventoryLog.Select Dim er As Long er = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row Cells(er, 1) = StockInventoryTemplate.Range("SIPONumberLine").Value Cells(er, 2) = StockInventoryTemplate.Range("SIPurchaseInvoiceDateLine").Value Cells(er, 3) = StockInventoryTemplate.Range("SIPurchaseInvoiceNumberLine").Value Cells(er, 4) = StockInventoryTemplate.Range("SIProductNameLine").Value Cells(er, 5) = StockInventoryTemplate.Range("SITemplateBatchNumbers").Value Cells(er, 6) = StockInventoryTemplate.Range("SIQuantityLine").Value Cells(er, 7) = StockInventoryTemplate.Range("SISerialNumber1Line").Value Cells(er, 8) = "In" Cells(er, 9) = "N/A" Cells(er, 10) = StockInventoryTemplate.Range("SILoggedByLine").Value 'the number of times for VBA to loop Dim LoopNumber As Integer 'number of each turn Dim ThisGo As Integer 'the number of the loop Dim y As String Dim z As String 'the formula where x = cells(rows.count,7).end(xldown).value + 1 ' batch is written where SISerialNumber1Line is y and SISerialNumber2Line is z 'x = Cells(Rows.Count, 7).End(xlDown).Value + 1 y = StockInventoryTemplate.Range("SIQuantity").Value - 1 z = StockInventoryTemplate.Range("SISerialNumber2Line").Value ' but apparently I receive error on when ever I create a value for x,y and z for being not a valid qualifier. Cells(er, 1) = StockInventoryTemplate.Range("SIPONumberLine").Value Cells(er, 2) = StockInventoryTemplate.Range("SIPurchaseInvoiceDateLine").Value Cells(er, 3) = StockInventoryTemplate.Range("SIPurchaseInvoiceNumberLine").Value Cells(er, 4) = StockInventoryTemplate.Range("SIProductNameLine").Value Cells(er, 5) = StockInventoryTemplate.Range("SITemplateBatchNumbers").Value Cells(er, 6) = StockInventoryTemplate.Range("SIQuantityLine").Value Cells(er, 7) = LabelsandTitles.Range("SISerialNumber1Line").Value Cells(er, 8) = "In" Cells(er, 9) = "N/A" Cells(er, 10) = StockInventoryTemplate.Range("SILoggedByLine").Value For ThisGo = 1 To y Cells(er, 1) = StockInventoryTemplate.Range("SIPONumberLine").Value Cells(er, 2) = StockInventoryTemplate.Range("SIPurchaseInvoiceDateLine").Value Cells(er, 3) = StockInventoryTemplate.Range("SIPurchaseInvoiceNumberLine").Value Cells(er, 4) = StockInventoryTemplate.Range("SIProductNameLine").Value Cells(er, 5) = StockInventoryTemplate.Range("SITemplateBatchNumbers").Value Cells(er, 6) = StockInventoryTemplate.Range("SIQuantityLine").Value Cells(Rows.Count, 7).End(xlUp).Select LabelsandTitles.Range("LastSerialNumber").Value = ActiveCell.Value Cells(er, 7) = LabelsandTitles.Range("LastSerialNumber").Value + 1 Cells(er, 8) = "In" Cells(er, 9) = "N/A" Cells(er, 10) = StockInventoryTemplate.Range("SILoggedByLine").Value If LabelsandTitles.Range("LastSerialNumber").Value = StockInventoryTemplate.Range("SISerialNumber2Line").Value Then Exit For Else: Next ThisGo End If Unload Me Application.DisplayAlerts = False End Sub
Question for the almighty wise owl, k i got a input box, you enter a number in the box and the number shows up in a cell, thats great. Where Im having my problem is If the item number is zero, i have a msgbox that pops up and says "try again". well what bit of code do I use to get back to the input box? a loop? or what do I use? your input is greatly appreciated. your vids are amazing. I knew nothing 2 days ago, know I can at least write a simple piece of code to do what I want. thank you.
The lesson was really helpful. I tried putting Activecell.offset(0,4).Value = FilmRating underneath FilmLength = Activecell.offset(0,1).Value but it doesn’t write the text in the cell. Unless I bring it down to the way you did it
Great Video. I am trying to Copy data using the formula you mention in the video, but paste into another cell on the same sheet. The trouble is, when it reaches the first row which matches the criteria to copy and paste the cell, it pastes it to the desired position, but then the "ActiveCell" is incorrect, so exits the loop.....any ideas on how this can be rectified?
Hello, your videos are really wonderful! I have a question to ask:how to calculate age of someone if given their date of birth using VBA? and the next birthday of someone given their date of birth and age( when it is successfully calculated)? Please and thanks a lot for your help.
Awesome, love WiseOwl Tutorials! I learnt A LOT! May I have a question, I created a workbook after I watched your tutorials. I recently created a workbook which used the Loops coding to automatically calculate the dates. However I need the template to do one more thing, and I am not sure whether I need use loop in this case, and the question is as below1) if column E from previous worksheet "completed", then the data from column (A:D) from previous worksheet should carry forward to the next worksheet, when I click the add a new week button at the top of the worksheet.
Fantastic job. I've watched all the videos so far and I'll watch some more. However, I do need to know how to have the column widths automatically readjust for work. Could you just give me the code? I can figure it out from there probably
Again, your tutorials are excellent. The only question is that my Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets(FilmRating).Activate ActiveCell.PasteSpecial ActiveCell.Offset(1, 0).Select When I do the ActiveCell.PasteSpecial, I get an offset for the data copied instead of starting at cell A1. However I do have the correct data in terms of long , short and medium movies ! What can it be ? Thak you
ub SimpleDoLoop() Dim FilmLength As Integer Dim FilmRating As String Application.ScreenUpdating = False Worksheets("sheet1").Activate Range("a3").Select Do Until ActiveCell.Value = "" FilmLength = ActiveCell.Offset(0, 3).Value If FilmLength < 100 Then FilmRating = "short" ElseIf FilmLength < 150 Then FilmRating = "medium" Else FilmRating = "long"
When I run my code, the data starts at "A1" for the long sheet. E18 for the short and F21 for the medium one. However I do have the correct information. This is strange.
Hey Andrew! Thank you for the useful video. One question though- is there a way for the vba to run the loop on it own without having to press f5 again and again?
Hi Bhuvaneshwari, one way to trigger your code automatically is to use worksheet or workbook events. We have a video on this topic which you see here ua-cam.com/video/0EXdPcbsTZI/v-deo.html I hope that helps!
Hi, Here we are assuming that the ActiveCell in Worksheet(FilmRating) by default by cell A1. what if A1 is not the active cell? How would we select the active cell? I tried with the following, but it gave me a single row for each Film Rating Tab. Thanks!!! Sheets(FilmRating).Activate Range("A1").Select ActiveCell.PasteSpecial ActiveCell.Offset(1,0).Select
I think I got the answer. Here is the code Worksheets(rating).Activate If Range("A1").Value = "" Then Range("A1").PasteSpecial Else Range("A1").End(xlDown).Select Selection.End(xlDown).Select Selection.End(xlUp).Offset(1, 0).Select Selection.PasteSpecial End If Worksheets("sheet3").Activate But I am sure there must be a better way or simpler way to do this. I f you know then please share.
Hello, I'm trying to copy and paste the info to a specific cell range, say for example range("a3"), on the new worksheets rather than any active cell, and continue downward from there. Problem is, when the code loops around again, it just overwrites and pastes the new info into range("a3") rather than continuing to paste the new info downward into a list. If possible, can somebody demonstrate the proper code? Thanks! And thanks for these videos.
When you are in new worksheet, after the copying is done, do offset (1,0) to go to the next row before going back to the original sheet. Now when you will come back to your new sheet from original sheet, the copied value will be pasted in the new row!!
Hello Philippe, I am actually curious how you made sure that the sub will pick the A1 cell on the first run through for each "Short" "Medium" and "Long" sheet, and the ones below in the following loops. I thought about a counter to refer the correct selected cell, but that means, since there are 3 different sheets to populate, 3 different counters... it sounds a bit messy. Or maybe you simply initialized A1 as active cell outside the do loop cycle, counting on the fact that each sheet remembers its last selected cell when you return to it (like Andrew suggested)? Did you spot an easier solution? Thanks! And thanks to Andrew Gould too, of course!
Hi Andrew, I am getting Subscript out of range error when its compiling the line- worksheets(District).Activate. Can you please let me know the mistake I am doing. Option Explicit Sub Abhi() Dim District As String Application.ScreenUpdating = False Worksheets("Sheet1").Activate Range("A2").Select Do While ActiveCell.Value "" District = ActiveCell.Offset(2, 0) Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets(District).Activate ActiveCell.PasteSpecial ActiveCell.Offset(1, 0).Select Worksheets("Sheet1").Activate ActiveCell.Offset(1, 0).Select Loop Application.CutCopyMode = False Application.ScreenUpdating = True End Sub Thanks for your videos!!!
Could anyone help mi with my code? I've got problem with this part of code: FilmLength = ActiveCell.Offset(0, 3).Value If FilmLength < 100 Then FilmRating = "Short" ElseIf FilmLength < 150 Then FilmRating = "Medium" Else: FilmRating = "Long" End If When I press run key, I got an error in the third line. The message is Compile error: Else without if; what's wrong wit my code???
Andrew, Is ActiveCell in Worksheet(FilmRating) default by cell A1? If I want ActiveCell in Worksheet(FilmRating) starts with cell A2, what should I write? I tried with the following, but it gave me a single row for each Film Rating Tab. Thanks!!! Sheets(FilmRating).Activate Range("A2").Select ActiveCell.PasteSpecial ActiveCell.Offset(1,0).Select
Hi JSavic, Thank you for reply, really appriciate the effort you guys are putting. I'm stuck in this, propbably a mental block. I wrote this code to force excel to paste the first value starting from a1 Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets(rating).Activate 'ActiveCell.PasteSpecial
If Range("a1").Value = "" Then Range("a1").PasteSpecial Range("a1").Offset(1, 0).Select Else Range("a1").End(xlDown).Offset(1, 0).PasteSpecial End If Worksheets("sheet3").Activate ActiveCell.Offset(1, 0).Select Please suggest if there is a better way to do this.
I think I got the answer. Here is the code Worksheets(rating).Activate If Range("A1").Value = "" Then Range("A1").PasteSpecial Else Range("A1").End(xlDown).Select Selection.End(xlDown).Select Selection.End(xlUp).Offset(1, 0).Select Selection.PasteSpecial End If Worksheets("sheet3").Activate But I am sure there must be a better way or simpler way to do this. I f you know then please share.
Hi Andrew! When I run the code it takes my laptop about 17 seconds to execute it for a database of 13 lines and 5 columns. Even with the screen-updating turned off. It's quicker if I just hold down my F8 key. Any idea what might be causing this? Thanks for the tutorials, they are the best I have found!
Hi, you could do something like this (you'll have to run the first sub to kick off the process, or attach it to the Open event of the workbook so that it fires automatically when you open the file): Sub Start_Running_Timed_Routine() Application.OnTime _ EarliestTime:="14:00:00", _ Procedure:="Timed_Routine" End Sub Sub Timed_Routine() 'do something useful If Hour(Now) < 15 Then Application.OnTime _ EarliestTime:=Now + TimeValue("00:10:00"), _ Procedure:="Timed_Routine" End If End Sub
Hi Team WiseOwl can you help me about looping i want whenever you enter a value in the ID text box, Excel VBA loads the corresponding record. When you click the Edit / Add button, Excel VBA edits the record on the sheet or adds the record when the ID does not yet exist. need your help TIA
i already watch the video but i think i need the after update in textbox ID , example i type a 123456 in textbox ID if ID 123456 is in the data the corresponding data will show in other text box and if 123456 is nothing in the data the form will clear and nothing show error anyway i like the disable method
Hi WiseOwl 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
Dear Andrew, after this video I am convinced that I can do very useful things with the vba and I am just at number fifteen! But I would like to ask you how can I solve this: instead of the FilmLenght, I have a variable in column D which is the PraticNumber (as integer) composed of 13 digits (for example 2193458345937). Based on the last number (in this case 7) I must get the name of the assigned operator to write in column E. In a nutshell, instead of FilmRating I have the variabile called OperatorName (as string). If the last number of PraticNumber is between zero and 4, OperatorName must be Mauro, if instead it is between 5 and 9 OperatorName must be Paul. How do I have to set the statement if to get this result? Thank you very much in advance!
Dear Andrew, the code that you suggested works perfectly (thanks!), but only if PraticNumber is a single number (example 1,2,3,4, etc.). The numbers of my practices, instead, are of 13 numbers, so I have to extract the last digit of that number and based on it assign the practice to Mauro or Paul. So I'll watch your video n. 53.2 because I need to find the VBA code that matches the extracted excel string function. How can I copy the columns headings of the original sheet (in your video called Top Movies 2012) on the top of the three sheets just created? In my case I have to copy the first row only. Thank you very much!
Thanks for the awesome videos. I noticed in a code that I wrote once: "Loop Until Round(X2, 2) = Round(L, 2)" this works when X2 reaches the value of L (X2 is increased incrementally by 0.1L). If however I used "Loop Until X2 = L," it doesn't finish the loop when L is = X2. I am tracking the Locals window and in both cases, X2 reaches L, the first case the loop stops while the second case the loop keeps going until X2 is too big and then out of range error. the full code (this is a mix of self-learning from the videos + the WO Blog): Sub DrawingBendingMoment() Dim C1 As Range, C2 As Range, C3 As Range, C4 As Range, C5 As Range Dim ws As Worksheet Dim sh As Shape Dim L, W, X, X2, R, fac1, fac2, fac3, M1, M2, C L = 52 W = 6.32 X = L / 10 fac2 = 0.1 R = W * L / 2 X2 = 0 C = 3 Set C1 = Cells(6, C) Set C2 = Cells(6, 7) Set C3 = Cells(5, 3) Set C4 = Cells(5, 7) Set C5 = Cells(5, 4) Set ws = ActiveSheet For Each sh In ws.Shapes If Not (sh.Type = msoOLEControlObject Or sh.Type = msoFormControl) Then sh.Delete Next sh ws.Shapes.AddConnector(msoConnectorStraight, C1.Left, C1.Top + C1.Height / 2, C2.Left + C2.Width, C2.Top + C2.Height / 2).Select Do For fac1 = 0 To 1 M1 = Round(R * X2 - W * X2 ^ 2 / 2, 2) M2 = Round(R * (X2 + X) - W * (X2 + X) ^ 2 / 2, 2) ws.Shapes.AddConnector(msoConnectorStraight, C1.Left + fac1 * C1.Width / 2, C1.Top + C1.Height / 2, C1.Left + fac1 * C1.Width / 2, C1.Top + C1.Height / 2 + M1 * fac2).Select If fac1 = 0 Then fac3 = 1 ElseIf fac1 = 1 Then fac3 = 2 End If ws.Shapes.AddConnector(msoConnectorStraight, C1.Left + fac1 * C1.Width / 2, C1.Top + C1.Height / 2 + M1 * fac2, C1.Left + fac3 * C1.Width / 2, C1.Top + C1.Height / 2 + M2 * fac2).Select X2 = X2 + X Next fac1 C = C + 1 Set C1 = Cells(6, C) 'Loop Until X2 = L Loop Until Round(X2, 2) = Round(L, 2) End Sub
Hi , i have watched several videos that helped quite much get into vba, but now i am reallly stuck, can someone help me please! I need to sum a product n-times: It should be like this: The product is this: 10*Range("H8").value*Cos(Angle)..... The next product to sum is:10*Range("H8").value*Cos(Angle-range("B17").value/10)... and the next 10*Range("H8").value*Cos(Angle-2*(range("B17").value/10) and so on n-times, until the angle becomes ZERO. So, the product will be sumed n-times according to the times it needs for the angle to be zero.
Hey Andrew! I'm from BA Argentina, and I love your videos, when I found your videos I watched everyday everytime I could, you have a really understandable method, even some guys that went outside my work looking for training now they are asking me to help them for some VBA codes, please continue developing this videos with detail as you did it so far.... I have asked in a lot of forums but I couldn't find a simple answer to fix my code or make it easier to run... could you help me with this? this is the link with my code and the explanation ... Shortly ... I need to automate bank reconciliations, matching transactions by numbers when they sum zero, I think a loop and some nested ifs would work but I'm missing something... any suggestion?? thanks in advance .. you are my Obi Wan Kenobi in VBA :) stackoverflow.com/questions/28868259/vba-create-a-macro-to-match-items-in-a-bank-reconciliation-payment-booking-ba
***** no problem , I could fix it thanks to a video upload from an Indian guy, the compensation was done with two "do loops" and some counters to match amounts, and then the code writes references to each reconciliated items, I was close but not enough, your videos are awesome, thanks to you I'm the teacher on my company lol , hope you continue with the videos!! thanks for your answer!!
The series just cannot be disliked. Superb content 😊
Thank you for watching!
simply amazing! I work at a company that's has no idea about anything in vba, however were a large company. My projects blow my bosses away when I reduce work processes by huge numbers. I'm seen as some mad genius of computers/excel/etc. These tutorials are so easy to learn! Changed my life more than any other education videos!!!!
Whatever Mr. Andrew teaches us I will still believe that he is the best! From the Philippines. Thank you sir.
I am trully satisfied with WiseOwlTutorials!!! I have seen lots of Loops tutorials and I am sure here you can find find better videos much much better then Indian's, Brazilian (my people) etc... Thank you so much WiseOwlTutorials for your always amazing videos. You have a gift from God for helping people learn something new and usable in life.
***** Please please please , watch my comment on the top of the list,,, I've a problem with some nested loops... if you can't do anything no problem, just tell me what do you think ;) regards from Buenos Aires
By far one of my favorite videos! Excellent job with this! This is by far the best instructional content I have used for learning VBA.
think this the best loop tutorial i found. simple. all the other tutorials i found had too many codes not needed shown
Thanks Raphael, glad you found it useful! We have a more up to date version of this information which you can see here www.wiseowl.co.uk/online-training/excel-vba/conditions-loops/
Just in case you find it useful!
I understand in 3 minutes a key and important concept thanks to your simple explanation! Clear as usual and above all explained from simple concepts to get a little to one of the more complex ones. It's really a pleasure but above all an honor for me to be a student of really exceptional teacher like you! At any time of the day, when I have available time, I can not wait to learn and perfect a further argument thanks to your knowledge, which is really great!Thank you for all that you taught me.
Dear Andrew, you are a genius! It is the simple truth! I don't have to say nothing else.
Nothing Better than this video....on Loops...
Brilliant Way to Explain the Loops...
Superb..
Thanks for helping me with work 👍
This is exactly to the last minute what i was looking. Firstly i looked for do until empty cell. Then i figured i would need to copy that information to other sheets. Bang! You show that as well :) Exactly exactly what i was looking. Thanks. Gonna watch all tutorials and subscribe.
thank you wiseowl, at first I thought all the programming stuff is actually boring, (I took a course on VBA, SAS, R), but after watching your videos my thinking has changed, after your VBA videos I will move to learning R. Thanks.
I am constantly re-watching these videos. awesome
Great Tutorial Andrew. Ur way of explaining things is superb.
Thank you Nader, I appreciate the comments!
You guys are a life saver! Thanks so much for these video tutorials. Really taking my excel know-how to the next level :)
I would like to put 1000 like for any of your videos but unfortunately I can't. You are really the best teacher I've ever seen, not only because of the great competence in matter but also and above all for the teaching method. Thank you very much!
You did an excellent work. No video ever created like your videos. Thanks a lot for whole things.
wow super excited you are really amazing sir 👍🏻👍🏻👍🏻👍🏻👍🏻💯💯💯
Thank you Vijay, I'm glad you enjoyed the video!
cn we see same examples with Forloop if you have ds
Hi Vijay, we certainly do! You might find it useful to bookmark these two playlists so that you can easily find all our VBA tutorial videos:
ua-cam.com/play/PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5.html This is the original VBA playlist we created which covers the basic topics in parts 1 to 19 and then covers lots of advanced topics as well.
ua-cam.com/play/PLNIs-AWhQzckV9rAM3yv8ym4pioIMA0UR.html This covers the basics in more detail and with more up to date videos and there are links to the Wise Owl website where you can download the files and see a written version of each tutorial.
Both playlists contain videos on For loops and For Each loops.
I hope that you find it useful!
You are the best!, your way of explaining VBA concepts is amazing. Keep doing the good work! all the best.
One of the clearest VBA tutorials thank you😊
WiseOwlTutorials I've just started learning VBA and really think these set of training videos are the best:)
Just some quick questions, when should I use each type of loops, for each, do while and for next and whenever I add a module does it apply to all open sheets and workbook? Hope these questions wouldn't trouble you.
Thank you once again:)
WiseOwlTutorials thank you😊
thank you you are the best teacher in the whole world i watch all video
Many thanks for videos. I'm watching in Brazil.
Thanks
Thank you so much for your support!
Simply great content presented superbly! Well done!
Thank you!
Thanks!
Terima kasih.
Thank you so much for your support!
thank you very much for your video. very practical & useful for beginners
Thank you :)
Many thanks Melki!
@@WiseOwlTutorials sorry just reply, somehow replies to my comments don't appear in my feed
Hello, thanks a lot for the tutorial, it is very well structured and explained.
Could you please help me with the formats while I create the loop? If in stead of length of the movie, i have a date, and want to sort out the database, comparing given dates with today's date, how can i do that?
Thanks in advance :-)
Hi Andrew, wonderful tutorial again. I just wanted to point you out one little thing: in all the videos the volume is not very high. It's alright to listen to them at home but if you're outdoors sometimes it is a bit low. It would be awesome if you'd record them a bit up so that we could have a bit more of scope up the volume. Thanks a lot again
you deserve a big like .. thank u very much
Fantastic videos! I tell everyone about Wise Owl.
However, I wish this video talked a bit more about the rules for the IF/Then structure within nested loop statements. I had to create such code and had a hard time making it work due to If/Then must be within a loop. (It is harder than what it sounds like!) I finally got it working but I have no idea why it does work compare to the previous codes. I really need to understand the rules and see some examples.
Still, I learn from Wise Owl.
Worth spending that 20 odd minutes. Thanks Owl,
I spent a lot of time on this. Loops were always hard to understand, I had not used them however I will use them more.just one thing. I did have a couple of blank worksheets so I renamed them for the copy and paste routine. My code copied but into cell A3. It took me ages to figure it out. I had the active cell as A3 on one of the sheets at some other point. :) hahaha
I was looking for intersect and union method can I get the video link
Beat video on loops! Thank you.
Very helpful and easy to understand
Plz what to do if the while loop still incrementing
HI Andrew,
First of all, I want to thank you for the tutorials that you provided on youtube and as well as on your website.
Secondly, I would like to ask your help regarding part of my project regarding loops with fixed number of iterations. When I write the script to give value for my For statement e.g. For i = a to b, whenever I create a value for a and b I am receiving an error message. The case is that I want to write a code to run a series of serial numbers using a userform. The idea is to use a batch to generate the series say 123-128 for a quantity of 6. I want to loop it meaning I have to first enter the 123 and then search it using the find last row method and use the value by copying it to a temporary cell within a sheet i have created and from there +1 that will be the value of the next cell down then loop the process 6 times. Apparently when I give value to my variables using the range.value I receive an error. Please find below my code that I modeled from your code that I got from your website. Please kindly comment. And thank you. =)
Apologies if seems flooding.
StockInventoryTemplate.Activate
Range("SIPONumberLine").Value = SIPONumberTextBox.Text
Range("SIPurchaseInvoiceDateLine").Value = SIPurchaseInvoiceDateTextBox.Text
Range("SIPurchaseInvoiceNumberLine").Value = SIPurchaseInvoiceNumberTextBox.Text
Range("SIProductNameLine").Value = SIProductNameTextBox.Text
Range("SIQuantityLine").Value = SIQuantityTextBox.Text
Range("SISerialNumber1Line").Value = SISerialNumber1TextBox.Text
Range("SISerialNumber2Line").Value = SISerialNumber2TextBox.Text
Range("SILoggedByLine").Value = SILoggedByTextBox.Text
Application.DisplayAlerts = False
InventoryLog.Select
Dim er As Long
er = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(er, 1) = StockInventoryTemplate.Range("SIPONumberLine").Value
Cells(er, 2) = StockInventoryTemplate.Range("SIPurchaseInvoiceDateLine").Value
Cells(er, 3) = StockInventoryTemplate.Range("SIPurchaseInvoiceNumberLine").Value
Cells(er, 4) = StockInventoryTemplate.Range("SIProductNameLine").Value
Cells(er, 5) = StockInventoryTemplate.Range("SITemplateBatchNumbers").Value
Cells(er, 6) = StockInventoryTemplate.Range("SIQuantityLine").Value
Cells(er, 7) = StockInventoryTemplate.Range("SISerialNumber1Line").Value
Cells(er, 8) = "In"
Cells(er, 9) = "N/A"
Cells(er, 10) = StockInventoryTemplate.Range("SILoggedByLine").Value
'the number of times for VBA to loop
Dim LoopNumber As Integer
'number of each turn
Dim ThisGo As Integer
'the number of the loop
Dim y As String
Dim z As String
'the formula where x = cells(rows.count,7).end(xldown).value + 1
' batch is written where SISerialNumber1Line is y and SISerialNumber2Line is z
'x = Cells(Rows.Count, 7).End(xlDown).Value + 1
y = StockInventoryTemplate.Range("SIQuantity").Value - 1
z = StockInventoryTemplate.Range("SISerialNumber2Line").Value
' but apparently I receive error on when ever I create a value for x,y and z for being not a valid qualifier.
Cells(er, 1) = StockInventoryTemplate.Range("SIPONumberLine").Value
Cells(er, 2) = StockInventoryTemplate.Range("SIPurchaseInvoiceDateLine").Value
Cells(er, 3) = StockInventoryTemplate.Range("SIPurchaseInvoiceNumberLine").Value
Cells(er, 4) = StockInventoryTemplate.Range("SIProductNameLine").Value
Cells(er, 5) = StockInventoryTemplate.Range("SITemplateBatchNumbers").Value
Cells(er, 6) = StockInventoryTemplate.Range("SIQuantityLine").Value
Cells(er, 7) = LabelsandTitles.Range("SISerialNumber1Line").Value
Cells(er, 8) = "In"
Cells(er, 9) = "N/A"
Cells(er, 10) = StockInventoryTemplate.Range("SILoggedByLine").Value
For ThisGo = 1 To y
Cells(er, 1) = StockInventoryTemplate.Range("SIPONumberLine").Value
Cells(er, 2) = StockInventoryTemplate.Range("SIPurchaseInvoiceDateLine").Value
Cells(er, 3) = StockInventoryTemplate.Range("SIPurchaseInvoiceNumberLine").Value
Cells(er, 4) = StockInventoryTemplate.Range("SIProductNameLine").Value
Cells(er, 5) = StockInventoryTemplate.Range("SITemplateBatchNumbers").Value
Cells(er, 6) = StockInventoryTemplate.Range("SIQuantityLine").Value
Cells(Rows.Count, 7).End(xlUp).Select
LabelsandTitles.Range("LastSerialNumber").Value = ActiveCell.Value
Cells(er, 7) = LabelsandTitles.Range("LastSerialNumber").Value + 1
Cells(er, 8) = "In"
Cells(er, 9) = "N/A"
Cells(er, 10) = StockInventoryTemplate.Range("SILoggedByLine").Value
If LabelsandTitles.Range("LastSerialNumber").Value = StockInventoryTemplate.Range("SISerialNumber2Line").Value Then
Exit For
Else: Next ThisGo
End If
Unload Me
Application.DisplayAlerts = False
End Sub
Question for the almighty wise owl, k i got a input box, you enter a number in the box and the number shows up in a cell, thats great. Where Im having my problem is If the item number is zero, i have a msgbox that pops up and says "try again". well what bit of code do I use to get back to the input box? a loop? or what do I use? your input is greatly appreciated. your vids are amazing. I knew nothing 2 days ago, know I can at least write a simple piece of code to do what I want. thank you.
The lesson was really helpful. I tried putting Activecell.offset(0,4).Value = FilmRating underneath FilmLength = Activecell.offset(0,1).Value but it doesn’t write the text in the cell. Unless I bring it down to the way you did it
Hello sir i just want to search a data in a database and edit the search's data values in the coloum.
Great Video. I am trying to Copy data using the formula you mention in the video, but paste into another cell on the same sheet. The trouble is, when it reaches the first row which matches the criteria to copy and paste the cell, it pastes it to the desired position, but then the "ActiveCell" is incorrect, so exits the loop.....any ideas on how this can be rectified?
Private Sub CommandButton1_Click()
Dim EnoughOrdered As String
Application.ScreenUpdating = False
Worksheets("DASHBOARD").Activate
Range("B48").Select
Do Until ActiveCell.Value = ""
EnoughOrdered = ActiveCell.Offset(0, 4).Value
If EnoughOrdered = "Order Required" Then
Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
Range("B33").Activate
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.Offset(1, 0).Select
End If
Worksheets("DASHBOARD").Activate
ActiveCell.Offset(1, 0).Select
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Your teaching style is simply amazing.
Do you have trainings in Java, Andriod programming etc?
You are simply great sir...
Hello, your videos are really wonderful!
I have a question to ask:how to calculate age of someone if given their date of birth using VBA? and the next birthday of someone given their date of birth and age( when it is successfully calculated)? Please and thanks a lot for your help.
Awesome, love WiseOwl Tutorials! I learnt A LOT! May I have a question, I created a workbook after I watched your tutorials. I recently created a workbook which used the Loops coding to automatically calculate the dates. However I need the template to do one more thing, and I am not sure whether I need use loop in this case, and the question is as below1) if column E from previous worksheet "completed", then the data from column (A:D) from previous worksheet should carry forward to the next worksheet, when I click the add a new week button at the top of the worksheet.
Fantastic job. I've watched all the videos so far and I'll watch some more. However, I do need to know how to have the column widths automatically readjust for work. Could you just give me the code? I can figure it out from there probably
That greatly helps. That's all I needed was the AutoFit method, how simple. You're the best!
Thanks Wise Owl.
Again, your tutorials are excellent. The only question is that my Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
Worksheets(FilmRating).Activate
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Select
When I do the ActiveCell.PasteSpecial, I get an offset for the data copied instead of starting at cell A1. However I do have the correct data in terms of long , short and medium movies ! What can it be ? Thak you
ub SimpleDoLoop()
Dim FilmLength As Integer
Dim FilmRating As String
Application.ScreenUpdating = False
Worksheets("sheet1").Activate
Range("a3").Select
Do Until ActiveCell.Value = ""
FilmLength = ActiveCell.Offset(0, 3).Value
If FilmLength < 100 Then
FilmRating = "short"
ElseIf FilmLength < 150 Then
FilmRating = "medium"
Else
FilmRating = "long"
End If
Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
Worksheets(FilmRating).Activate
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Select
Worksheets("sheet1").Activate
ActiveCell.Offset(1, 0).Select
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
When I run my code, the data starts at "A1" for the long sheet. E18 for the short and F21 for the medium one. However I do have the correct information. This is strange.
Hey Andrew! Thank you for the useful video. One question though- is there a way for the vba to run the loop on it own without having to press f5 again and again?
Hi Bhuvaneshwari, one way to trigger your code automatically is to use worksheet or workbook events. We have a video on this topic which you see here ua-cam.com/video/0EXdPcbsTZI/v-deo.html
I hope that helps!
That was wonderful, i'm speechless
Hi,
Here we are assuming that the ActiveCell in Worksheet(FilmRating) by default by cell A1. what if A1 is not the active cell? How would we select the active cell? I tried with the following, but it gave me a single row for each Film Rating Tab. Thanks!!!
Sheets(FilmRating).Activate
Range("A1").Select
ActiveCell.PasteSpecial
ActiveCell.Offset(1,0).Select
I think I got the answer.
Here is the code
Worksheets(rating).Activate
If Range("A1").Value = "" Then
Range("A1").PasteSpecial
Else
Range("A1").End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial
End If
Worksheets("sheet3").Activate
But I am sure there must be a better way or simpler way to do this.
I f you know then please share.
When i try to do the exact same thing, my excel crashes. Why is that?
Hi Andy,
It would be great if you could use another example instead this Film details example....
i think u haven't used 'Do while. Is Do While and Do untill the same?
great video, better than the videos where you need to pay on some websites..
Very nice and helpful. Thank you.
Hello,
I'm trying to copy and paste the info to a specific cell range, say for example range("a3"), on the new worksheets rather than any active cell, and continue downward from there. Problem is, when the code loops around again, it just overwrites and pastes the new info into range("a3") rather than continuing to paste the new info downward into a list. If possible, can somebody demonstrate the proper code?
Thanks! And thanks for these videos.
When you are in new worksheet, after the copying is done, do offset (1,0) to go to the next row before going back to the original sheet. Now when you will come back to your new sheet from original sheet, the copied value will be pasted in the new row!!
is there anyway to end the loop if the next 10 cells are blank ( "" )?
you are boss, thank you!
i got a promotion thanks to your videos!
Hi, I was able to have my program to select A1 on each sheet before running your code you should see the correct result. It's working well
Hello Philippe, I am actually curious how you made sure that the sub will pick the A1 cell on the first run through for each "Short" "Medium" and "Long" sheet, and the ones below in the following loops. I thought about a counter to refer the correct selected cell, but that means, since there are 3 different sheets to populate, 3 different counters... it sounds a bit messy.
Or maybe you simply initialized A1 as active cell outside the do loop cycle, counting on the fact that each sheet remembers its last selected cell when you return to it (like Andrew suggested)?
Did you spot an easier solution?
Thanks! And thanks to Andrew Gould too, of course!
Great tutorial. Thank you!
Hi Andrew,
I am getting Subscript out of range error when its compiling the line- worksheets(District).Activate. Can you please let me know the mistake I am doing.
Option Explicit
Sub Abhi()
Dim District As String
Application.ScreenUpdating = False
Worksheets("Sheet1").Activate
Range("A2").Select
Do While ActiveCell.Value ""
District = ActiveCell.Offset(2, 0)
Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
Worksheets(District).Activate
ActiveCell.PasteSpecial
ActiveCell.Offset(1, 0).Select
Worksheets("Sheet1").Activate
ActiveCell.Offset(1, 0).Select
Loop
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Thanks for your videos!!!
Could anyone help mi with my code? I've got problem with this part of code:
FilmLength = ActiveCell.Offset(0, 3).Value
If FilmLength < 100 Then FilmRating = "Short"
ElseIf FilmLength < 150 Then FilmRating = "Medium"
Else: FilmRating = "Long"
End If
When I press run key, I got an error in the third line. The message is Compile error: Else without if; what's wrong wit my code???
Andrew,
Is ActiveCell in Worksheet(FilmRating) default by cell A1? If I want ActiveCell in Worksheet(FilmRating) starts with cell A2, what should I write? I tried with the following, but it gave me a single row for each Film Rating Tab. Thanks!!!
Sheets(FilmRating).Activate
Range("A2").Select
ActiveCell.PasteSpecial
ActiveCell.Offset(1,0).Select
Hi I have the same question. did you get a reply?
Hi JSavic, Thank you for reply, really appriciate the effort you guys are putting.
I'm stuck in this, propbably a mental block.
I wrote this code to force excel to paste the first value starting from a1
Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
Worksheets(rating).Activate
'ActiveCell.PasteSpecial
If Range("a1").Value = "" Then
Range("a1").PasteSpecial
Range("a1").Offset(1, 0).Select
Else
Range("a1").End(xlDown).Offset(1, 0).PasteSpecial
End If
Worksheets("sheet3").Activate
ActiveCell.Offset(1, 0).Select
Please suggest if there is a better way to do this.
I think I got the answer.
Here is the code
Worksheets(rating).Activate
If Range("A1").Value = "" Then
Range("A1").PasteSpecial
Else
Range("A1").End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial
End If
Worksheets("sheet3").Activate
But I am sure there must be a better way or simpler way to do this.
I f you know then please share.
Hi Andrew! When I run the code it takes my laptop about 17 seconds to execute it for a database of 13 lines and 5 columns. Even with the screen-updating turned off. It's quicker if I just hold down my F8 key. Any idea what might be causing this? Thanks for the tutorials, they are the best I have found!
Yes I have several plugins that we use for work. They have caused me some problems before, so I guess that might be the reason. Thanks for the reply
How we can use time based trigger like if it should start 2pm to 3pm with 10 mint interval ?
Hi, you could do something like this (you'll have to run the first sub to kick off the process, or attach it to the Open event of the workbook so that it fires automatically when you open the file):
Sub Start_Running_Timed_Routine()
Application.OnTime _
EarliestTime:="14:00:00", _
Procedure:="Timed_Routine"
End Sub
Sub Timed_Routine()
'do something useful
If Hour(Now) < 15 Then
Application.OnTime _
EarliestTime:=Now + TimeValue("00:10:00"), _
Procedure:="Timed_Routine"
End If
End Sub
@@WiseOwlTutorials Thanks for your valuable time you are great human being as like yours great knowledge 👍👍👍👍❤️❤️❤️❤️, hope this will work for me
@@WiseOwlTutorials I am not from coding background but still I understand yours coding thanks ❤️❤️❤️❤️
@@nvcworld6423 You're very welcome!
@@WiseOwlTutorials I have tried this but giving error 13 type mismatch
for "FilmRarting" you have to crate a new sheet for which "short" "medium" "long" ,that otherwise it said "error 9"
Just fantastic! Thanks!
Good job my friend.
Hi Team WiseOwl can you help me about looping i want whenever you enter a value in the ID text box, Excel VBA loads the corresponding record. When you click the Edit / Add button, Excel VBA edits the record on the sheet or adds the record when the ID does not yet exist. need your help TIA
Thanks Andrew! im very happy to have this channel
i already watch the video but i think i need the after update in textbox ID , example i type a 123456 in textbox ID if ID 123456 is in the data the corresponding data will show in other text box and if 123456 is nothing in the data the form will clear and nothing show error anyway i like the disable method
Thank you so much Andrew !!
www.excel-easy.com/vba/examples/interactive-userform.html hi andrew this is what I want but I don't know how can you create a video tutorial TIA
He's got tons of videos on user forms. Watch his channel.
Simply the best!!!!
Hi WiseOwl 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
Dear Andrew, after this video I am convinced that I can do very useful things with the vba and I am just at number fifteen! But I would like to ask you how can I solve this: instead of the FilmLenght, I have a variable in column D which is the PraticNumber (as integer) composed of 13 digits (for example 2193458345937). Based on the last number (in this case 7) I must get the name of the assigned operator to write in column E. In a nutshell, instead of FilmRating I have the variabile called OperatorName (as string). If the last number of PraticNumber is between zero and 4, OperatorName must be Mauro, if instead it is between 5 and 9 OperatorName must be Paul. How do I have to set the statement if to get this result? Thank you very much in advance!
Thanks endless Andrew! You are simply the best! Greetings from Italy.
Dear Andrew,
the code that you suggested works perfectly (thanks!), but only if PraticNumber is a single number (example 1,2,3,4, etc.).
The numbers of my practices, instead, are of 13 numbers, so I have to extract the last digit of that number and based on it assign the practice to Mauro or Paul.
So I'll watch your video n. 53.2 because I need to find the VBA code that matches the extracted excel string function.
How can I copy the columns headings of the original sheet (in your video called Top Movies 2012) on the top of the three sheets just created? In my case I have to copy the first row only.
Thank you very much!
Thanks for the awesome videos. I noticed in a code that I wrote once:
"Loop Until Round(X2, 2) = Round(L, 2)" this works when X2 reaches the value of L (X2 is increased incrementally by 0.1L).
If however I used "Loop Until X2 = L," it doesn't finish the loop when L is = X2.
I am tracking the Locals window and in both cases, X2 reaches L, the first case the loop stops while the second case the loop keeps going until X2 is too big and then out of range error.
the full code (this is a mix of self-learning from the videos + the WO Blog):
Sub DrawingBendingMoment()
Dim C1 As Range, C2 As Range, C3 As Range, C4 As Range, C5 As Range
Dim ws As Worksheet
Dim sh As Shape
Dim L, W, X, X2, R, fac1, fac2, fac3, M1, M2, C
L = 52
W = 6.32
X = L / 10
fac2 = 0.1
R = W * L / 2
X2 = 0
C = 3
Set C1 = Cells(6, C)
Set C2 = Cells(6, 7)
Set C3 = Cells(5, 3)
Set C4 = Cells(5, 7)
Set C5 = Cells(5, 4)
Set ws = ActiveSheet
For Each sh In ws.Shapes
If Not (sh.Type = msoOLEControlObject Or sh.Type = msoFormControl) Then sh.Delete
Next sh
ws.Shapes.AddConnector(msoConnectorStraight, C1.Left, C1.Top + C1.Height / 2, C2.Left + C2.Width, C2.Top + C2.Height / 2).Select
Do
For fac1 = 0 To 1
M1 = Round(R * X2 - W * X2 ^ 2 / 2, 2)
M2 = Round(R * (X2 + X) - W * (X2 + X) ^ 2 / 2, 2)
ws.Shapes.AddConnector(msoConnectorStraight, C1.Left + fac1 * C1.Width / 2, C1.Top + C1.Height / 2, C1.Left + fac1 * C1.Width / 2, C1.Top + C1.Height / 2 + M1 * fac2).Select
If fac1 = 0 Then
fac3 = 1
ElseIf fac1 = 1 Then
fac3 = 2
End If
ws.Shapes.AddConnector(msoConnectorStraight, C1.Left + fac1 * C1.Width / 2, C1.Top + C1.Height / 2 + M1 * fac2, C1.Left + fac3 * C1.Width / 2, C1.Top + C1.Height / 2 + M2 * fac2).Select
X2 = X2 + X
Next fac1
C = C + 1
Set C1 = Cells(6, C)
'Loop Until X2 = L
Loop Until Round(X2, 2) = Round(L, 2)
End Sub
It shows Variant/Double. I will try to specify the date type to Single or Double, and see how it goes. Thanks for the advise :)
Hi , i have watched several videos that helped quite much get into vba, but now i am reallly stuck, can someone help me please!
I need to sum a product n-times: It should be like this:
The product is this: 10*Range("H8").value*Cos(Angle).....
The next product to sum is:10*Range("H8").value*Cos(Angle-range("B17").value/10)...
and the next 10*Range("H8").value*Cos(Angle-2*(range("B17").value/10)
and so on n-times,
until the angle becomes ZERO. So, the product will be sumed n-times according to the times it needs for the angle to be zero.
Wow. Just great! thanks
Excellent! Thank you
thank you ! awesome vids
couldn't be better
Thank you!
Thank you.
thank you! :)
wooooooow..........mind burgling......
Great...................
Hey Andrew! I'm from BA Argentina, and I love your videos, when I found your videos I watched everyday everytime I could, you have a really understandable method, even some guys that went outside my work looking for training now they are asking me to help them for some VBA codes, please continue developing this videos with detail as you did it so far....
I have asked in a lot of forums but I couldn't find a simple answer to fix my code or make it easier to run... could you help me with this? this is the link with my code and the explanation ...
Shortly ... I need to automate bank reconciliations, matching transactions by numbers when they sum zero, I think a loop and some nested ifs would work but I'm missing something... any suggestion?? thanks in advance .. you are my Obi Wan Kenobi in VBA :)
stackoverflow.com/questions/28868259/vba-create-a-macro-to-match-items-in-a-bank-reconciliation-payment-booking-ba
***** no problem , I could fix it thanks to a video upload from an Indian guy, the compensation was done with two "do loops" and some counters to match amounts, and then the code writes references to each reconciliated items, I was close but not enough, your videos are awesome, thanks to you I'm the teacher on my company lol , hope you continue with the videos!! thanks for your answer!!
Its amazing how much your code speeds up with
Application.ScreenUpdating = False
Thanks
Thanks so much for your support!
Thanks!
Terima kasih.
Thank you so much for the support!
Thanks
Thank you as always for your support, it's much appreciated!
Thanks!
Thank you so much for the support!
Terima kasih.
Thank you so much for your support!
Thanks
Many thanks!
Thanks!
Thanks so much for the support!
Terima kasih.
Thank you so much!
Thanks!
Thank you so much for your support!
Terima kasih.
Thanks!
Thank you for all your support!
Thanks!
Thank you so much for your continued generosity!