I remember telling you that I cant understand VBA and today just because of you I am able to create logical templates using vba...Thanks Leila! I cud never have Guru like you!
Came here to get a better idea of how iteration loops work for a coursera class I am doing. My class offers written guidance, but I need to see and hear at the same time to better understand. Nevertheless, Leila did not fail as usual. Thank you!
@@LeilaGharani you are very thorough in your explanations and John C provides excellent and timely support. I haven't found that with the other 4 that I purchased from other instructors.
Excellent. Madam, your video is superb. I hope in the coming days, with some more VBA techniques. Waiting eagerly, with enthusiasm to see with new ideas/techniques/tricks, Salute to my beloved beautiful teacher.
You're welcome. I agree - they do take some practice to get the hang of. My favorite ones are the For Each collection loop and the For Next counter loop :)
Hi, I am creating an application in which I have some doubts, kindly would like to take some advices from you. Question1. In the every new record submitted by my client from user form, there will be a total of 12 cells data will be filled in each row. And out of this 12 cells 4 cells are such a type that each of these 4 cells have 6 to 10 extra rows. Now the question comes here: I want those 4 cells (including 6 to 10 extra rows) to be copies and pasted to another sheet. (Note: Every record will be searched using a unique Id(in column "D") not SL No.(in column "A") Waiting for your earliest Reply.. Regards.
i need help with nested looping to copy data from range I"m looping I want to loop down rows in a column set value in cell as a row # to copy data to then loop column in that row get Value and assign it to the row # & Column
Hi Mam When I am using the VBA code for pasting excel range in outlook mail body with signature including in the HTML body, the signature is coming to the right of the table. I need to have more line breaks after the table to get the signature at the bottom of the mail body. Please help me as the rows in the tables are not fixed to use the known number of line breaks so that signature or whatever text comes below the table, while sending multiple mails with for loop.
You are awesome as usual... Bt Leila, Could you pls help me to write a macro for datediff for values which are in range... Ex:range(A2:A10).value= different dates And calculate datediff on( ("D"),Date,(A2:A10))
so how can I know wich property i can use? Or function? I want use a command button open a formula, click on the command button on the opend formual and close it. at the first time: passed allready, I want show in different comboboxes the values = done allready. numbers-text and so on. So how can I open formula 1, then close, on the commandbutton, number 2: forumla 2 opens, no need: close formula 2 (soluted allready) the number 3 on the commandbutton: click: the content from the combobox 3 opens (and the next userform 3.). but separated NOT behind and behind and then closed? So wich function do I need for that? and second case: I want start a scroll bar at the value 0 then click on command button then the scrollbar runns 1-2-3-4 and back: -1 -2 -3 and so on as a timeline.
Can you help me Mam. The query is:: I have a range V1:V6091. I want a vba code so that each value from this range should be pasted one by one in cell B5 in same sheet. Then, each paste converts the active worksheet(Dashboard) into pdf(by name in cell B5).
Excellent I need a vba IF condition is met then copy and paste value to another sheet or workbook automatically OR IF condition is met then take screen shot and save it to any defined destination automatically
How do I modify VBA Code to execute basicly the samw way in three different columns. I am recording Break times and lunch Periods in Break Aid Spreadsheet. All activated by a dropdown box.
With regards to the for each cell loop, how do you loop vertically through the cells in a column before moving onto the next column? Excel's For Each Cell loops appears to go A1,B1,C1... can you loop A1,A2,A3....before B1,B2... ?
Hello Leila, Every Month I need to copy a range of data (e.g. D4:D32) from a file. The column is based on the month so for 1-4-2018 it is D4:D32 and for 1-5-2018 it is E4:E32 and so on.... The number of cells is always the same Then I need to paste this range into another workbook where also the column is based on the Month so e.g. 1-4-2018 have to go in range M4:M32 and 1-5-2018 is N4:N32 etc... The Months are stated in row 3 and go from 1-4-2018 till 1-5-2021. So I need a code which can copy a range of data based on the Month and also past in another workbook again based on the same Month. The number of cells are always the same, but only the row (in both files) changes depending on the Month I have selected.I would like to select the Month via combobox in a userform So I first select a Month and then I want to run the code. Can you help me with this complex case please.?
Well... since I was bored enough and I wanted to practice VBA a bit... there you go (not sure if you still need it after so long time though). Option Explicit Sub TH-cam() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim src As Worksheet, dst As Worksheet Dim srcLCol As Byte, srcColNum As Byte, dstLCol As Byte, dstColNum As Byte Dim srcLRow As Long, i As Long, j As Long, runCheck As Long Dim inputDate As String, dRow As String, pRow As String 'Select source sheet. Set src = ThisWorkbook.Worksheets("Date1") 'Select path & sheet of the destination. Set dst = Workbooks.Open("C:\Excel Sheets\youtube.xlsx").Worksheets("Date2") 'Select the row number where the date is situated (applies for both sheets) dRow = 3 ' ---------------- ' Don't edit below ' ---------------- pRow = dRow + 1 runCheck = MsgBox("Do you wish the run the macro?", vbYesNo + vbQuestion, "Security Check") If runCheck = vbYes Then inputDate = Application.InputBox("Please select a date", "Date required!", Format(Date, "dd.mm.yyyy")) If Not IsDate(inputDate) Then MsgBox "Your input is not a valid date. Try again!", vbCritical Exit Sub End If With src srcLCol = .Cells(dRow, Columns.count).End(xlToLeft).Column For i = 1 To srcLCol If inputDate "" And .Cells(dRow, i).Value = inputDate Then srcLRow = .Cells(.Rows.count, i).End(xlUp).Row srcColNum = .Cells(dRow, i).Column Exit For ElseIf i = srcLCol And srcColNum = 0 Then MsgBox "The date selected doesn't exist in the SOURCE sheet!", vbCritical src.Activate Exit Sub End If Next i .Range(.Cells(pRow, srcColNum), src.Cells(srcLRow, srcColNum)).Copy End With With dst dstLCol = .Cells(dRow, Columns.count).End(xlToLeft).Column For j = 1 To dstLCol If inputDate "" And .Cells(dRow, j).Value = inputDate Then dstColNum = dst.Cells(dRow, j).Column Exit For ElseIf j = dstLCol And dstColNum = 0 Then MsgBox "The date selected doesn't exist in the DESTINATION sheet!", vbCritical dst.Activate Exit Sub End If Next j .Range(.Cells(pRow, dstColNum), .Cells(srcLRow, dstColNum)).PasteSpecial Paste:=xlPasteValues MsgBox "Success!", vbInformation dst.Activate End With End If Application.CutCopyMode = False Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub
@@jxggxr_dxv hi jugger can you help me please? to solve my dilemma. I think I need to use the Loop formula but I'm stuck while returning the value to "0" if the cycle cuts. So I haven't used the loop but I have this formula: =IF(AND(C56>0,E52
Madam, I currently using loop macros to send multiple emails with attachment from excel sheet. I have around 300 rows, each row include different email ids, attachment paths, subjects, cc, to, etc... But amongs 300 mails i want to send only any 250 emails only But i dont know how to write program to skip rows having some "x" symbol will be set by me. I want to write program that should not sent mails from rows having "x" symbols Pls help😭
Assuming you have the "x" value in the Column A: Dim YourWs As Worksheet Dim i as Long Set YourWs = ThisWorkbook.Worksheets("Sheet1") For i = 1 to 300 If YourWs.Range("A" & i).Value "x" Then ' your code here that sends emails End if Next i
I created a form and I want to limit the user to editing, save, save as or close using quick access toolbar. how can hide ribbons on Workbook_Open event for that specific workbook? Thanks.
Please , i have this problem, my code for i = 1 to 100 if worksheets(“sheet1”).cells(i,1).value = i ----this dont work when i change i to 1 it works like this if worksheets(“sheet1”).cells(i,1).value = 1----work i try convert the cell covert the every value no way , thanks
I tried to run this code but it gives me error!!. Could you help me on that plz so this code can run: Dim Cell As Range For Each Cell In Sheet1.Range(Cells(1, 1), Cells(1, 8)) Sheet2.ComboBox1.AddItem (Cell.Value) Next Cell Note: data is in sheet1 ComboBox1 is in sheet2 the code excuted when workbook opens. when I use this code it works: Dim Cell As Range For Each Cell In Sheet1.Range("A1", "H1") Sheet2.ComboBox1.AddItem (Cell.Value) Next Cell But I want to use Sheet1.Range(Cells(1, 1), Cells(1, 8)) instead of Sheet1.Range("A1", "H1")
the only things about "Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)" it hides also the quick task bar. I want the user to be able to save, save as, print and close.
*I still don't understand* the CountFormula = CountFormula + 1 *as i understand*, what if there's 1 formula in the Range Example: Countformula = Countformula + 1 0 = 0 + 1 0 = 1 1 but there's a formula in the Range so the VBA will give us 1 + the One formula in Range so the total VBA will give us value would be 2 but the VBA gave us correct number which is 1 only because the actual count of formula is only 1 .. I don't understand, i repeat what Leila explained so many times😭😥
Get access to the full Excel VBA course here 👉 www.xelplus.com/course/excel-vba-excel-macros/
Excellent teacher, I wish I had you in my class 40 years ago. I feel so lucky that I found you.
Leila sometimes I wonder ,people like you are adding so much value to people like us. We get inspired from you. Love from India.
Thank you Himanshu for your kind comment :) I am glad the videos are adding value. That means a lot to me.
Make a video if ,and, or functions
I remember telling you that I cant understand VBA and today just because of you I am able to create logical templates using vba...Thanks Leila! I cud never have Guru like you!
Great to hear! It's a really useful tool to have at your disposal :)
You're teaching / explanation is top draw...
Very intelligent.
Hi Leila, thank you so much... can I get excel sheet with the Macro for this video. best teacher we ever have
Came here to get a better idea of how iteration loops work for a coursera class I am doing. My class offers written guidance, but I need to see and hear at the same time to better understand. Nevertheless, Leila did not fail as usual. Thank you!
Glad it was helpful :)
Thank you.
Very good explaining. I am also one of your udemy subscribers.
Thanks a lot!
@@LeilaGharani you are very thorough in your explanations and John C provides excellent and timely support. I haven't found that with the other 4 that I purchased from other instructors.
Great video, with a few more videos I can get started with what I want to do in Excel. Thank you.
Leila! Excellent videos. Enjoy the VBA videos!
Thank you! I'm glad you like them.
Excellent. Madam, your video is superb. I hope in the coming days, with some more VBA techniques. Waiting eagerly, with enthusiasm to see with new ideas/techniques/tricks, Salute to my beloved beautiful teacher.
Thank you very much Gopala - Yes I will continue to upload more VBA videos for the next few weeks :)
You are the best teacher
Good Explained !!!
Really Impress...
Great! still have a question did you ever made video on FizzBuzz activity in vba if not please include that in your videos.
I love your voice
Thanks a ton!
Loops are so difficult...
Thanks you the video!
You're welcome. I agree - they do take some practice to get the hang of. My favorite ones are the For Each collection loop and the For Next counter loop :)
Yes ma'am....i'll practice more now! 😀
Who is the psycho who put a dislike on this excellent video
Thanks for the loopy fun : )
Loopy fun :) I like that. You're welcome Mike.
Hi, Leila can you tell us how to apply same formula in two different sheets.
Someone did it before me
Anyway
Welcome back ur highness
:) Better late than never. Thank you for dropping by.
Great video, Leila!! Thanks for your lessons ^^
You're very welcome Bruno. Glad you like the lessons :)
Thanks Leila.
Hi,
I am creating an application in which I have some doubts, kindly would like to take some advices from you.
Question1. In the every new record submitted by my client from user form, there will be a total of 12 cells data will be filled in each row. And out of this 12 cells 4 cells are such a type that each of these 4 cells have 6 to 10 extra rows.
Now the question comes here: I want those 4 cells (including 6 to 10 extra rows) to be copies and pasted to another sheet.
(Note: Every record will be searched using a unique Id(in column "D") not SL No.(in column "A")
Waiting for your earliest Reply..
Regards.
thanks thanks thanks
wow thanks for htis. Quick question tho, how do you specify it so only a certain column will be checked?
Thanks leila :)
You're very welcome John :)
i need help with nested looping to copy data from range I"m looping
I want to loop down rows in a column set value in cell as a row # to copy data to then loop column in that row get Value and assign it to the row # & Column
Does the each cell the loop gets to become the Active Cell?
Hi Mam
When I am using the VBA code for pasting excel range in outlook mail body with signature including in the HTML body, the signature is coming to the right of the table. I need to have more line breaks after the table to get the signature at the bottom of the mail body.
Please help me as the rows in the tables are not fixed to use the known number of line breaks so that signature or whatever text comes below the table, while sending multiple mails with for loop.
You are awesome as usual...
Bt Leila, Could you pls help me to write a macro for datediff for values which are in range...
Ex:range(A2:A10).value= different dates
And calculate datediff on( ("D"),Date,(A2:A10))
Hi Leila, can we get cell address where formula contents another workbook? From sheets of active workbook
so how can I know wich property i can use? Or function? I want use a command button open a formula, click on the command button on the opend formual and close it. at the first time: passed allready, I want show in different comboboxes the values = done allready. numbers-text and so on. So how can I open formula 1, then close, on the commandbutton, number 2: forumla 2 opens, no need: close formula 2 (soluted allready) the number 3 on the commandbutton: click: the content from the combobox 3 opens (and the next userform 3.). but separated NOT behind and behind and then closed? So wich function do I need for that? and second case: I want start a scroll bar at the value 0 then click on command button then the scrollbar runns 1-2-3-4 and back: -1 -2 -3 and so on as a timeline.
Can you help me Mam. The query is:: I have a range V1:V6091. I want a vba code so that each value from this range should be pasted one by one in cell B5 in same sheet. Then, each paste converts the active worksheet(Dashboard) into pdf(by name in cell B5).
Excellent
I need a vba
IF condition is met then copy and paste value to another sheet or workbook automatically
OR
IF condition is met then take screen shot and save it to any defined destination automatically
Good
How do I modify VBA Code to execute basicly the samw way in three different columns. I am recording Break times and lunch Periods in Break Aid Spreadsheet. All activated by a dropdown box.
With regards to the for each cell loop, how do you loop vertically through the cells in a column before moving onto the next column? Excel's For Each Cell loops appears to go A1,B1,C1... can you loop A1,A2,A3....before B1,B2... ?
Hello Leila,
Every Month I need to copy a range of data
(e.g. D4:D32) from a file. The column is based on the month so for 1-4-2018 it is D4:D32
and for 1-5-2018 it is E4:E32 and so on.... The number of cells is always the same
Then I need to paste this range into another workbook where also
the column is based on the Month so e.g. 1-4-2018 have to go in range M4:M32 and 1-5-2018 is
N4:N32 etc... The Months are stated in row 3 and go from 1-4-2018 till 1-5-2021. So I need a code
which can copy a range of data based on the Month and also past in another
workbook again based on the same Month. The number of cells are always the same, but only the row (in
both files) changes depending on the Month I have selected.I would like to select the Month via combobox in a userform So I first select a Month and then I want to run the code. Can
you help me with this complex case please.?
Well... since I was bored enough and I wanted to practice VBA a bit... there you go (not sure if you still need it after so long time though).
Option Explicit
Sub TH-cam()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim src As Worksheet, dst As Worksheet
Dim srcLCol As Byte, srcColNum As Byte, dstLCol As Byte, dstColNum As Byte
Dim srcLRow As Long, i As Long, j As Long, runCheck As Long
Dim inputDate As String, dRow As String, pRow As String
'Select source sheet.
Set src = ThisWorkbook.Worksheets("Date1")
'Select path & sheet of the destination.
Set dst = Workbooks.Open("C:\Excel Sheets\youtube.xlsx").Worksheets("Date2")
'Select the row number where the date is situated (applies for both sheets)
dRow = 3
' ----------------
' Don't edit below
' ----------------
pRow = dRow + 1
runCheck = MsgBox("Do you wish the run the macro?", vbYesNo + vbQuestion, "Security Check")
If runCheck = vbYes Then
inputDate = Application.InputBox("Please select a date", "Date required!", Format(Date, "dd.mm.yyyy"))
If Not IsDate(inputDate) Then
MsgBox "Your input is not a valid date. Try again!", vbCritical
Exit Sub
End If
With src
srcLCol = .Cells(dRow, Columns.count).End(xlToLeft).Column
For i = 1 To srcLCol
If inputDate "" And .Cells(dRow, i).Value = inputDate Then
srcLRow = .Cells(.Rows.count, i).End(xlUp).Row
srcColNum = .Cells(dRow, i).Column
Exit For
ElseIf i = srcLCol And srcColNum = 0 Then
MsgBox "The date selected doesn't exist in the SOURCE sheet!", vbCritical
src.Activate
Exit Sub
End If
Next i
.Range(.Cells(pRow, srcColNum), src.Cells(srcLRow, srcColNum)).Copy
End With
With dst
dstLCol = .Cells(dRow, Columns.count).End(xlToLeft).Column
For j = 1 To dstLCol
If inputDate "" And .Cells(dRow, j).Value = inputDate Then
dstColNum = dst.Cells(dRow, j).Column
Exit For
ElseIf j = dstLCol And dstColNum = 0 Then
MsgBox "The date selected doesn't exist in the DESTINATION sheet!", vbCritical
dst.Activate
Exit Sub
End If
Next j
.Range(.Cells(pRow, dstColNum), .Cells(srcLRow, dstColNum)).PasteSpecial Paste:=xlPasteValues
MsgBox "Success!", vbInformation
dst.Activate
End With
End If
Application.CutCopyMode = False
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
@@jxggxr_dxv hi jugger can you help me please? to solve my dilemma.
I think I need to use the Loop formula but I'm stuck while returning the value to "0" if the cycle cuts.
So I haven't used the loop but I have this formula:
=IF(AND(C56>0,E52
Madam,
I currently using loop macros to send multiple emails with attachment from excel sheet.
I have around 300 rows, each row include different email ids, attachment paths, subjects, cc, to, etc...
But amongs 300 mails i want to send only any 250 emails only
But i dont know how to write program to skip rows having some "x" symbol will be set by me.
I want to write program that should not sent mails from rows having "x" symbols
Pls help😭
Assuming you have the "x" value in the Column A:
Dim YourWs As Worksheet
Dim i as Long
Set YourWs = ThisWorkbook.Worksheets("Sheet1")
For i = 1 to 300
If YourWs.Range("A" & i).Value "x" Then
' your code here that sends emails
End if
Next i
I created a form and I want to limit the user to editing, save, save as or close using quick access toolbar. how can hide ribbons on Workbook_Open event for that specific workbook? Thanks.
Hi - maybe this link helps: bit.ly/2I8Qc4R
Please , i have this problem, my code
for i = 1 to 100
if worksheets(“sheet1”).cells(i,1).value = i ----this dont work
when i change i to 1 it works like this
if worksheets(“sheet1”).cells(i,1).value = 1----work
i try convert the cell covert the every value no way , thanks
I tried to run this code but it gives me error!!. Could you help me on that plz so this code can run:
Dim Cell As Range
For Each Cell In Sheet1.Range(Cells(1, 1), Cells(1, 8))
Sheet2.ComboBox1.AddItem (Cell.Value)
Next Cell
Note:
data is in sheet1
ComboBox1 is in sheet2
the code excuted when workbook opens.
when I use this code it works:
Dim Cell As Range
For Each Cell In Sheet1.Range("A1", "H1")
Sheet2.ComboBox1.AddItem (Cell.Value)
Next Cell
But I want to use Sheet1.Range(Cells(1, 1), Cells(1, 8)) instead of Sheet1.Range("A1", "H1")
Hi - the object (parent) of the cells is missing. Try this:
Sheet1.Range(Sheet1.Cells(1, 1), Sheet1.Cells(1, 8))
Leila Gharani Thank you so much. It’s Okay now.
the only things about "Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)" it hides also the quick task bar. I want the user to be able to save, save as, print and close.
How do we reference a specific worksheet?
Dim x As Worksheet - Set x = Sheets(" ") - x.Select
[B6] = CountFormula ?
*I still don't understand* the
CountFormula = CountFormula + 1
*as i understand*, what if there's 1 formula in the Range
Example:
Countformula = Countformula + 1
0 = 0 + 1
0 = 1
1
but there's a formula in the Range so the VBA will give us 1 + the One formula in Range so the total VBA will give us value would be 2
but the VBA gave us correct number which is 1 only because the actual count of formula is only 1 ..
I don't understand, i repeat what Leila explained so many times😭😥
You are too much beautiful....
I love you
I think this was a bad example, very complex