VBA Select command for Excel Elements

In Excel we deal with cells, rows, columns, worksheet, ranges, etc. Today, let us find how to identify these elements using vba.

range.pngcell

To use cells in vba we have to define as cell(1,1), which means the cell where the  first row and first column is intersected. The same we can define in vba in a different way as Range(“A1”), which is more simpler than cell, as there is no hardship in remembering column number, we can identify easily with alphabetical declaration of the column in excel.

To select A5, we can use:

Cells(5,1).select in which the starting 5 refers to the row and the latter refers to column.

or simpler way is:

Range(“A5”).select

VBA Beginners mostly use cells to identify the ranges. Better use Range command to refer the range element rather than defining cells. It will be easy to identify and remember as well.

Cells reference is used only when you want to select all the cells in a sheet.

Example: cells.select

or to clear all the contents of the sheet

Example: cells.clear

Range(“G5”).Select Selects Cell G5
Range(“D5, M10, Z16, ZZ202”).select Selects noncontiguos cells, A1, B2.
Range(“A1:E200”).select Selects ranges A1:E200
Range(“A1:B10,D6:Z20”).select Select noncontiguos ranges A1:B10 and D6:Z20
Range(“January”).select Select range named “January”
Range(“Order”,”Sales”,”Purchase”).select Selects named ranges Order, Sales, Purchase
Range(“Order”,”January”,”Sales”,”A6″).select Selects named ranges as well as cells A6

These are the different ways in VBA to select cells and ranges and named ranges.

For Sheets:

Sheets(“Order”).select

Sheets(“Purchase”).select

 

What is Data Analysis?

Data analytics refers to qualitative and quantitative techniques and processes used to enhance productivity and business gain. Data is extracted and categorized to identify and analyze behavioral data and patterns, and techniques vary according to organizational requirements.

Data analysis is a process of inspecting, cleansingtransforming, and modeling data with the goal of discovering useful information, informing conclusions, and supporting decision-making. Data analysis has multiple facets and approaches, encompassing diverse techniques under a variety of names, while being used in different business, science, and social science domains.

The process of evaluating data using analytical and logical reasoning to examine each component of the data provided. This form of analysis is just one of the many steps that must be completed when conducting a research experiment. Data from various sources is gathered, reviewed, and then analyzed to form some sort of finding or conclusion. There are a variety of specific data analysis method, some of which include data mining, text analytics, business intelligence, and data visualizations.Data analysis is a primary component of data mining and Business Intelligence (BI) and is key to gaining the insight that drives business decisions. Organizations and enterprises analyze data from a multitude of sources using Big Data management solutions and customer experience management solutions that utilize data analysis to transform data into actionable insights.

Data Analytics Process

Now in Big Data Analytics Tutorial we are going to see the analytic process or how analyzing data can be done?

a. Business Understanding

The very first step consists of business understanding. Whenever any requirement occurs, firstly we need to determine business objective, assess the situation, determine data mining goals and then produce the project plan as per the requirement. Business objectives are defined in this phase.

b. Data Exploration

Second step consists of Data understanding. For further process, we need to gather initial data, describe and explore the data and verify data quality to ensure it contains the data we require. Data collected from the various sources is described in terms of its application and need for the project in this phase. This is also known as data exploration. This is necessary to verify the quality of data collected.

c. Data Preparation

Next come Data preparation. From the data collected in last step, we need to select data as per the need, clean it, construct it to get useful information and then integrate it all. Finally we need to format the data to get appropriate data. Data is selected, cleaned, and integrated in the format finalized for the analysis in this phase.

d. Data Modeling

Once data is gathered, we need to do data modeling. For this, we need to select modeling technique, generate test design, build model and assess the model built. Data model is build to analyze relationships between various selected objects in the data, test cases are built for assessing the model and model is tested and implemented on the data in this phase.

e. Data Evaluation

Next come data evaluation where we evaluate the results generated in last step, review the scope of error and determine next steps that need to be performed. Results of the test cases are evaluated and reviewed for the scope of error in this phase.

f. Deployment

Final step in analytic process is deployment. Here we need to plan the deployment and monitoring and maintenance, we need to produce final report and review the project. Results of the analysis are deployed in this phase. This is also known as reviewing of the project.

The complete above process is known as business analytics process.

Characteristics of Big Data Analysis

Characteristics of Big Data Analytics which make it different from traditional kind of analysis.

Concurrently selecting data collection methods and appropriate analysis

