Skip to main content

Posts

Showing posts with the label Excel 2010

VBA Word change graph data point width

If you have been playing with the chart in Word or Excel you might encountered that the point on the graph is quite small.
If you need to adjust the data point on Word graph, you have two options do it manually or simply automate it at runtime using a VBA code.
Assuming you have all the necessary parameters that creates the graph via VBA, and you just need to adjust the data point you can use the code below, the code below was tested using Word 2016. It may not apply to older versions but I think logic should stay the same.
cht.SeriesCollection(1).Format.Line.Weight = 9.75
To do it manually, click on the data point and right click on it and select “Format Data Point”, click the bucket icon and change the width value.

Here’s the screen shot using Word 2016:

For the sake of completeness, the code will be something like this:
Dim shp As Shape Dim cht As Chart  
Set shp = ActiveDocument.Shapes.AddChart(xlXYScatterLines)   Set cht = shp.Chart
Dim wb As Excel.Workbook Dim ws As Excel.Worksh…

How to rename a chart in Word 2016

Do you want to check what’s the name of the chart in Word 2016?
Or you just want to rename the chart in Word 2016 to a name, that the chart represents?
Word 2016 default chart name is in sequence, like Chart1, Chart2, Chart3 and it follows this pattern depending on how may charts you have at your document.

If you have multiple charts in a word document, and you want the chart name it to something meaningful or what the chart itself is all about; it’s quite easy and straight forward to rename a chart, but the way to rename it is not conventional as the previous way of doing it in other version of office.
You can rename the chart via VBA code or using the GUI in Word itself.
To rename or check the name of the chart in Word 2016 or O365.
Click on the chart:

After clicking the chart, the "Chart Tools" will open at the Ribbon.
Click the "Chart Tools"  at the ribbon. Click on the "Format" tab.
Under Format tab, click on "Selection Pane", this will op…

Sort numbers, digits or figures in Excel 2016

To sort numbers, digits, or figures in Excel 2016 is quite straight forward.
There are two ways to do it via the Home tab or Data tab.
What is found on the Home tab is sorting of alphabets or A-Z option which can easily be seen beside the auto-sum function which is on the right most tab of the ribbon by default.
Screen shot below shows the sorting functionality found in Home tab.


The other way to sort by numbers, digits or figures in Excel 2016 is to click on “Data” click on “Sort” then an option will appear to sort by smallest to largest or largest to smallest.
A screen shot below shows where to find the function.


That’s it hopes it helps to make thing easier.


================================ Free Android Apps:
Click on links below to find out more:

Excel Keyboard shortcuts guide https://play.google.com/store/apps/details?id=chrisjoms.myexcelapplicationguide
Linux Android App cheat sheet: https://play.google.com/store/apps/details?id=com.LinuxMobileKit Multiplication Table for early learner…

Excel VBA set worksheet visibility

Set excel worksheet visibility by changing its property.
Simple and a one liner code to hide and unhide worksheet in Excel.
Sub Visible_Sheet()
Worksheets(1).Visible = 1  'Make the worksheet visible
End Sub
Sub Hide_RightClick_Unhide()
Worksheets(1).Visible = 0    'Hide Worksheet to Unhide Right click on Sheet name and unhide the worksheet
End Sub
Sub Super_Hide()
Worksheets("calcx").Visible = 2
 'Hide Worksheet but right click on Sheet name will not show whether the sheet is hidden or not
End Sub



Cheers! Till next time.

================================ Free Android Apps:
Click on links below to find out more:

Excel Keyboard shortcuts guide https://play.google.com/store/apps/details?id=chrisjoms.myexcelapplicationguide
Linux Android App cheat sheet: https://play.google.com/store/apps/details?id=com.LinuxMobileKit
Multiplication Table for early learners https://play.google.com/store/apps/details?id=com.TableMultiplication
Catholic Rosary Guide  for Android:
https://play.google.com/sto…

Excel VBA copy or reference a value

Excel VBA provide options on how to pass value from worksheet to worksheet or from workbook to another workbook.
To optimize or make the VBA code run faster is to make the worksheet interaction lesser.
Recording an Excel VBA macro to copy and paste a value will have a similar output to the code below.
Sub Macro1() ' ' Macro1 Macro '     Range("A2").Select     Selection.Copy
    Sheets("Sheet2").Select     Range("A3").Select     ActiveSheet.Paste
End Sub
what the code does is to select the cell on the active sheet for copying and select another worksheet and select the cell where the value will be placed and paste the copied cell value.
Steps are: a. Select the cell and copy the value b. Select the worksheet where the value is to be copied c. Next step, is select the cell on the selected worksheet d. Paste the value on the cell
Steps are absolutely fine, if it works as expected. But if you are copying hundreds or thousands of cells. It will be a w…

Excel find last row with value in a specific column

1. How to find last row with value using vba in a specific column?

2. How to find last row with value  using vba in a worksheet?

The questions above are entirely two different scenarios.

