Learn Excel VBA Training from the Best Tutors
Search in
Answered on 20/06/2017 Learn Excel VBA Training
Altaf K.
Microsoft Certified Trainer
Lesson Posted on 31/05/2017 Learn Excel VBA Training
My Experience on Recruitment for Data Analyst role.
Sai Krishna
I have seven years of experience in IT and teaching field in Excel, VBA, SQL Server and Qlikview. I can...
Hi All,
I want to share my experience here and why I chose to take up classes on Advanced excel. This is not technical lesson but a motivation to improve your skills.
I was leading Data Analyst team in a startup company. We had to recruit four data analyst with Advanced Excel, VBA and any database knowledge.
We got around 45 - 70 profiles shortlisted for interview out of 100s of profiles from HR. We were shocked that candidates were not able to answer any Advanced Excel questions who have work experience in Excel for 3 to 4 years. Candidates with Advanced Excel knowledge are lacking VBA knowledge. When they have both of these, database was problem for them.
If you are working in Excel for atleast one year then you must have all related knowledge to get good salary. Advanced Excel ~ VBA ~ Database (Access/SQL Server) are good combination skills to survive in IT industry for atleast 10 years.
Regards,
Sai Krishna.
Lesson Posted on 24/05/2017 Learn Excel VBA Training
Excel Tip: VLOOKUP formula limitations
Karthik Dale
II have 8 years of Data Analytics Experience. I have extensive experience in wide range of Excel Automation...
1. Vlookup is not case-sensitive
2. If Lookup value has duplicates in the lookup column, then it returns 1st found value
3. Vlookup cannot pull values which are on the left side from Lookup column
4. Vlookup gives inaccurate output if the Lookup table range is huge.
read lessLearn Excel VBA Training from the Best Tutors
Lesson Posted on 17/05/2017 Learn Excel VBA Training
Excel Tip 1: VLOOKUP to pull left column values
Karthik Dale
II have 8 years of Data Analytics Experience. I have extensive experience in wide range of Excel Automation...
Hi,
Most of us know that using Vlookup function we can get the right column values, however by using Choose function in Vlookup table array, we can pull left column values as well. In the below screenshot lookup value column is not left most even though Vlookup is able to get the Product Name or Branch details
Note1: Choose function 1st argument contains flower brackets
Note2: If you change the column index number to 3 then it will populate branch details.
Hope it's a helpful tip. Will catch you in my next tip
read lessLesson Posted on 05/05/2017 Learn Excel VBA Training
Karthik Dale
II have 8 years of Data Analytics Experience. I have extensive experience in wide range of Excel Automation...
RIGHT function in VBA returns the Specified number of characters from the end of a text string
Syntax: Right(String,Length)
Sub RIGHT_1()
Dim mystring As String
mystring = "Narendra Modi"
MsgBox Right(mystring, 4)
'Output: "Modi"
'To get first name dynamically use RIGHT & InStr functions
MsgBox Right(mystring, Len(mystring) - InStr(1, mystring, " "))
'Output: "Modi"
End Sub
read lessLesson Posted on 02/05/2017 Learn Excel VBA Training
Formula to Populate Weekdays of a Given Month
Karthik Dale
II have 8 years of Data Analytics Experience. I have extensive experience in wide range of Excel Automation...
If you want to populate weekdays (Monday to Friday) in a range, follow below steps.
Step1: In Cell A1 write any month 1st date. Let’s say 02/01/2017 (Feb 1st 2017)
Step2: In Cell C1 write this formula =WORKDAY.INTL($A$1,IF(OR(WEEKDAY($A$1)=1,WEEKDAY($A$1)=7),ROW(A1),ROW(A1)-1),1)
Step3: Drag Cell C1 formula down…Check the below screenshot
read lessLearn Excel VBA Training from the Best Tutors
Lesson Posted on 26/04/2017 Learn Excel VBA Training
Karthik Dale
II have 8 years of Data Analytics Experience. I have extensive experience in wide range of Excel Automation...
INSTR works similar to FIND function in Excel. INSTR search for specific text in a given text and returns the position number of what you are searching for.
Syntax: InStr( [start_num], string, searchingfor, [compare] )
Sub INSTR_1()
MsgBox InStr(1, "Narendra Modi", " ")
'Output: 9
MsgBox InStr(1, "Narendra Modi", "Modi")
'Output: 10
MsgBox InStr(1, "Narendra Modi", "MODI")
'Output: 0
'To find the 2nd "e" postion number in "excel is awesome"
MsgBox InStr(InStr(1, "excel is awesome", "e") + 1, "excel is awesome", "e")
'Output:4
'Note: INSTR function is a case-sensitive and if it's not found the value then output will be 0
End Sub
read lessAsked on 22/04/2017 Learn Excel VBA Training
Lesson Posted on 13/04/2017 Learn Excel VBA Training
Punit kumar
I have a total of six years plus of work experience.I have worked on multiple projects of different sizes....
In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1900. For example, if you enter July 5, 1998, Excel converts the date to the serial number 35981.
By default, Microsoft Excel for Windows uses the 1900 date system. The 1900 date system enables better compatibility between Excel and other spreadsheet programs, such as Lotus 1-2-3, that are designed to run under MS-DOS or Microsoft Windows.
In the 1904 date system, the first day that is supported is January 1, 1904. When you enter a date, the date is converted into a serial number that represents the number of elapsed days since January 1, 1904. For example, if you enter July 5, 1998, Excel converts the date to the serial number 34519.
By default, Microsoft Excel for the Macintosh uses the 1904 date system. Because of the design of early Macintosh computers, dates before January 1, 1904 were not supported. This design was intended to prevent problems related to the fact that 1900 was not a leap year. If you switch to the 1900 date system, Excel for the Macintosh does support dates as early as January 1, 1900.
Because the two date systems use different starting days, the same date is represented by different serial numbers in each date system. For example, July 5, 1998 can have two different serial numbers, as follows.
Serial number
Date system of July 5, 1998
----------------------------------
1900 date system 35981
1904 date system 34519
The difference between the two date systems is 1,462 days; that is, the serial number of a date in the 1900 Date System is always 1,462 days bigger than the serial number of the same date in the 1904 date system. 1,462 days is equal to four years and one day (including one leap day).
How to handle through VBA
Dim lngRow as long
If this workbook.Date1904 = True Then
For lngRow = 2 To sheet("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
if isdate(sheet("Sheet1")) then
sheet("sheet1").Cells(lngRow, iCol).Value = sheet("sheet1").Cells(lngRow, iCol).Value + 1462
end if
Next lngRow
End if
read less
Learn Excel VBA Training from the Best Tutors
Lesson Posted on 23/03/2017 Learn Excel VBA Training
Excelytics
Excelytics was established by Microsoft Certified Professionals. Excelytics is a dedicated training...
Using FORMAT Function in VBA we can modify the format of Numbers, Dates, Times & String.
Syntax: FORMAT(expression, format, [firstdayofweek], [firstweekofyear])
1. Formatting "Numbers" using FORMAT function in VBA
SUB Format_Numbers()
MsgBox format(8686836999, "####-###-###")
'Output: '8686-836-999'
MsgBox format(143.5, "#,##.00")
'Output: '143.50'
MsgBox format(1729.4, "Currency")
'Output: '$1,729.40'
MsgBox format(0.9156, "Percent")
'Output: '91.56%'
MsgBox format(1729.4, "Standard")
'Output: '1,729.40'
End Sub
2. Formatting "Dates" using FORMAT function in VBA
Sub FORMAT_Dates()
MsgBox format("3/21/2017", "Short Date")
'Output: '3/21/2017'
MsgBox format("3/21/2017", "Medium Date")
'Output: '21-Mar-17'
MsgBox format("3/21/2017", "Long Date")
'Output: 'Tuesday, March 21, 2017'
MsgBox format("3/21/2017", "mmm dd")
'Output: 'Mar 21'
End Sub
3. Formatting "Time" using FORMAT function in VBA
Sub FORMAT_Time()
MsgBox format("6:30:45", "Short Time")
'Output: '06:30'
MsgBox format("6:30:45", "LONG Time")
'Output: '6:30:45 AM'
MsgBox format("6:30:45", "MEDIUM Time")
'Output: '06:30 AM'
MsgBox format("14:30:45", "HH:MM")
'Output: '14:30'
End Sub
4. Formatting "Text" using FORMAT function in VBA
Sub FORMAT_Text()
MsgBox format("EXCEL IS MAGIC", "<")
'Output: 'excel is magic' (convert text to lower case)
MsgBox format("excel is magic", ">")
'Output: 'EXCEL IS MAGIC' (convert text to upper case)
End Sub
read less
UrbanPro.com helps you to connect with the best Excel VBA Training in India. Post Your Requirement today and get connected.
Ask a Question
The best tutors for Excel VBA Training Classes are on UrbanPro
The best Tutors for Excel VBA Training Classes are on UrbanPro