While methods of analysis may differ by scientific discipline, the optimal stage for determining appropriate analytic procedures occurs early in the research process and should not be an afterthought. According to Smeeton and Goda (2003), “Statistical advice should be obtained at the stage of initial planning of an investigation so that, for example, the method of sampling and design of questionnaire are appropriate”.

Data Analysis Model

Gwen Shapira, a solutions architect at Cloudera and an Oracle ACE Director, outlines seven key steps of data analysis for Oracle’s Profit magazine. Shapira explains that while each company has its own data requirements and goals, there are seven steps that remain consistent across organizations and their data analysis processes:

  • Decide on the objectives – Determine objectives for data science teams to develop a quantifiable way to determine whether the business is progressing toward its goals; identify metrics or performance indicators early
  • Identify business levers – Identify goals, metrics, and levers early in data analysis projects to give scope and focus to data analysis; this means the business should be willing to make changes to improve its key metrics and reach its goals as well
  • Data collection – Gather as much data from diverse sources as possible in order to build better models and gain more actionable insights
  • Data cleaning – Improve data quality to generate the right results and avoid making incorrect conclusions; automate the process but involve employees to oversee the data cleaning and ensure accuracy
  • Grow a data science team – Include on your science team individuals with advanced degrees in statistics who will focus on data modeling and predictions, as well as infrastructure engineers, software developers, and ETL experts; then, give the team the large-scale data analysis platforms they need to automate data collection and analysis
  • Optimize and repeat – Perfect your data analysis model so you can repeat the process to generate accurate predictions, reach goals, and monitor and report consistently

Screenshots

Work smarter not harder

As we grow in our business, we are circled with unlimited list of tasks. To do these, we have to spare lot of our time.

We realize that we cannot spare lot of our time and the task we have is really important to deal with.

How to deal:

1. Segregation of the task as:

a. Urgent

b. Important but not Urgent

c. Urgent and Important

d. Not Important

Complete the urgent task, urgent and important task, then process with important but not urgent task and after that not important task. This way of dealing can probably make you work more smart.

2. Automate repetitive task.

If you are an office user, and has lot of repetitive task, then try to automate using VBA.

VBA – Visual Basic for Application is a powerful programming tool for office applications, through which one can automate all the office applications – word, excel, powerpoint, one note, access, ms project and can even extract bulk web data from websites. If you have online business, you can directly get the stats and update using VBA.

Can  create dashboards to know the stats and process your interaction with the websites in excel on a single click.

3. Take small intervals while on work:

    Upon long hours of work, its a good booster to take break in small intervals, this will make you relaxed. In turn, increases productivity in your working process.

What is Work Ethics?

We must respect our work and deal justly. In order to perform best one must have work ethic. Having a belief that work has intrinsic value, one must be accountable and responsible for the work one does. This belief is what work ethic advocates a person to cultivate a positive attitude within. Regardless of the context, work ethic is usually associated with people who work hard and do a good job.

Traditionally work ethics is defined as a value related to hard work and diligence. To create wealth to elite, the working classes are made to work harder, dishonoring the work ethics by not valuating the hard work. Fact is that, it’s not true that working more means producing more or even that more production leads to a better life… this is, of course, not to be confused with quality productivity. Now, what the modern term changed from being working hard to working smart.

Our work ethics can be utilized to find latest tools and technology to work smarter rather than harder. Prioritize work; be productive in order to provide quality work within a less span of time.

That’s not my job – Way to Failure

We all of some reason, would fall prey to our own egos, which in-turn leads to our own destruction.

Image result for thats not my job cartoon
Normally, we used to escape from work. We pinpoint on others, rather doing our work in time and gets into trouble.

Our day to day team activities is the base for our success. We have to provide clear instructions to our team members to do the task in time. Without such action, we rely on others hoping that they would do. At last, none will do, instead gets into trouble at end.

That’s not my job, is what we are mentoring our-self and backbitting the others instead for not doing the job, that can be done by our-self.

Check out:

That’s not my job

saftproimg1

This is a story about four people: Everybody, Somebody, Anybody and Nobody.

There was an important job to be done and Everybody was sure that Somebody would do it. Anybody could have done it, but Nobody did it.

Somebody got angry about that, because it was Everybody’s job. Everybody thought Anybody could do it but Nobody realized that Everybody wouldn’t do it.

It ended up that Everybody blamed Somebody when Nobody did what Anybody could have done.

We are all working towards the same common goal. Success comes from the value that each member brings to the table. Sometimes this means going above and beyond. Put values out of the box.

Success lies on doing what you can do best, not on what others do.

 

How to Add formulas using Vba

VBA to fill formula down till last row in column