The first question will find the last row with value in a specific column within the worksheet.

While the second question find the last row with value of the entire worksheet.

The first question is applicable if you need to find the last row in different columns.

Let's say Column 1 or Column A, or Column 2 or Column B etc.

Example code below will find the last row in a specific column via VBA code.

===================
'command Cells(Rows.Count, 1) = find the last row with value in Column 1 for Sheet3

Dim last_row As Integer

last_row = Worksheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row

Msgbox last_row

'replace 1 with the desired column 
'ex: command Cells(Rows.Count, 7) = find the last row with value in Column 7
===================

If you don't need to specify a worksheet or…

How to insert landscape orientation in Word?

If you are writing a thesis, a report or an article that you need to insert a landscape layout in a specific page in Word document but your orientation all long is portrait and changing the orientation causes the whole layout to change.
Inserting landscape in a specific page in a word document can be done by inserting section breaks. Let’s just make a basic example.
Let’s say you have 3 page document and you want that first page in Portrait mode, second page in landscape and third page in portrait layout also.
First, click on “home” tab, click the “show/hide paragraph marks” option or see the icon below. Enabling this option the section breaks, spaces and tabs will be visible. But what we are interested is to see the “section breaks”.


In the first page position the cursor on the last area at the bottom of the page.
In Word 2010, click on “page layout” in the ribbon tab. Click on “breaks” and select “next page”. In the second page, position also the cursor at the middle or bottom of t…

Create a progress bar in Excel

Create a progress bar in Excel that varies on the input value and range.

If the value is  equivalent to 100% or maximum input is reach then the color will fill the whole cell.

This example below was created using Excel 2010, the logic should be the same with other version that supports this function.

1. Select the cell, that will have the progress bar.
    Click on "Home" tab, click on "Conditional Formatting"
    - In drop down menu select Data Bars
       - In the sub menu click "More Rules".

See screen shot below:



2. After clicking "More Rules", "New Formatting Rules" window will open.
    - In "Select a Rule Type"
       "Format all cells based on their values" should be selected
    - Under Rule Description
       Set the type to "number"
       Set the range of minimum and maximum value
       Select the color that you want and click "OK", once customization is done.

See screen shot below:





Excel formula fixed cell address

How to insert a formula with a fixed cell address? Absolute referencing will come to the rescue.

Absolute reference in Excel is prefix with a dollar ($) sign.

$A$1: This means that Column "A" and Row "1" will not change when copied to another cell.

Example:

=SUM(A$1,$A2,$A$1)


The above formula when copied from one cell to another cell, $A$1 will remain the same.

While A$1,$A2 will change its location when copied to another cell.

 A$1: This means that column "A" will change while row location (which is number 1) will not change when copied.

 So this absolute reference will change like D$1, G$1 or E$1 depends on the location. So number "1" value will never change.

 $A1: The column "A" will not change when copied from one cell to another cell, while the row value which is number "1" will change.


So the above absolute reference will change like $A3, $A10, $A12 etc., Column "A" will be constant while the row changes.


So…

Word VBA Select Page

How to select a page in Word VBA?

Sub Select_Home()

'Select the first page of the document (page 1)
'Like pressing Ctrl + Home

Selection.GoTo wdGoToBookmark, , , "\StartOfDoc"


End Sub

Sub Select_document_end()

'Select the end of the document
'Like pressing "Ctrl+End"

Selection.GoTo wdGoToBookmark, , , "\EndOfDoc"

End Sub

Sub Select_via_Page_Number()

'specify the page number
'Example below will select page 5

Selection.GoTo wdGoToBookmark, , , "5"

End Sub

Tested and working using Word 2010.

Cheers..till next time :) Happy coding.

================================
Free Android Apps:

Click on links below to find out more:

Linux Android App cheat sheet:
https://play.google.com/store/apps/details?id=com.LinuxMobileKit


https://play.google.com/store/apps/details?id=soulrefresh.beautiful.prayer

Catholic Rosary Guide  for Android:

https://play.google.com/store/apps/details?id=com.myrosaryapp

http://quickbytesstuff.blogspot.sg/2014/09/how-to-r…

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 VBA autofill date range

How to auto fill a range of cells with date?

To auto fill range of cells can be done via VBA or manually.

To manually fill a range of cells with date.

Type two dates on two cells and highlight the two cells and manually drag to the desired range of cells.

Drag from the bottom right with the "+" sign on it.

See screen shot below:



To do it via VBA is quite simple also using autofill function.

Sub AutoFill_Dates()

  Dim srcRange As Range
  Dim destRange As Range

  Set srcRange = ActiveSheet.Range("E1") 'E1 should have valid date value
  Set destRange = ActiveSheet.Range("E1:E15") 'E1 to E15 will be filled with the dates (starting the date specified on E1)
  srcRange.AutoFill destRange, xlFillSeries 'xlFillSeries will auto fill the dates in sequence

End Sub



Cheers.. Hope it helps..

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…