Excel VBA Introduction Part 15 - Do Until and Do While Loops
HTML-код
- Опубликовано: 5 фев 2014
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/shop/andrew-goul...
By Andrew Gould
www.wiseowl.co.uk - Do Loops in VBA allow you to carry out a set of instructions repeatedly until some kind of condition is met. This video teaches you about the basics of the Do Loop statement including how to write Do Until and Do While loops, where to place your conditional statements and how to exit from a loop. The final part of the video provides a couple of longer examples using Do Loops to calculate new values for a list of data and then to split the list onto different worksheets.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Visual Studio, ASP.NET, VB.NET, C# and more!
The series just cannot be disliked. Superb content 😊
Thank you for watching!
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!
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!!!!
Great Tutorial Andrew. Ur way of explaining things is superb.
Thank you Nader, I appreciate the comments!
Whatever Mr. Andrew teaches us I will still believe that he is the best! From the Philippines. Thank you sir.
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.
Many thanks for videos. I'm watching in Brazil.
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
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.
I am constantly re-watching these videos. awesome
You guys are a life saver! Thanks so much for these video tutorials. Really taking my excel know-how to the next level :)
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.
Nothing Better than this video....on Loops...
Brilliant Way to Explain the Loops...
Superb..
You did an excellent work. No video ever created like your videos. Thanks a lot for whole things.
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!
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.
You are the best!, your way of explaining VBA concepts is amazing. Keep doing the good work! all the best.
thank you you are the best teacher in the whole world i watch all video
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😊
you deserve a big like .. thank u very much
Thanks for helping me with work 👍
Beat video on loops! Thank you.
wow super excited you are really amazing sir 👍🏻👍🏻👍🏻👍🏻👍🏻💯💯💯
Thank you Vijay, I'm glad you enjoyed the video!
Thanks Wise Owl.
That was wonderful, i'm speechless
thank you very much for your video. very practical & useful for beginners
Simply great content presented superbly! Well done!
Thank you!
Great tutorial. Thank you!
You are simply great sir...
Simply the best!!!!
Very helpful and easy to understand
Worth spending that 20 odd minutes. Thanks Owl,
Just fantastic! Thanks!
Very nice and helpful. Thank you.
thank you ! awesome vids
Wow. Just great! thanks
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.
Excellent! Thank you
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.
Excellent. Do you have the Excel datasets to use along with these tutorials?
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 :-)
Thanks!
Thank you so much for your continued generosity!
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
Good job my friend.
Thanks
Thank you as always for your support, it's much appreciated!
Thank you!
Your teaching style is simply amazing.
Do you have trainings in Java, Andriod programming etc?
Thank you.
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
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
great video, better than the videos where you need to pay on some websites..
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!
Thank you :)
Many thanks Melki!
@@WiseOwlTutorials sorry just reply, somehow replies to my comments don't appear in my feed
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 ruclips.net/video/0EXdPcbsTZI/видео.html
I hope that helps!
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.
thank you! :)
I was looking for intersect and union method can I get the video link
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?
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
Hi Andrew... Thank you so much for your videos. All your videos are great!!! I have a question not sure if you have an answer... I have seen your previous videos and I understand that you have many different versions of Excel. Do you use office 365 or Excel 2019? If so can you please try to run the same code on the new version of excel that you demonstrated @ 21:35?... I'm running Office Excel 365 and this took me exactly 20 seconds to run your code… I ran the same code on my old laptop that's using office 2010, this took less than a second to complete… (my worksheet have 15 rows and 5 columns..)
I do not understand why office excel 365 handles Copying, activating sheets and pasting so slowly... It took you less than a second to run on your tutorial… and it took my old laptop running office 2010 the same amount of time… I understand that there's more efficient way to write the same code such as using Array (and I have learn a lot of that from your videos, thank you!!!) However, I still want to understand why the speed get worse but not better on a newer version of excel? I have use office 2003 and 2010 before, it wasn't that bad… after upgrading to a new office running 64 bits everything turn bad… I have to revise a lot of my works that I have done for years..
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.
Hello sir i just want to search a data in a database and edit the search's data values in the coloum.
Great...................
wooooooow..........mind burgling......
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
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!!
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!
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:
ruclips.net/p/PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5 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.
ruclips.net/p/PLNIs-AWhQzckV9rAM3yv8ym4pioIMA0UR 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!
Terima kasih.
Thank you so much for your support!
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.
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.
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???
couldn't be better
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.
Plz what to do if the while loop still incrementing
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 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.
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!!!
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!
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!
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....
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
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.
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"
i think u haven't used 'Do while. Is Do While and Do untill the same?
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!
Thank you for all your support!
Thanks
Thanks so much for your support!
Terima kasih.
Thank you so much for the support!
Thanks!
Thank you so much for your support!
Thanks!
Thanks so much for the support!
Thanks!
Thank you so much for the support!
Thanks
Thank you so much for your support!
Thanks!
Thanks!