Excel VBA Training

Excel VBA Training

Trending Questions and Lessons

Follow 1,051 Followers

Ask a Question

Feed

All

All

Lessons

Discussion

Answered on 20/06/2017 Excel VBA Training

Hi I want to Vlookup a file and copy the entire row to another file and I wan this to be done through... read more
Hi I want to Vlookup a file and copy the entire row to another file and I wan this to be done through VBA. Can anyone help me on it? read less

Altaf K.

Microsoft Certified Trainer

What help you need? You want to have code or you want to learn VBA properly so you can do such small stuff at your own.
Answers 1 Comments
Dislike Bookmark

Lesson Posted on 31/05/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel 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... read more

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.

read less
Comments
Dislike Bookmark

Lesson Posted on 17/05/2017 IT Courses/Advanced VBScript Excel Macro Training Excel VBA Training +5 Excel VBScript Training IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training less

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... read more

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 less
Comments
Dislike Bookmark

Looking for Excel VBA Training

Find best Excel VBA Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 05/05/2017 IT Courses/MS Office Software Training/Microsoft Excel Training Excel VBA Training Excel Macro Training +4 Excel VBScript Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training less

VBA Function - RIGHT

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... read more

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 less
Comments
Dislike Bookmark

Lesson Posted on 02/05/2017 Excel Macro Training Excel VBA Training IT Courses/MS Office Software Training/Microsoft Excel Training +3 IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel Excel VBScript Training IT Courses/Advanced VBScript less

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... read more

 

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 less
Comments
Dislike Bookmark

Lesson Posted on 26/04/2017 IT Courses/Advanced VBScript IT Courses/Computer Course IT Courses/MS Office Software Training +7 IT Courses/MS Office Software Training/Microsoft Excel Training Excel Macro Training Excel VBA Training Excel VBScript Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel less

VBA Function - INSTR

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( , string, searchingfor, ) Sub INSTR_1() MsgBox InStr(1, "Narendra Modi", " ") 'Output: 9 MsgBox InStr(1, "Narendra Modi", "Modi") 'Output:... read more

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 less
Comments
Dislike Bookmark

Looking for Excel VBA Training

Find best Excel VBA Training in your locality on UrbanPro.

FIND NOW

Asked on 22/04/2017 Excel VBA Training

How can I post my requirement on UrbanPro? Over 7 years of experience in Advance Excel VBA (Visual Basic... read more
How can I post my requirement on UrbanPro? Over 7 years of experience in Advance Excel VBA (Visual Basic for Applications) with other technologies i.e MS Access, SQL Server. I teach basic & advanced excel, VBA Access and SQL. read less

Answer

Lesson Posted on 13/04/2017 IT Courses/Advanced VBScript Excel VBA Training Excel VBScript Training

Excel

Punit kumar

I have a total of six years plus of work experience.I have worked on multiple projects of different sizes....

The 1900 Date System 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... read more

The 1900 Date System

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.

The 1904 Date System

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.

The Difference Between the Date Systems

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
Comments
Dislike Bookmark

Lesson Posted on 23/03/2017 IT Courses/MS Office Software Training/Microsoft Excel Training Excel VBA Training Excel Macro Training +3 IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/IT Certifications/Microsoft Certification less

VBA Function -

Excelytics Vba Macros

Excelytics was established in the year 2014 by Microsoft Certified Professionals. Excelytics is a...

Using FORMAT Function in VBA we can modify the format of Numbers, Dates, Times & String. Syntax: FORMAT(expression, format, , ) 1. Formatting "Numbers" using FORMAT function in VBA SUB Format_Numbers() MsgBox format(8686836999, "####-###-###") 'Output: '8686-836-999' MsgBox format(143.5, "#,##.00") 'Output:... read more

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
Comments
Dislike Bookmark

Looking for Excel VBA Training

Find best Excel VBA Training in your locality on UrbanPro.

FIND NOW

Lesson Posted on 27/02/2017 IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel Excel Macro Training +2 Excel VBA Training Excel VBScript Training less

Excel Tip: Calculation of Overall Rating in Excel

Excelytics Vba Macros

Excelytics was established in the year 2014 by Microsoft Certified Professionals. Excelytics is a...

Nowadays rating got high importance everywhere. Whether you want to purchase an AC or a toy first we will check for rating of that product. Even before joining any institute we will review the rating then we will decide whether to join or not. Today we will learn calculating rating in Excel with example.... read more

Nowadays rating got high importance everywhere. Whether you want to purchase an AC or a toy first we will check for rating of that product. Even before joining any institute we will review the rating then we will decide whether to join or not. Today we will learn calculating rating in Excel with example. In the below screenshot1 you can see the Overall rating and number of students reviewed count in JD site. Overall rating is 4.4 from 307 student reviews. Let’s see algorithm behind this 4.4.

5 star rating given by 210 students, 4 star rating by 60 students…(see below screenshot2)

Weighted Average of Overall student rating is =210*5+60*4+19*3+6*2+12*1 = 4.47

Screenshot1

 

Screenshot2

Hope you have learned something new today

Regards,

Team Excelytics

 

 

read less
Comments
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best Excel VBA Training in India. Post Your Requirement today and get connected.

Overview

Questions 11

Lessons 22

Total Shares  

Top Contributors

Connect with Expert Tutors & Institutes for Excel VBA Training

Excel VBA Training in:

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 6.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more