VBA For Loop - A Complete Guide

VBA For Loop - A Complete Guide

Excel Macro Mastery

3 года назад

78,295 Просмотров

Ссылки и html тэги не поддерживаются


Комментарии:

Gerard van Eggermond
Gerard van Eggermond - 14.06.2023 15:33

Hello sir, maybe you can help me, I made a code but it is too long now I want to reduce it, is this possible? One section has the following code;
Application.ScreenUpdating = False
If Sheets("Menu").Range("E2") = 1 Then
Sheets("toplay1").Select
Range("A1").Select5
ActiveCell.Formula2R1C1 = _
"=FILTER('S1'!R[1]C[1]:R[20]C[1],'S1'!R[1]C[1]:R[20]C[1]<>" "")"
Range("B1").Select
ActiveCell.Formula2R1C1 = _
"=FILTER('S1'!R[1]C[5]:R[20]C[5],'S1'!R[1]C[5]:R[20]C[5]<>" "")"

This continues, at (c[ ]) each time 4 is added until 37 is reached
Could you help me?
Thanks in advance.


d

Ответить
Edwin Gonzalez
Edwin Gonzalez - 12.06.2023 19:59

Hi, in the section "How the For Loop works" you have to define the worksheet that "shNumbers" is. My code was breaking until I figured I had define which worksheet would be "shNumbers". Hope this helps someone in the future.

Ответить
Josue Rioux
Josue Rioux - 27.05.2023 06:08

Hi, how to make a loop in criteria of an autofilter in a column

Ответить
Ali Alizadeh
Ali Alizadeh - 14.11.2022 03:40

Thank ya

Ответить
jk.tal22
jk.tal22 - 25.07.2022 17:29

Hey paul, how can you enter a new line if a condition is met?

Ответить
Salim S
Salim S - 02.07.2022 21:50

Thank you so much Paul.

Ответить
ananthu h09
ananthu h09 - 13.04.2022 10:48

Is it possible to give the sheetname, ranges, copy, destination sheet, destination range,and using lopping copy and paste based on the inputs given

Ответить
kaana padhiyar
kaana padhiyar - 06.03.2022 19:15

Plz solve this code

Sub findpairs()
Dim columndebtt As String
Dim debitcolumn As String

Dim coumnifpairIsfound As String
Dim counter As Long
Dim counterasstring As String
Dim debitaddress As String
Dim creditaddres As String
Dim valueToFind As Double
Dim counterNew As String
Dim counternNewAsstring As String
columnDebit = "B"
columncredit = "A"
columnIfpairisfound ="C"
counter = 2
counterasstring = counter
debitaddress = counterdebit & counterasstring
While (Range(debitaddress).Value <> "")
valueToFind = Range(debitaddress).vlaue
counterNew = 2
counterNewasstring = counterNew
creditaddress = columncredit & counterNewasstring
Do While (Range(creditaddress).Value <> "")
if (valueTofind <> 0 and valueTofind = range(creditAddress).Value and range(columnIfpairisfound & counterNewAsstring).
Range(columnifpairIsfound & counterNewasstring).Value = "YES"
Exit Do
End If
counterNew = counterNew + 1
creditNewasstring = counterNew
creditaddress = columncredit & counterNewasstring

Loop

counter = counter + 1
counterasstring = counter
debitaddress = columnDebit & counterasstring
Wend

Ответить
brandon
brandon - 12.02.2022 23:24

Do you realize how much money people can make with the right background using these skills? SCADA uses these principals all the time. Especially in the oil and gas industry. I am just barely scratching the surface of VBA in excel and I have already changed the company.

Ответить
Rukky Neel
Rukky Neel - 07.11.2021 17:22

hi Sir, I am stuck somehwere in excel. I hope you can help me.
I am generating a random number using vba command (randbetwee,1,6)...and i want to add the result each time when i clcik the button..
on click 1: random number 5, Store the value 5
on click 2: random number 2, store the value 7
on click 3: random number 1, store the value 8..and so on...can you please help me..many thanks in advnace.

Ответить
Joe Htoo
Joe Htoo - 21.09.2021 00:46

Great video! Thanks

Ответить
amar majeed
amar majeed - 03.09.2021 03:31

Thankyou for the facile explanation.

Ответить
João Neto
João Neto - 19.08.2021 02:44

Hi, thans a lot for your videos they are helping me a lot. I would like to ask abouy a doubt i have. Which aproach it is faster, a for loop or autofill? I handle with large data with 100.000 rows so the theme faster is important. And one more time, thanks a lot for your work.

Ответить
rohith nlp
rohith nlp - 15.08.2021 21:12

i love you paul from INDIA

Ответить
Brian Costello
Brian Costello - 21.07.2021 14:48

very clear, im trying to simulate time and have cells coloured in real time from milliseconds to seconds, each with differing intervals depending on user input, does VBA have built in time functions

Ответить
Luis Mateo
Luis Mateo - 19.07.2021 22:41

Very nice video

Ответить
jonny peace
jonny peace - 06.07.2021 14:34

I think if I used arrays, lbound and ubound more often, my code would be easier lol. Need to start using them more often 👍 Great video

Ответить
Ganesh Uttekar
Ganesh Uttekar - 04.07.2021 16:36

Hi sir, please make a video on Userform.

Ответить
Ratthapon Paikhayat
Ratthapon Paikhayat - 04.07.2021 12:36

Been followed you channel for awhile, do you have a recommended list to properly start from beginning level from your video?

Ответить
D. Suresh Kumar
D. Suresh Kumar - 04.07.2021 09:36

Vba is an excellent subject then why Microsoft company not encouraging such a nice language.. Plz advice. I will miss vba in my future.. It's eazy as far as what I knew

Ответить
Karl Lee
Karl Lee - 01.07.2021 04:58

I did not know For Each ran faster than For i. I always used For i because I could use the i within the loop. Thank you for the guide, very thorough.

Ответить
MikeBeliard
MikeBeliard - 30.06.2021 15:48

Great work!

Ответить
Alexis Gonzalez
Alexis Gonzalez - 25.06.2021 20:01

HI master, I'm from Santiago Chile, how can a buy your TheExcelVBAHandbook in equal parts? I can buy pdf1 in 40usd and so on?

Ответить
janez klun
janez klun - 23.06.2021 19:57

Good one, thank you, Cheers

Ответить
Wayne Edmondson
Wayne Edmondson - 17.06.2021 02:13

Hi Paul. Awesome lesson, as always! Nice to refresh these concepts and reinforce them in memory with practice and examples. Thanks for sharing :)) Thumbs up!!

