MY FULL EXCEL VBA COURSE (Beginner to Expert) [35% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_lR04WsiKlnc 200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
Clearly this information is timeless.😀 This was perfect for me Don so thanks so much. I've been building 'stuff' over the past weeks but hate not really understanding what it is I'm doing. Makes it difficult to reuse... The numerous other beginners tutorials talk in terms of an entire line of code where as you go to the lengths of explaining each element of each line which makes it understandable to me (a 60 yr old newbie)..... Thanks again Don.
I’m mid way through this tutorial and I have to say you are doing an excellent job! I didn’t understand the xlUp part but u covered it in a comment below in response to a question from a viewer and for that I applaud you good Sir
I tried a few other similar tutorials with some confusions. This is the one much easier to understand, very well explained step by step. I really like it.
Thank you for your brilliant tutorials. Your method of teaching and your explanations are exceptional. I look forward to learning from each and every one of your classes. Heather
I'm really glad you like them :))) Here is the playlist for this tutorial series if you're interested: ua-cam.com/play/PLxhsXZXQXrUB6Zw8a18hEiJxhgWUxL07i.html
awesome. clear. no wasted space. I have a question: What if instead of the entire row, I want to copy/paste a cell to the right or two cells to the right of Thanos??
Thank you so much, Don. This is very helpful and the idea of reversing the loop is brilliant and you have explained that very clearly. However, maybe if you used F8 to step into the code that would have been better shown the progress of the loop especially to those who might have found it a bit confusing.
Hello, I am going through your classes and they are pretty good. Can you please tell me how to do mathematical computations using macros, by taking in the values.
You need to tell it in which direction to find the last cell. Writing "xlUp" makes it, basically, go from the last cell and search UP and this eliminates problems that you could encounter by searching from the top of the spreadsheet, such as Excel returning the value for the first empty cell even when there is more data below that empty cell. That might sound confusing, but to test it out replace Cells(Rows.Count, 1).End(xlUp) with Cells(1, 1).End(xlDown) and delete the value from cell A6 and output the row number into a msgbox so you can see it in the spreadsheet and that should help clear it up. The left/right options for End work in a similar way but would be used to find the last column for a data set.
Fantastic tutorial, thanks. I was going to ask if there is a way of checking if EntireRow.Delete = True (that is, that the macro just deleted a row and then making i = i-1. Then I realized that I could reset i within the if loop and that would be the end of it. So, if you need the data to be imported in the same order, then inside the If loop after 'Delete the copied data. Sheets("Import").Cells(i, 1).EntireRow.Delete Add in the following, before the End if 'Reset i i = i - 1 Do not forget to reset the For i loop to low to high (otherwise you get the error the inverse loop is designed to avoid). Thanks again for helping me learn.
Your videos are excellent. Do you know of a way to loop through an entire range of cells and compare the contents of each cell in that range to the contents of another range of cells. If a match is found, then do something (e.g. copy matched cell context to another cell)? Thank you.
Too many adds in your website. I gave up. I'm really sorry. Thank you for your videos anyway. I'm following your instructions on youtube and it's working fine.
is there a way that i can write the if statement on a range of cells per column? i have a table that shows multiple dates and i want to "archive" the data using a drop down and selecting archive. its in multiple columns. from 8 to 38.
Loved your tutorial! But i need to find a solution too a problem. I have a column of data in sheet1 , a textbox and a "button" in sheet2. i want, when the button is pressed, data from sheet1 to present in textbox in sheet2 from top cell to last cell "(end.xldown) - 1 sort of" one cell per button press a time. similar to msgbox... I managed to find a code online that did just that. BUT the problem is that its a randomized result in the textbox: Sub Uppgiftsgenerator() Dim ws As Worksheet Dim tabellomr As range Dim slump As Long Dim antal As Long Set ws = Worksheets("Händelseliggare") antal = ws.range("a1").End(xlDown).Row - 1 Set tabellomr = ws.range("c2:c" & antal + 1) Randomize slump = Int(antal * Rnd() + 1) Worksheets("Uppgiftsgenerator").Shapes("textruta 1").TextFrame.Characters.Text = tabellomr(slump, 1) End Sub did i make my self clear? English is not my native languish... If you know Swedish that would help a lot ;) thankful for any help !
I couldnt understand if i denotes last row in sheet,why does it copy first entire row of the sheet to the other one? Didnt it have to take last row for copying entire row to other sheet?
What if you wanted to import the data from the last row up a certain number of rows (let's say 60 rows)? How would that change this code "For i = import_last_row To 2 Step -1"?
I saw another way to find the number of rows - CurrentRegion.Rows.Count. It seems very intuitive. Any reason this would not work as well as your examples? Or are they all just different ways to skin a cat?
Try using that method when you have empty rows in the middle of your data set and when your data set/table starts on a row that is below row 1. The method that I show is, generally, more versatile.
Thanks :) If you have any thoughts on what should be in the next one let me know! I want to make sure that this Practical macros series remains relevant and that I don't end up covering obscure and not-so-useful features.
I have to format a bank export. I need to split it into positive and negative values onto separate worksheets then format it to make it look a little prettier than the original csv :) - bold titles, number formats, column width etc. I can sort of see how this would happen with the looping macro looking for if i < 0, but something showing this would be awesome - well, for me anyway! I don't get out much, you see... :) But in all seriousness, this is a truly practical application that I am sure many accountants would find useful.
You're on the right track for sure, you would do something like IF cells(i,1).value < 0 Then BLAH. But, you should ask this question on our forum and upload a sample file, then you can get better help! www.teachexcel.com/talk/microsoft-office?src=yt
Right now there is not, but working between workbooks in VBA is heavily covered in the course that I'm working on now and if you're a newsletter subscriber on teachexcel.com you will be notified when its releasd, which I hope will be soonish lol
Cant really understand 17;44 there should have been all rows instead of only first,because equation of i denotes from last row to 1.In sheet Import downside up sequence is all rows from last to one,but it copied only first row,if someone knows please help.
I'm nood in macros Sheets("Sheet3").Cells(Data, 2).EntireRow.Copy Sheets("sheet3").Cells(LastdataRow + 1, 2) But i'm getting Run-time error 1004 you can't paste this here because the copy area and paste area aren't the same size. ...........! + I have formuas in the cells which i'm copying, and because of that I get 0,00 in sead of true value. What can I do?
MY FULL EXCEL VBA COURSE (Beginner to Expert) [35% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_lR04WsiKlnc
200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
Clearly this information is timeless.😀 This was perfect for me Don so thanks so much. I've been building 'stuff' over the past weeks but hate not really understanding what it is I'm doing. Makes it difficult to reuse... The numerous other beginners tutorials talk in terms of an entire line of code where as you go to the lengths of explaining each element of each line which makes it understandable to me (a 60 yr old newbie)..... Thanks again Don.
thank you i like your way of explaining things directly and simply without waisting time 😍😍
I’m mid way through this tutorial and I have to say you are doing an excellent job! I didn’t understand the xlUp part but u covered it in a comment below in response to a question from a viewer and for that I applaud you good Sir
This macro tutorial was awesome. Extremely useful since It's so relevant in real life, especially for me as a business controller 😁
I'm very very glad you found it so useful :)
I tried a few other similar tutorials with some confusions. This is the one much easier to understand, very well explained step by step. I really like it.
Thank you for your brilliant tutorials. Your method of teaching and your explanations are exceptional. I look forward to learning from each and every one of your classes. Heather
I'm really glad you like them :))) Here is the playlist for this tutorial series if you're interested: ua-cam.com/play/PLxhsXZXQXrUB6Zw8a18hEiJxhgWUxL07i.html
yours teaching way is so wonderful 👍👍👍👍👍
Bruh, thank you very much for these lessons. They're invaluable.
awesome. clear. no wasted space. I have a question: What if instead of the entire row, I want to copy/paste a cell to the right or two cells to the right of Thanos??
Thanks for this. I really enjoyed this lesson. Thanks for sharing your knowledge.
You're very welcome Vincent! I'm glad you enjoyed it!)
Thank you so much, Don. This is very helpful and the idea of reversing the loop is brilliant and you have explained that very clearly. However, maybe if you used F8 to step into the code that would have been better shown the progress of the loop especially to those who might have found it a bit confusing.
Hello, I am going through your classes and they are pretty good. Can you please tell me how to do mathematical computations using macros, by taking in the values.
Thank you very much, TeachExcel for the videos! I am finding them extremely helpful to learn macros.
You are very welcome my friend!)
Beautiful video!!! One question: why there is (xlUp) next to the End? Isn't end enough to write End only
You need to tell it in which direction to find the last cell. Writing "xlUp" makes it, basically, go from the last cell and search UP and this eliminates problems that you could encounter by searching from the top of the spreadsheet, such as Excel returning the value for the first empty cell even when there is more data below that empty cell. That might sound confusing, but to test it out replace Cells(Rows.Count, 1).End(xlUp) with Cells(1, 1).End(xlDown) and delete the value from cell A6 and output the row number into a msgbox so you can see it in the spreadsheet and that should help clear it up. The left/right options for End work in a similar way but would be used to find the last column for a data set.
Fantastic tutorial, thanks. I was going to ask if there is a way of checking if EntireRow.Delete = True (that is, that the macro just deleted a row and then making i = i-1. Then I realized that I could reset i within the if loop and that would be the end of it. So, if you need the data to be imported in the same order, then inside the If loop after
'Delete the copied data.
Sheets("Import").Cells(i, 1).EntireRow.Delete
Add in the following, before the End if
'Reset i
i = i - 1
Do not forget to reset the For i loop to low to high (otherwise you get the error the inverse loop is designed to avoid).
Thanks again for helping me learn.
Thank you. How would the code change if you only wanted to copy several cells in the row as opposed to the entire row?
This was a huge help for something I was working on, thanks! Awesome tutorials
Nice works Sir! I love your tutorials. You explained every steps well. I just want to ask what if you want to copy from column b to d only?
You are an incredible tutor, thank you.
i like the explanation thanks :) i was just wondering why didn't work with me at first
Your videos are excellent. Do you know of a way to loop through an entire range of cells and compare the contents of each cell in that range to the contents of another range of cells. If a match is found, then do something (e.g. copy matched cell context to another cell)? Thank you.
I love you
in a totally platonic way ofc
Too many adds in your website. I gave up. I'm really sorry. Thank you for your videos anyway. I'm following your instructions on youtube and it's working fine.
Thanks for your class ... Really very useful
I like your tutorials, please keep this up :)
Thanks for the support! I've got a series on Idiot-Proofing forms out now, but next up I'll try and do more vba/macros.
Thank you, that helped me with a small project i'm doing, i just have 1 question, i'm trying to assign a macro button to do this, and i can't, why?
Thank you very much for your instruction
is there a way that i can write the if statement on a range of cells per column? i have a table that shows multiple dates and i want to "archive" the data using a drop down and selecting archive. its in multiple columns. from 8 to 38.
Thank you very much for your clip. It nice to sample.
nd easy way of understanding
Thanks
Loved your tutorial! But i need to find a solution too a problem.
I have a column of data in sheet1 , a textbox and a "button" in sheet2. i want, when the button is pressed, data from sheet1 to present in textbox in sheet2 from top cell to last cell "(end.xldown) - 1 sort of" one cell per button press a time. similar to msgbox...
I managed to find a code online that did just that. BUT the problem is that its a randomized result in the textbox:
Sub Uppgiftsgenerator()
Dim ws As Worksheet
Dim tabellomr As range
Dim slump As Long
Dim antal As Long
Set ws = Worksheets("Händelseliggare")
antal = ws.range("a1").End(xlDown).Row - 1
Set tabellomr = ws.range("c2:c" & antal + 1)
Randomize
slump = Int(antal * Rnd() + 1)
Worksheets("Uppgiftsgenerator").Shapes("textruta 1").TextFrame.Characters.Text = tabellomr(slump, 1)
End Sub
did i make my self clear? English is not my native languish... If you know Swedish that would help a lot ;)
thankful for any help !
I couldnt understand if i denotes last row in sheet,why does it copy first entire row of the sheet to the other one? Didnt it have to take last row for copying entire row to other sheet?
What if you wanted to import the data from the last row up a certain number of rows (let's say 60 rows)? How would that change this code "For i = import_last_row To 2 Step -1"?
I saw another way to find the number of rows - CurrentRegion.Rows.Count. It seems very intuitive. Any reason this would not work as well as your examples? Or are they all just different ways to skin a cat?
Try using that method when you have empty rows in the middle of your data set and when your data set/table starts on a row that is below row 1. The method that I show is, generally, more versatile.
Thanks for the explanation
great stuff !
I can't get this to work on excel 2016 it only copies 1 line. Is this due to the version I am using
What a wonderful tutorial!
Thanks :) If you have any thoughts on what should be in the next one let me know! I want to make sure that this Practical macros series remains relevant and that I don't end up covering obscure and not-so-useful features.
Yeah sure why not dear! If I got any new ideas I will let you know.
I have to format a bank export. I need to split it into positive and negative values onto separate worksheets then format it to make it look a little prettier than the original csv :) - bold titles, number formats, column width etc. I can sort of see how this would happen with the looping macro looking for if i < 0, but something showing this would be awesome - well, for me anyway! I don't get out much, you see... :) But in all seriousness, this is a truly practical application that I am sure many accountants would find useful.
You're on the right track for sure, you would do something like IF cells(i,1).value < 0 Then BLAH. But, you should ask this question on our forum and upload a sample file, then you can get better help! www.teachexcel.com/talk/microsoft-office?src=yt
Im trying to run a macro everytime a cell changes. Any help would be appreciated
James, I have just the tutorial for you! ua-cam.com/video/HsiwC9xg06c/v-deo.html
But how do we get it to do do this from another workbook, is there a tutorial on that?
Right now there is not, but working between workbooks in VBA is heavily covered in the course that I'm working on now and if you're a newsletter subscriber on teachexcel.com you will be notified when its releasd, which I hope will be soonish lol
Love the laugh and its reason at 25:00
Cant really understand 17;44 there should have been all rows instead of only first,because equation of i denotes from last row to 1.In sheet Import downside up sequence is all rows from last to one,but it copied only first row,if someone knows please help.
I'm nood in macros
Sheets("Sheet3").Cells(Data, 2).EntireRow.Copy Sheets("sheet3").Cells(LastdataRow + 1, 2)
But i'm getting Run-time error 1004
you can't paste this here because the copy area and paste area aren't the same size. ...........!
+ I have formuas in the cells which i'm copying, and because of that I get 0,00 in sead of true value.
What can I do?
how do i copy only selected column the the row and with 2 condition
we can even do it without using .value
Why cant we just use formulas instead of macros?
With VBA School fee & other data management video created sir file
please make this statement shorter... thank you in advance...
I tried "Keys.Value = Range("O" & Inum).Value" but return an error.
If Inum = 1 Then
Keys.Value = Range("O1").Value
ElseIf Inum = 2 Then
Keys.Value = Range("O2").Value
ElseIf Inum = 3 Then
Keys.Value = Range("O3").Value
ElseIf Inum = 4 Then
Keys.Value = Range("O4").Value
ElseIf Inum = 5 Then
Keys.Value = Range("O5").Value
ElseIf Inum = 6 Then
Keys.Value = Range("O6").Value
ElseIf Inum = 7 Then
Keys.Value = Range("O7").Value
ElseIf Inum = 8 Then
Keys.Value = Range("O8").Value
ElseIf Inum = 9 Then
Keys.Value = Range("O9").Value
ElseIf Inum = 10 Then
Keys.Value = Range("O10").Value
confusing, the last section thanos was below thor , but the outcome become below thor and spiderman
Oh, the second time u insert in different rows
Witness the number of thumbs-up decreased since the first tutorial. lol
This pastes the data upside down (first row last-last row first), not helpful if you need the data set to stay in the same order.