I want excel to draw down this formula so in cell M4 it would be =G4&”,”&L4 Cell M5 would be =G5&”,”&L5 and so on.

My code looks like:

Range("$M$3").Formula = Range("G3") & (",") & Range("L3")

Dim Lastrow As Long

Application.ScreenUpdating = False

Lastrow = Range("L" & Rows.Count).End(xlUp).Row
Range("M4").FormulaR1C1 = Range("G4") & (",") & Range("L4")
Range("M4").AutoFill Destination:=Range("M4:M" & Lastrow)
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

VBA To Add Formula To Cell

I am attempting to write some VBA which will add header text to 3 cells then fill a formula all the way down to the last row. I have written the below, which writes the headers no problems, but when it get’s to my first .Formula it throws a

Application Defined or Object Defined error

What needs to be altered so that this macro will execute successfully? (The formulas were pulled directly from the formula in the cell, so I know they are valid formulas at least on the “front-end”)

Function Gre()
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Under"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Over"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "Result"

    With Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(C2<B2,B2-C2,"")"
    End With
    With Range("F2:F" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(C2>B2,C2-B2,0)"
    End With
    With Range("G2:G" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(F2>0,'Issue',"")"
    End With
End Function

Offset in VBA

What is Offset?

The Offset property in Excel VBA takes the range which is a particular number of rows and columns away from a certain range (border below for illustration only). Place a command button on your worksheet and add the following code lines: Dim example As Range.

What is the OFFSET Function?

The OFFSET function is categorized under Lookup and Reference functions. OFFSET will return a range of cells. That is, it will return a specified number of rows and columns from an initial range that was specified.

In financial analysis, we often use Pivots Tables and Charts. The OFFSET function can be used to build a dynamic named range for pivot tables or charts to make sure that the source data is always up to date.

 

Formula

OFFSET(reference, rows, cols, [height], [width])

 

The OFFSET function uses the following arguments:

  1. Reference (required argument) – It is the cell range that is to be offset. It can be either single cell or multiple cells
  2. Rows (required argument) – It is the number of rows from the start (upper left) of the supplied reference, to the start of the returned range.
  3. Cols (required argument) – It is the number of columns from the start (upper left) of the supplied reference, to the start of the returned range.
  4. Height (optional argument) – It specifies the height of the returned range. If omitted, the returned range is the same height as the supplied refe
  5. Width (optional argument) – It specifies the width of the returned range. If omitted, the returned range is the same width as the supplied reference.

In computer science, an offset within an array or other data structure object is an integer indicating the distance (displacement) between the beginning of the object and a given element or point, presumably within the same object. The concept of a distance is valid only if all elements of the object are of the same size (typically given in bytes or words).

For example, in A as an array of characters containing "abcdef", the fourth element containing the character 'd' has an offset of three from the start of A.

In computer engineering and low-level programming (such as assembly language), an offset usually denotes the number of address locations added to a base address in order to get to a specific absolute address. In this (original) meaning of offset, only the basic address unit, usually the 8-bit byte, is used to specify the offset’s size. In this context an offset is sometimes called a relative address.

In computer science, offset describes the location of a piece of data in relation to another location. For example, when a program is accessing an array of bytes, the fifth byte is offset from the beginning of the array by four bytes. If the array contained data that is meant to be read 32 bits at a time, element n would be offset from the start of the array by 4 * (n – 1) bytes.

What is VBA?

Microsoft has implemented various features in its office suite. Most of us know, how to use excel, add formulas, charts, tables, etc., but did you thought beyond these that you can yet accomplish an advanced feature, of which you are unaware.

We are tired of performing repetitive task in our daily life and would think of a better way to automate this process.

Yeah….

To your astonishment, here is an another tool called “VBA” to automate your Office day to day process.

Want to become awesome in Excel, Access, Powerpoint, OneNote, Outlook, Ms Project and Ms Word. Hey! We are here to make you Office Pro.

Get along with us and carry on VBA Journey.

What is VBA?

Visual Basic for Applications (VBA) is an implementation of Microsoft’s event-driven programming language Visual Basic 6, which was discontinued in 2008, and its associated integrated development environment (IDE). Although Visual Basic is no longer supported or updated by Microsoft, the VBA programming language was upgraded in 2010 with the introduction of Visual Basic for Applications 7 in Microsoft Office applications.

‘Visual Basic For Applications –VBA‘ … Visual Basic for Applications requires code to run within a host application such as Excel or other office applications, because it cannot run as a standalone application. In the finance industry, VBA for Excel is frequently used to create and maintain complex financial spreadsheet models.

 

%d bloggers like this: