Thank you for these tutorials, I have made a lot of fancy looking tools using excel with vba and I learned everything from you. I just came across one of your videos and I subscribed after that. Thank you for explaining everything so clearly that a person without any programming background can understand it 😀
I have also a Excel tutorial channel ... But You are very senior in this field and i appreciate you sir to make videos in Hindi language for our indian peoples. Lots of things i have been learning from your channel.
Instead of writing Range("C5:I9") a range variable can be created to adjust it's size dynamically as number of rows and/or columns increases. This way all the adjacent nonblank cells (i.e. cells with value) will be accounted to form the range. Dim MyTable As Range Set MyTable = Range("C5").CurrentRegion ' This would return the range "C5:I9" in the video example This has more advantages and makes life a lot easy. For example, instead of defining absolute addresses of cells, relative referencing is possible. The simple example below demonstrates that: Sub DemoCurrentRegion() Dim MyTable As Range Dim r As Long, c As Long
Set MyTable = Range("C5").CurrentRegion Debug.Print MyTable.Address, MyTable.Address(0, 0), vbCrLf
For r = 2 To MyTable.Rows.Count For c = 2 To MyTable.Columns.Count With MyTable.Cells(r, c) Debug.Print .Address & " -> " & .Value End With Next c Next r End Sub Thank you Sir.
You are welcome Sir. Quite often we may want to get a trimmed part of the current region… i.e. eliminate few rows at top/bottom, few columns at left/right of the current region. To help doing so with a single line of code I wrote the following function ‘GetRegion()’ and have presented that with an example below. This function uses 2 very lucrative methods: Range.Offset() and Range.Resize() Private Sub TestFunc() ' CurrentRegion without top 2 rows and the bottom row Debug.Print GetRegion(Sheet1.Range("A1"), 2, 1).Address End Sub ' CurrentRegion optionally trimming 1 or 1+ rows/columns Public Function GetRegion(ByVal rg As Range, _ Optional TrimTopRows As Long = 0, _ Optional TrimBotRows As Long = 0, _ Optional TrimLftCols As Long = 0, _ Optional TrimRgtCols As Long = 0) As Range
Set rg = rg.CurrentRegion
If TrimTopRows 0 Then Set rg = rg.Offset(TrimTopRows).Resize(rg.Rows.Count - TrimTopRows) If TrimBotRows 0 Then Set rg = rg.Resize(rg.Rows.Count - TrimBotRows) If TrimLftCols 0 Then Set rg = rg.Offset(ColumnOffset:=TrimLftCols).Resize(ColumnSize:=rg.Columns.Count - TrimLftCols) If TrimRgtCols 0 Then Set rg = rg.Resize(ColumnSize:=rg.Columns.Count - TrimRgtCols)
Sir, thanks for this video. If few data in the table are in duplicate, what changes required in the formula. Further, if we autofill the formula in rows i.e. for Jacob, william etc. would it work?
Search this channel or our website: www.exceltrainingvideos.com/ These links will help: www.exceltrainingvideos.com/search-multiple-values-in-worksheet-data/ www.exceltrainingvideos.com/automatically-search-excel-data-display-print-using-vba/ www.exceltrainingvideos.com/how-to-add-data-in-non-contiguous-cells-with-vba/ www.exceltrainingvideos.com/calculating-sum-of-sales-during-specific-period-in-excel/
Hello, I would like to ask you if you would explain how I can add a button that takes me to a screen where it lets me select an Excel file and it will import some data from that file to the one where I added the button. Best regards José
could you assign an end of file character in the last column, like * or |, and then your code would be flexible as your array expands and contracts as needed?
Hello Sir, Nice Video. I have a query. Say if I had to import the live stock price of Infosys to cell B2 of excel and upon each refresh, the new value should be updated in the next cell say B3, B4, B5 .....and so on, instead of erasing and updating the new value in cell B2 itself. so upon each refresh it should leave the previous trail of prices as it is and should be updated in the next cell. I did it 10 yrs ago with Reset, count and Iterate options but do not remember it now. May I request if you would be able to guide me on this? Thanks in advance. Regards, Senthil
This VBA solution will help: www.exceltrainingvideos.com/using-vlookup-with-indirect-function-in-vba/ You can also search this TH-cam channel or our website www.exceltrainingvideos.com
Sir is it possible to have cell C3 where you have Olivia a drop down menu to select names instead of typing it and still work with the same visual basic formula you Made?
How can I display in any random cell. what is the nearest date existing in column "D" ( bunch of dates ) since same line of nearest date has the word '"Open" in A column (bunch of open and closed lines ) by showing also the adjacent B and C value of newest date line? pls
You should choose a preferred size and declare that as a constant. Alternatively you can create a custom function using something like so: infinityt=1/0
hello sir.. 👍 excellent clips. can u plz do it by taking multiple search inputs in different workbook. suppose i have different country names in workbook 1 & i want to lookup same values in workbook 2 & past it in beside the respective value of workbook 1 .. plzzz
Hi sir! Im confused on how did you short cut thus, that after typing "dim r as long" suddenly appear the rest of the code. I am new in micro application. Please help, thanks!!!
hello sir mai poochna chahata hu ki jab vlookup mai kisi deta ko sarch kare to jis jis column mai vlookup formula apply ho raha hai un sabi column mai colour kese apply ho vlookup ke sath
sir, please create video on my below query : How to create browse button in excel, and open browse path and copy specific header's data in one excel, without open browse file(excel). also it is possible to loop.
Use dependent lists. This link will help: www.exceltrainingvideos.com/tag/create-dependent-combo-boxes-in-excel-user-form-with-vba/ Or search my website www.exceltrainingvideos.com or this TH-cam channel.
Sir, Can you tell if one worksheet has all sells list what you mentioned and if we do that same in other worksheet how to do it, how to do it. All are only showing one name data if we put name all name and how to get data automatically
Hi Sir i need your help. how can i pull the raw data to a clean sheet i have this raw data that have some spaces and it can't vlookup because there are spaces i dont want to delete those spaces because this raw data, can be change from time to time.
yes sir.. if its ok i will send you the sample raw data.. not to mention this data is from a notepad that i put in excel. and i want this data to be clean in a new sheet.. to be exact i just want to have a formula that will pull out the data into a clean sheet. so that if i put another raw data i will have a clean data.. but the only problem is i cannot pullout because there are some spaces in the raw data..
Hi sir I need one help I need to prepare one report that I have from 2007 patient visit....i need report of patient didn't visit after 2011...... Pls help me which formulaa i will use sir
Sir Please Help Me. That In my UserForm I have put three TextBox1, TextBox2 & TextBox3. TextBox1 = CurrentDate, TextBox2 = 30 Days and Now TextBox3 calculate future date from TextBox1 + TextBox2 ........
sir based on your trasnfer specific data to specific sheet which is vertically placed i tried to transfer horizontal data to horizontal i tired this code but some thing is wrong please help me on this please please
sir i did with code which is availble from your website i got success in transferring only in sheet 1 to sheet2(Horizontally placed data to horizontal way) i have send the file 2 your email id t********esh@gmail.com,dr******@familycomputerclub.com
This link will help: www.exceltrainingvideos.com/search-multiple-values-in-worksheet-data/ Or search my website www.exceltrainingvideos.com or TH-cam channel.
Hello sir im new subscriber here Can you please tell me From which video should i start because im a beginner Please please or show me any playlist so that i can learn please
Our data is in sheet1. We create our macro as below: Sub findData() Dim cell As Range Dim myString As String For Each cell In Worksheets("Sheet1").Range("A1:B20").Cells If cell.Value = 456 Or cell.Text = "456" Then MsgBox "The cell address of this value in sheet 1 is " & cell.Address Exit Sub End If Next cell End Sub Attache this macro to a button in sheet2.
Dear any body help me find multiple result of column b of value of column a with the help of vlookup or any other way for example in column a there are reapted name of a and in column b there are value now in the help of vlookup I can got first value, now I want to get another value also
HI GUYS AND LADIES ONE QUESTION I HAVE TWO TABLES AS SHOWN 1 2 3 5 1 2 3 4 5 ..... LEFT SIDE 4 COLUMNS RIGHT TABLE 5 COLUMNS THOUSANDS OF ROWS I WANT TO FIND IN RIGHT TABLE HOW MANY TIME AND WHERE IS EXACT MACHT OF LEFT TABLE IN ALL ROWS WHICH FUNCTION TO USE PLEASE AND HOW THANK YOU
Couldn't you just do a simple VLOOKUP and copy across without any macros? It's totally doable, like this: =VLOOKUP($C$3,$C$5:$I$9,COLUMN()-2,0) and just copy across? No need for macro nor button...and it's much shorter.
I was searching the TH-cam for this only.. and finally ended up with this excellent explanatory tutorial.. Thanks
Thank you for these tutorials, I have made a lot of fancy looking tools using excel with vba and I learned everything from you. I just came across one of your videos and I subscribed after that. Thank you for explaining everything so clearly that a person without any programming background can understand it 😀
Dear Sir, Your teaching is so amazing that any one can understand the great and difficult things easily. I am so happy with your teachings.
My teacher, thank you very much for this great video and for this valuable informations and for this simple, clear and wonderful method.
Thank you for the feedback.
I have also a Excel tutorial channel ... But You are very senior in this field and i appreciate you sir to make videos in Hindi language for our indian peoples. Lots of things i have been learning from your channel.
th-cam.com/users/MSExcelinHindi
Watch at 1.25X speed and thank me later. :)
lol thnks
thanks a lot
Thank you for your good Vedio Mr.Kumar and we still learned from you an exciting vedios Pls go ahead for more vedios.
Great teaching Thank you for sharing
Instead of writing Range("C5:I9") a range variable can be created to adjust it's size dynamically as number of rows and/or columns increases. This way all the adjacent nonblank cells (i.e. cells with value) will be accounted to form the range.
Dim MyTable As Range
Set MyTable = Range("C5").CurrentRegion ' This would return the range "C5:I9" in the video example
This has more advantages and makes life a lot easy. For example, instead of defining absolute addresses of cells, relative referencing is possible. The simple example below demonstrates that:
Sub DemoCurrentRegion()
Dim MyTable As Range
Dim r As Long, c As Long
Set MyTable = Range("C5").CurrentRegion
Debug.Print MyTable.Address, MyTable.Address(0, 0), vbCrLf
For r = 2 To MyTable.Rows.Count
For c = 2 To MyTable.Columns.Count
With MyTable.Cells(r, c)
Debug.Print .Address & " -> " & .Value
End With
Next c
Next r
End Sub
Thank you Sir.
Lovely. Thanks for sharing!
You are welcome Sir.
Quite often we may want to get a trimmed part of the current region… i.e. eliminate few rows at top/bottom, few columns at left/right of the current region. To help doing so with a single line of code I wrote the following function ‘GetRegion()’ and have presented that with an example below. This function uses 2 very lucrative methods: Range.Offset() and Range.Resize()
Private Sub TestFunc()
' CurrentRegion without top 2 rows and the bottom row
Debug.Print GetRegion(Sheet1.Range("A1"), 2, 1).Address
End Sub
' CurrentRegion optionally trimming 1 or 1+ rows/columns
Public Function GetRegion(ByVal rg As Range, _
Optional TrimTopRows As Long = 0, _
Optional TrimBotRows As Long = 0, _
Optional TrimLftCols As Long = 0, _
Optional TrimRgtCols As Long = 0) As Range
Set rg = rg.CurrentRegion
If TrimTopRows 0 Then Set rg = rg.Offset(TrimTopRows).Resize(rg.Rows.Count - TrimTopRows)
If TrimBotRows 0 Then Set rg = rg.Resize(rg.Rows.Count - TrimBotRows)
If TrimLftCols 0 Then Set rg = rg.Offset(ColumnOffset:=TrimLftCols).Resize(ColumnSize:=rg.Columns.Count - TrimLftCols)
If TrimRgtCols 0 Then Set rg = rg.Resize(ColumnSize:=rg.Columns.Count - TrimRgtCols)
Set GetRegion = rg
End Function
Thank you Sir.
Thanks for putting this up. Very helpful.
Outstanding trick air
Glad it helped. Please share with your friends.
Very nice sir
Please share with your friends. Thanks.
Thank you Sir. This video is very use ful
Great sir, very much useful code
Thank you. I'm going to use this useful vba code to obstract data from our employees rostered shifts.
Great video...thank you
Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
Really Good, Thank You
Glad you liked it! Please share the Excel tutorial with your friends too.
Really its a good video and Sir please make a brief video on VBA...
This is a Great video sir, thank you so much for this.
Thanks a lot sir... Great video nice
That is a great video, Thanks sir.
Thank you your kindness.
Good video
This video is very useful for me.
Thanks sir
Sir, thanks for this video. If few data in the table are in duplicate, what changes required in the formula. Further, if we autofill the formula in rows i.e. for Jacob, william etc. would it work?
nice thanks 😊
Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
Dear Sir, thanks to this video. Could you please show Vlookup multiple value from different worksheet. Thanks
Yes, sure
@@Exceltrainingvideos Could you please show Vlookup multiple value from DIFFERENT FILES, i.e file (A) lookup in file (B), Thanks
Hi Sir, Very excellent explain with thank you!!
I need one formula, two multiple colum text & numbers,how can i find & sum the value?
Search this channel or our website: www.exceltrainingvideos.com/
These links will help:
www.exceltrainingvideos.com/search-multiple-values-in-worksheet-data/
www.exceltrainingvideos.com/automatically-search-excel-data-display-print-using-vba/
www.exceltrainingvideos.com/how-to-add-data-in-non-contiguous-cells-with-vba/
www.exceltrainingvideos.com/calculating-sum-of-sales-during-specific-period-in-excel/
Hello, I would like to ask you if you would explain how I can add a button that takes me to a screen where it lets me select an Excel file and it will import some data from that file to the one where I added the button.
Best regards
José
could you assign an end of file character in the last column, like * or |, and then your code would be flexible as your array expands and contracts as needed?
👏🏽👏🏽👏🏽. Thank you.
Hello Sir,
Nice Video.
I have a query. Say if I had to import the live stock price of Infosys to cell B2 of excel and upon each refresh, the new value should be updated in the next cell say B3, B4, B5 .....and so on, instead of erasing and updating the new value in cell B2 itself. so upon each refresh it should leave the previous trail of prices as it is and should be updated in the next cell. I did it 10 yrs ago with Reset, count and Iterate options but do not remember it now. May I request if you would be able to guide me on this?
Thanks in advance.
Regards,
Senthil
Thanks for the video sir..how to lookup multiple values when the source data lies in a different sheet.. Kindly let me know
This VBA solution will help: www.exceltrainingvideos.com/using-vlookup-with-indirect-function-in-vba/
You can also search this TH-cam channel or our website www.exceltrainingvideos.com
Sir is it possible to have cell C3 where you have Olivia a drop down menu to select names instead of typing it and still work with the same visual basic formula you Made?
Many thanks sir
How can I display in any random cell. what is the nearest date existing in column "D" ( bunch of dates ) since same line of nearest date has the word '"Open" in A column (bunch of open and closed lines ) by showing also the adjacent B and C value of newest date line? pls
What happens if i have an ongoing list of data, is there a way to set the table array to infinity?
You should choose a preferred size and declare that as a constant. Alternatively you can create a custom function using something like so: infinityt=1/0
thanks great sir
Sir, I need a help for if i have some multiple values in cell how get the answer a multiple values by vlookup (Like 12,12,13,14,13,14, in a cell
hello, do you know a formula equivalent to CTRL + F? thanks
Try INDEX & MATCH.
hello sir.. 👍 excellent clips.
can u plz do it by taking multiple search inputs in different workbook.
suppose i have different country names in workbook 1 & i want to lookup same values in workbook 2 & past it in beside the respective value of workbook 1 .. plzzz
Please search my channel, playlists or website www.exceltrainingvideos.com for a solution.
Dinesh Kumar Takyar can u plz send me link fr the above query
Hi sir! Im confused on how did you short cut thus, that after typing "dim r as long" suddenly appear the rest of the code. I am new in micro application.
Please help, thanks!!!
This great sir but if value is located in closed workbook,how we can find
Hi Sir,
Please help me to get the above data for multiple rows
hello sir
mai poochna chahata hu ki jab vlookup mai kisi deta ko sarch kare to jis jis column mai vlookup formula apply ho raha hai un sabi column mai colour kese apply ho vlookup ke sath
sir, please create video on my below query :
How to create browse button in excel, and open browse path and copy specific header's data in one excel, without open browse file(excel). also it is possible to loop.
Himanshu Dave .. Hello himanshu yes it's possible I can do it for u...
How can I get a different part number to bring up the part number that I have in excel?
Use dependent lists. This link will help: www.exceltrainingvideos.com/tag/create-dependent-combo-boxes-in-excel-user-form-with-vba/
Or search my website www.exceltrainingvideos.com or this TH-cam channel.
Sir, how to use vba code to get multiple values same as this video but from another workbook ?
i am new to vba.. pls help
Sir, Can you tell if one worksheet has all sells list what you mentioned and if we do that same in other worksheet how to do it, how to do it. All are only showing one name data if we put name all name and how to get data automatically
This link will help: www.exceltrainingvideos.com/how-to-use-vlookup-to-get-multiple-values/
Hi Sir i need your help. how can i pull the raw data to a clean sheet
i have this raw data that have some spaces and it can't vlookup because there are spaces
i dont want to delete those spaces because this raw data, can be change from time to time.
You mean to say we cannot do a Vlookup if there are spaces, let's say, in a name?
yes sir.. if its ok i will send you the sample raw data.. not to mention this data is from a notepad that i put in excel. and i want this data to be clean in a new sheet.. to be exact i just want to have a formula that will pull out the data into a clean sheet. so that if i put another raw data i will have a clean data.. but the only problem is i cannot pullout because there are some spaces in the raw data..
How to create time table or daily routine of a school. there are 45 teacher and 27 classes.
Please help me.
Hi sir ,
I need help I am struck after first line of code writing ...
I can not understand from where the all values comes up suddenly
Check out this link: www.exceltrainingvideos.com/how-to-use-vlookup-to-get-multiple-values/
Hi sir
I need one help
I need to prepare one report that
I have from 2007 patient visit....i need report of patient didn't visit after 2011......
Pls help me which formulaa i will use sir
Use if function
Sir Please Help Me. That In my UserForm I have put three TextBox1, TextBox2 & TextBox3. TextBox1 = CurrentDate,
TextBox2 = 30 Days and Now TextBox3 calculate future date from TextBox1 + TextBox2 ........
What have tried? Search www.exceltrainingvideos.com
How can I read grouped of rows in excel using VBA
You can use the following IF condition in a looping process:
If Cells(i, 1).Rows.OutlineLevel > 1 Then
can you make a video on this topic please?
Sir i have 3 vlookup function 2 lookup function are working properly but 3 lookup showing 1004 error..
Check your code.
Sir, how to get multiple rows with multiple columns
sir based on your trasnfer specific data to specific sheet which is vertically placed i tried to transfer horizontal data to horizontal i tired this code but some thing is wrong please help me on this please please
What are your sheet names?
SIR, I HAVE GIVEN A,B,C,D,E AS YOU HAVE SHOWN IN THE VIDEO IF I AM WRONG PLEASE GUIDE ME SIR
SIR I HAVE SENT A MAIL WITH THE FILE SIR I FOUND ADDRESS ON FAMILYCOMPUTERCLUB.COM AND GOOGLE+
The best way to try something new is to try my example first - step by step.
sir i did with code which is availble from your website i got success in transferring only in sheet 1 to sheet2(Horizontally placed data to horizontal way) i have send the file 2 your email id t********esh@gmail.com,dr******@familycomputerclub.com
We actually made this tool at EasyExcelAutomation.com that combines hundreds of excel files into one in seconds.
is there any other way without using vba. this is very time consuming?
use vlookup with match
You need to write the code once!
how to make this in another sheet not in the sheet its self
This link will help: www.exceltrainingvideos.com/search-multiple-values-in-worksheet-data/
Or search my website www.exceltrainingvideos.com or TH-cam channel.
thanks alot sir, I want the same table but in another sheet not in the sheet its self. just to get me multiple values
like copy data from sheet to another but entering one cell like you did in the video you entire the Name and press button to get us multiple values
Sir, can u please explain how can we learn VBA.....
Have a data in range of two and three digits...have to count how many numbers have two digit..by formula
Use a looping process.
Give an example of use of such counting.
Hello sir im new subscriber here
Can you please tell me
From which video should i start because im a beginner
Please please or show me any playlist so that i can learn please
www.exceltrainingvideos.com/excel-vba/excel-2003-vba/
Sir, how to lookup multiple column with multiple row by vloockup.
Share an example.
Sir pls help me....how can I get address of value which is anywhere in a workbook.....pls write a vba code for it....
Our data is in sheet1. We create our macro as below:
Sub findData()
Dim cell As Range
Dim myString As String
For Each cell In Worksheets("Sheet1").Range("A1:B20").Cells
If cell.Value = 456 Or cell.Text = "456" Then
MsgBox "The cell address of this value in sheet 1 is " & cell.Address
Exit Sub
End If
Next cell
End Sub
Attache this macro to a button in sheet2.
Dinesh Kumar Takyar sir can I contact with you sir my no is 9899807961
Watch in 2x playback speed, thank me later.
These videos are made for learning and most people prefer this style.
Dear any body help me find multiple result of column b of value of column a with the help of vlookup or any other way for example in column a there are reapted name of a and in column b there are value now in the help of vlookup I can got first value, now I want to get another value also
Use find FindNext: www.exceltrainingvideos.com/tag/findnext-method-vba/
Or, th-cam.com/video/I7Is4d1CZfQ/w-d-xo.html
sir app graphics represent kaise karate usake bare me bhi bataye
Search www.exceltrainingvideos.com
HI GUYS AND LADIES
ONE QUESTION
I HAVE TWO TABLES
AS SHOWN
1 2 3 5 1 2 3 4 5
.....
LEFT SIDE 4 COLUMNS RIGHT TABLE 5 COLUMNS
THOUSANDS OF ROWS
I WANT TO FIND IN RIGHT TABLE HOW MANY TIME AND WHERE IS EXACT MACHT OF LEFT TABLE
IN ALL ROWS
WHICH FUNCTION TO USE PLEASE AND HOW
THANK YOU
What have you tried?
You can search: www.exceltrainingvideos.com/ for many solutions.
excuse me sir, Can you help me for previous comment
Couldn't you just do a simple VLOOKUP and copy across without any macros? It's totally doable, like this: =VLOOKUP($C$3,$C$5:$I$9,COLUMN()-2,0) and just copy across? No need for macro nor button...and it's much shorter.
Please share the VBA Code
www.exceltrainingvideos.com/how-to-use-vlookup-to-get-multiple-values/
It's a good idea to read the description accompanying the video.