Ответить
home jonny
home jonny - 15.06.2021 13:54

Please, which is this very nice mono font? Consolas?

Ответить
AlexB
AlexB - 13.06.2021 22:18

Please continue posting this kind of guides!

Ответить
Archibald Tuttle
Archibald Tuttle - 13.06.2021 13:41

Very nice! Just be aware that arrays can be zero based as well. Your I would then need to start at 0.

Ответить
Roger Prida
Roger Prida - 13.06.2021 00:42

Masterpiece!

Ответить
Frik Du Plessis
Frik Du Plessis - 12.06.2021 15:24

Great tutorial as always Paul 👌 clear and to the point, must say it's been a long time coming

Ответить
Miguel A. López
Miguel A. López - 12.06.2021 03:30

I'm starting with VBA... It's very useful to know how manage a For Next loop... Thanks!

Ответить
Geoffrey Jones
Geoffrey Jones - 11.06.2021 13:54

I have no idea what you're talking about.

Ответить
VIPUL JHAVERI
VIPUL JHAVERI - 11.06.2021 06:37

Brilliant - Crisp and to the point , thanks a lot Sir

Ответить
Hari Bhaskar
Hari Bhaskar - 11.06.2021 06:16

Excellent...well explained...thanks for this video...

Ответить
bagnon
bagnon - 11.06.2021 01:33

For looping through ranges, I like the additional Dim cell as Range method where it's for each cell in rg, next cell, etc.

Ответить
SimpleExcelVBA
SimpleExcelVBA - 10.06.2021 22:23

I wish I had such teacher at the beginning. Professional video.
Anyway, I'm a fan, like to listen to You smoothly explaining those things.

Ответить
Prasanth Jayaraman
Prasanth Jayaraman - 10.06.2021 20:33

Sir, Very nice and well explained. Thanks a lot .....

Ответить
Tom Harrington
Tom Harrington - 10.06.2021 20:03

Lbound for the low variable in array loops... I always learn something. Thank you for the lesson.

Ответить
Tattysnuc
Tattysnuc - 10.06.2021 19:55

great stuff.

You stated:
Make sure to download the source code with 20+ For Loop Examples from the description

where are they please?

Ответить
Vijay Sahal
Vijay Sahal - 10.06.2021 18:59

Very useful nd very clear your teaching way tq sir 👍🏻👍🏻👍🏻👍🏻

Ответить
Vijay Sahal
Vijay Sahal - 10.06.2021 18:36

Nice sir 👍🏻👍🏻👍🏻👍🏻

Ответить
SKarea51
SKarea51 - 10.06.2021 18:25

I thunk in the first example of nested loop you wanted to use... Cells(i, j).. Instead of Cells(i, 1)..

Ответить
John Wayne
John Wayne - 10.06.2021 18:22

Nice, as always!👍👍👍

Ответить
Excel Macro Mastery
Excel Macro Mastery - 10.06.2021 17:20

Let me know what you think about For Loops in the comments.

Make sure to download the source code with 20+ For Loop Examples from the description.

Ответить