Das war wirklich sehr gut. Habe mich lange gefragt, wie man range mit for verbinden kann. cells mit for war kein Problem. Vielen Dank für deine Arbeit.
@WiseOwlTutorials
2 жыл бұрын
You're very welcome Marin! I'm happy to hear that you found it useful and thank you for watching!
@maurocastagnera8949
7 жыл бұрын
Dear Andrew, it is really a pleasure to watch your videos and listen to your voice. At this time it's 8.15 p.m. on Saturday but I can not get out of the pc because every minute passes you teach me something new on the VBA. I really do not have any more words to thank you. You are really an excellent teacher. Thanks for all that I am learning from you!
@manishpanwar1421
4 жыл бұрын
I agree
@ishwarsingh881
2 жыл бұрын
Dear Sir Your way of presentation is so simple and easily understandable. You are great.
@qasimawan3569
Жыл бұрын
I agree
@houstonvanhoy7767
6 ай бұрын
@@ishwarsingh881 Yes. Some people can DO things, some people can TEACH things, but Andrew is one of the few who can do AND teach.
@guthrie_the_wizard
6 жыл бұрын
Just donated as a thank you! I would encourage anyone that feels they have gotten a lot of value to do the same.
@jamarsyn
10 жыл бұрын
Your tutorials are outstanding. You are an excellent teacher.
@thomaswashington8199
5 жыл бұрын
For Efficient Reference: The Syntax of a For Next Loop :41 The Step Keyword 3:30 Exiting from a for Next Loop 5:11 Looping Over Worksheets 6:56 Looping Over Workbooks 11:47 Looping Over Cells 16:38
@nadermounir8228
2 жыл бұрын
Thank you very much Andrew. Great video 📹 👍
@WiseOwlTutorials
2 жыл бұрын
Thanks Nader, glad you enjoyed it!
@ksujithkarun
9 жыл бұрын
Excellent video. Though I have gone through several other training materials, the concept of For - Next loop has never been clearer to me. Thanks for creating & sharing this...
@BigWes95
6 жыл бұрын
Appreciate so much that you've put these videos on KZitem for free. I payed a decent whack for an online course that is a pile of horseshit compared to your videos. Always very thorough and extremely well explained.
@larrykent196
6 жыл бұрын
Again outstanding VBA training video, thank you.
@inactiverahul
3 жыл бұрын
Amazing as always :)
@WiseOwlTutorials
3 жыл бұрын
Thank you Rahul!
@rajamanickamramalingam8786
5 жыл бұрын
Excellent explanation. Thank you.
@WiseOwlTutorials
5 жыл бұрын
You're welcome Raja, thank you for watching!
@tajsay
4 жыл бұрын
thank you so much Andrew.
@WiseOwlTutorials
4 жыл бұрын
You are very welcome Sayyad!
@tajsay
4 жыл бұрын
@@WiseOwlTutorials Hi Andrew I have started learning vba a month ago and I am going through all of your videos those are best online videos I ever come across. I'm looking for a way to get a list of properties of an object, for example a button object would have backcolor, text properties. I want to get all properties into an array of some sort. I found some helpful code on the web that was using Type Library Info library, I couldn't get it to work 100% right, but is there a better way to do it. Please guide. Thanks again.
@WiseOwlTutorials
4 жыл бұрын
Hi Sayyad, I suspect that you're following the guide here stackoverflow.com/questions/19783180/get-list-of-all-properties-for-an-object I don't have the TLBINF32.dll available on my machine and I don't know of any other way to do this in VBA I'm afraid. If I stumble across a solution I'll be sure to let you know!
@NFarooq176
8 жыл бұрын
Hi, thanks for your videos they have been incredibly helpful! I notice you have done the 'Rate All Films' example using a For Next loop in this video, and a Do Until Loop in a previous video. For practical purposes, which would be better to use and why? Thanks again
@wasimbader9170
8 жыл бұрын
THANK YOU FOR THIS ONE. GREAT JOB ANDREW. THUMBS UP
@TheJaebeomPark
4 жыл бұрын
You are awesome
@WiseOwlTutorials
4 жыл бұрын
Thank you!
@shelleyprice1887
10 жыл бұрын
Hi Andrew. First let me start by saying that I have watched a number of your videos (some more than once) and I am very impressed with your ability to explain seemingly complicated information. Secondly, I should mention that I am very new to VBA Excel and have a complicated task at hand which I am hoping you can help me with. I am processing a set of data using Case Select statements and then sorting (by copying) that information to new sheets within the same worksheet- similar to what you did with film length (short, medium, long into new tabs). This is fine and dandy- no problems. What I wish to do then is to have that "loop" process through most of the sheets in the workbook (excluding the "new sheets" which have been copied into). I have been attempting to do this using a "For each/ Next" statement and using the index number of the sheets, but I am apparently not skilled enough to do so. Any advice would be greatly appreciated!
@tristanquintus3200
10 жыл бұрын
Thanks - a very useful video that I am sure I will utilize...
@morsheddld
8 жыл бұрын
Awesome video !!!!! i appreciate you for your contribution to #Excel Training. i must say, this video is pretty much useful for the beginners like us.
@Tat-cx1lr
7 жыл бұрын
Merci pour le conseil. Encore une fois vos vidéos sont excellentes et très utiles.
@marcinsienkiewicz4275
9 жыл бұрын
Great tutorials, thank you Mr Andrew
@sanukumar7122
5 жыл бұрын
Thanks
@samuelkingston4572
7 жыл бұрын
Very useful & easy to learn. Excellent.
@Mirelinin
5 жыл бұрын
18:02 - could you please explain why have you chosen the bottom of your list A2, when the bottom of your list is A14? Also, if I specify A14 as the bottom of my list, the macro goes absolutely nuts and crashes my excel :D. On the other hand, if I choose A13 as the bottom, then it works just fine.
@Mirelinin
5 жыл бұрын
Also, if I choose bottom Range A11, and let's say, I add additional rows into the sheet. The code goes through, for example, 20 rows, however, in the code I specified the bottom as A11. :/
@gianchi86
9 жыл бұрын
Thanks a lot for these video!
@Blazer433
6 жыл бұрын
nice video. I have a question: how can i make my own npv & irr function?
@lowiehojas5525
8 жыл бұрын
Thank you very much for your videos!
@violman100
7 жыл бұрын
Hi Andrew! thx for your excellent videos. I learned A LOT form your sql, ssrs and vba videos. A question for this particular video. Why do you use 2 variables on the protectsheet sub? i used only one variable and it workd fine. Do i miss something by not using the second variable? that's my sub Sub protect() Dim now As Integer now = Worksheets.Count For now = 1 To now Worksheets(now).protect Next now End Sub
@rajivankrishnan9617
9 жыл бұрын
Simple and easy to understand =)
@perfumer8549
2 жыл бұрын
first, thank you very much for sharing this excellent tutorial for free Why did you use the range "A3" to count the number of sells ? isn't it included if we start counting from A2 to the end of the column? thanks in advance
@WiseOwlTutorials
2 жыл бұрын
Hi! Starting in range A2 would include the column headers but I only wanted to loop through the rows of data. I hope that helps!
@perfumer8549
2 жыл бұрын
@@WiseOwlTutorials thank you so much for the quick reply what if we say the number of sells = Range ("A3" , Range ("A3").End (xlDown)).cout will we get the same number of sells if we change the A2 to A3
@WiseOwlTutorials
2 жыл бұрын
@@perfumer8549 Hi! Yes, in this example you would get the same result. If your list contained only one row (so if cell A4 was empty) you would get quite different results. Using cell A2 means the code works even if the list contains only one item. I hope that helps!
@DennisYeadon
6 жыл бұрын
Thank you
@AndrewFrankRevolution
10 жыл бұрын
Outstanding as always
@houstonvanhoy7767
6 ай бұрын
Hello, fellow viewers. Andrew (correctly) does not like to use ancient variable names "i" and "j" and "k", left over from 1970s mainframe programming practices. If anyone questions you about declaring a variable named "LoopCounter" instead of "i", just reply that you are not programming in Fortran. And if you are feeling feisty, you might add: "How old are you, anyway?"
@WiseOwlTutorials
6 ай бұрын
😀thanks I appreciate the support! Although I have to admit that my variable names have tended to become shorter the older I've become!
@Tubedog8
10 жыл бұрын
Great Video. I have a workbook that has had many sheets copied and renamed for new clients. Now the Sheet Number doesn't match the number that the code says it it. In other words - In the Project Explorer there is a list of sheets with their sheet number but the value in the code is different. ie Sheet 12 is Sheet48? Can I renumber the sheets? After a new sheet is created I put the last name of the customer as the Sheet Name then drag is to Alpha order in the workbook. This seems to cause confusion with the sheet number. Any suggestions?
@jadenguyen9564
8 жыл бұрын
Andrew, I came across several articles referring to the difference between For...Next (loop) and Do Until/ While ....Loop, but couldn't find the satisfactory answer. Could you please advise which one is better since both methods give the same results?
@zaidsada6841
8 жыл бұрын
Isn't the last example possible to execute by directly using a Nested IF in the adjacent column without using VBA?
@mohamedelmoslmany
4 жыл бұрын
how to copy the result (for/next) to anothere sheet
@qasimawan3569
Жыл бұрын
Might be a stupid question but can you protect/unprotect worksheets using the "Do Unil & Do While" method? Is there any advantage to using one over the other?
@WiseOwlTutorials
Жыл бұрын
Hi Qasim! Yes, you can use a Do Until/While loop but if you're trying to do anything to a collection of objects (like worksheets) the best option is a For Each loop - the syntax is just so much simpler. Here's a comparison: Sub DoUntilProtect() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets(1) Do Until ws Is Nothing ws.Protect Set ws = ws.Next Loop End Sub Sub DoWhileProtect() Dim ws As Worksheet Set ws = ThisWorkbook.Worksheets(1) Do While Not ws Is Nothing ws.Protect Set ws = ws.Next Loop End Sub Sub ForProtect() Dim i As Integer Dim ws As Worksheet For i = 1 To ThisWorkbook.Worksheets.Count Set ws = ThisWorkbook.Worksheets(i) ws.Protect Next i End Sub Sub ForEachProtect() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Protect Next ws End Sub
@b.petrushchak
9 жыл бұрын
Thank you!
@TJGalloway1
9 жыл бұрын
Mister Owl, what is the best function in VBA that can determine the class of data in a column? Such as date, numeric or string.
@TJGalloway1
9 жыл бұрын
Awesome. This is exactly what I was looking for. Thanks Andrew.
@perrykatz9381
7 жыл бұрын
Hi, I have watched all your videos but I have a question: I want to set up for i = 1 to 1000 "code" next i but instead of 1000, I want to use a variable number that depends on how many items are in that column....the only issue is that the code creates the column so i cannot set it up to begin with..... please help!!!
@lydialu8486
7 жыл бұрын
Hello! How did you get the random number 0.76 at 6:34?
@dbascb
10 жыл бұрын
Excellent! Thank you
@eduardmart1237
8 жыл бұрын
Cool videos! It's a masterpiece!
@Search4Knowledge
8 жыл бұрын
Thanks alot!
@ArunKumar-yb2jn
2 жыл бұрын
Is it any different if I do NumberofCells = Range("A3", Range("A3").End(xlDown)).Count instead of NumberofCells = Range("A3", Range("A2").End(xlDown)).Count I quite didn't get why we got to start from A2 instead of A3.
@WiseOwlTutorials
2 жыл бұрын
It depends how long your list is! If your list has only one item then using "A2").End(xlDown) will stop at cell A3. Using "A3").End(xlDown) will stop at the bottom of the worksheet!
@TheKhushty
10 жыл бұрын
Uploaded on my Birthday :D
@delhiverma
10 жыл бұрын
Thanks for useful video....
@tekdahal1
9 жыл бұрын
Hi is it possible to do loop reverse? As an example: let say I have 8 rows of data (A1:A8) which I entered using loop and InputBox function. Now I am trying to do reverse the loop using loop function but I couldn't figure out the code. I want is data from cell A8 To A1 appear on A9 to A16. Would be greatly appreciated if u can help about it. This is what i have done for A1:A8 For i = 1 To 8 Step 1 Cells(i, 1).Value = InputBox("Enter SECTION") Next i Thanks, Tek
@tekdahal1
9 жыл бұрын
tek dahal Sorry I forgot to mention about it: How we put Negative sign in some value in Reverse Data part?
@emaus8344
6 жыл бұрын
When I try to store Number of cells into a veriable: NumOfCells = Range("A3", Range("A2")).End(xlDown).Count The value assigned is 1, therefore For Loop only goes once. What could be wrong?
@emaus8344
6 жыл бұрын
But when I store the cells into a range, it works then. Set NumOfCells = Range("A3", Range("A2")).End(xlDown)
@devexpost8508
6 жыл бұрын
Your second end-parenthesis is misplaced... should be: Range("A3", Range("A2").End(xlDown)).Count [Note also that if cell A2 is empty, this strategy will not work.]
@jonjxmagic
9 жыл бұрын
how will i code it if i want an infinite range of numbers?
@jonjxmagic
9 жыл бұрын
got the codes i was looking for. i was trying to come up with a code that when you try to input 10 random numbers then counts how many are odd and even. but Thanks. exam is over hahaha
@LaoWang-j3e
7 жыл бұрын
I found a weird thing when I tried to code by myself, could you please help to solve? The code is as below: ============================== For Each cell In ActiveSheet.Range("A3:A"&Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible) i = cell.Row (other code...) Next ============================== You see usually the variable i should be start with row 3, then show the visible row. when I select row 4 or 5, the variable i start with 4 or 5. -> That is the normal thing. but when I select row 3, the variable i start with 1 ! -> I don’t understand why. I’m so confused because the target range should exclude the range A1:A2. I really appreciate your help.
@idlevandal69
7 жыл бұрын
.End(xlUp) takes you to the end of the sheet then comes back up and looks for the first row with data in it. If there is no data it will take you back to row 1. Remove .End(xlUp) from the code and i will equal 3. Then you could do - i = cell.Row Range("A" & i).Select
@LaoWang-j3e
7 жыл бұрын
Thank you for replying, idlevandal69. I've check there is data in row A, so i shouldn't start from 1. Anyway, i set if then statement to ignore row 1 and 2.
@FRANKWHITE1996
8 жыл бұрын
thank you :)
@TrapBarWith41.5cmSleeves
5 жыл бұрын
BTW, you don't need to write the loop variable again after Next. It's redundant. I wonder why it's even there, it's useless. Simply write: for x=1 to 10 [do stuff] next
@adamlanghans
5 жыл бұрын
It's there for optional code readability. Multiple nested loops or loops with many lines of code may obfuscate the purpose of the loop.
Пікірлер: 85