Skip to main content

Posts

Showing posts with the label Vlookup Function

Excel count non-empty cells or blank cells

Excel provides a function to check blank cells and also provide functions to check cells that are not empty.

CountA function will check cells that are not empty.
Countblank function as its name literally implies will check or count for blank or empty cells.

Example for Countblank function:

=COUNTBLANK(A1:B11) --- Function will check for empty spaces from A1 to B11 (2 columns)
=COUNTBLANK((A1:A11)) --- Function will check for empty spaces in a single column. Note the double parentheses
Example for CountA function:
=COUNTA(A1:A11) --- Function to check cells with values in a single column (Column A only)
=COUNTA(A1:B11)  --- Functio to check cells with values in two columns from A1 to B11 (Column A and B)

To enter formula above using VBA:
==============================
Sub VBA_Formula()
Dim Cell_Formula As String Dim xvalue As String Cell_Formula = "=COUNTA(A1:A11)" 'Cell_Formula = "=COUNTBLANK((A1:A11))" 'Cell_Formula = "=COUNTBLANK(A1:B11)"
'Set cell for…

Excel 2010 date difference

How to use datedif function in Excel?
How to find date difference between two dates in Excel?
How to find date difference by month, year or days in Excel?
Excel has a built-in function and does the heavy lifting to date difference between two dates.
Excel uses the “datedif” function to easily compute the date difference  by days, months or years.
Notice the Datedif function has a single “f” at the end it’s not DateDiff. If you accidentally type double “f” at the end of the function excel will return an error and will just display “#NAME?” which basically means that the function is not recognized by Excel.
Here’s the function:
=DATEDIF(A1,C1,"d")  ==== > this function find the date difference by days for the values of A1 and C1

=DATEDIF(A3,C3,"m")  ==== > this function find the date difference by months for the values of A3 and C3

=DATEDIF(A5,C5,"y")  ==== > this function find the date difference by years for the values of A5 and C5
Excel screen sho…

Excel find workbook path or location

Excel VBA adjust printing setup

Printing an Excel worksheet, can be adjusted manually.

In Excel 2010 and other versions of excel, printing can be adjusted by zoom in and zoom out.

In Excel 2010, to adjust page setup:

Click on "File".

Click on "Print".

Then the print preview is shown on the screen.

Under the "Custom Scaling" option, click on "Page Setup".

Click on "Adjust to" option and set the desired percentage.

See image below:



To do this via VBA or macro, it's quite straight forward.

See code below, just specify the "zoom" keyword and the value.

=========================
Sub VBA_PageSetup_Option()

With Worksheets("Sheet1")
 .PageSetup.Orientation = xlLandscape
 .PageSetup.Zoom = 280 '< - - - set the desired percentage
 '.PrintPreview 
 .PrintOut
End With

End Sub

 =========================

Code VBA above will zoom or adjust the whole worksheet.

If need to choose only a specific print area, then use the code below from Technet.

After sett…

Excel 2010 disable auto insert of decimal point

Disable auto insert of decimal point in excel 2010.

When typing in excel, the number typed on the worksheet automatically changed to decimal point.

This could happen, if the auto insert of decimal point is enabled.

To disable follow settings below:

Go to the ribbon, click on File.

Select "options".

On the excel options window, click on 'Advance'.

Uncheck "Automatically insert a decimal point".

See image below:



Cheers!! Hope it helps...

Use SQL Query in Excel VBA

If you are an SQL junkie then using SQL in Excel VBA would be a good choice.

If just starting to embark on the SQL world, then it would be a fun also to use SQL statement in Excel VBA.

To start SQL queries in Excel VBA, it will need to reference to "Microsoft Activex Data Object 6.1 Library".

If Microsoft Activex Data Object 6.1 Library, is not found on the system.

Please refer to this Microsoft link:
https://support2.microsoft.com/kb/2640696?wa=wsignin1.0

To add reference using Excel VBA, press "Alt + F11" to open Excel VBA Project Window.

Click on "Tools" and click "References", then browse for Microsoft Activex Data Object 6.1 Library and click ok.

Or check out link below for the screen shot on how to add References in Excel VBA.

This link has screen shots on how to add references.
http://quickbytesstuff.blogspot.sg/2014/09/excel-vba-speak-method.html

Once the Activex Library is added, then it's time to code and have some fun.

There are tw…

Excel VBA get data from Word Table

Insert watermark in excel 2010

Excel 2010 does not provide a native function to insert watermark unlike in Word.
Word by default has a function on the Ribbon tab to insert a watermark.
In Excel watermark can be done also using this method below.
On the Ribbon tab click on “Insert”, then click on “Header & Footer”.
On the small rectangle that will pop up after clicking “Header & Footer” type:
&[Picture]
Type exactly as it shown “&[Picture]”,  when done typing click outside the small rectangle.
Then an insert dialog box will appear choose from your PC any pictures you want as a watermark background.
Please images below on how to insert a watermark background in “Excel 2010”.






After selecting a picture to insert then the final result would be something like this:
Image inserted as watermark:

Screen shot of excel with the watermark background:




Cheers.. hope it helps!!

Excel VBA Speak method