Skip to main content

Posts

Showing posts with the label Vlookup

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…

Combine excel cell values

How to combine excel values from different cells?
Combining values in excel saves time of course, unless you like to buy some time to drag the work and keep yourself busy.
But how to combine values using excel formula?
If you have data of first name and last name in different cells and you want to combine those values in another cell without re-typing the data. This formula below will come handy.
Let’s assume this value:
A1 – contains first name
B1 – contains last name
D1 – will contain the combine cell values in first name and last name format
D2 – will contain the combine cell values in last name, comma and first name format.

For cell D1 the formula is: =A1&" "&B1 (Notice the space in between the “&” (ampersand sign) if it is a double space the combine value will have a double space)
For cell D2 the formula is: =B1&", "&A1 (Notice that the cell address has been reverse and the comma after cell B1.


That’s it those simple formula will save you …

Delete page in word document

Delete word Hyperlinks via VBA

This simple VBA code will delete all the hyperlinks on the active document.

Reading a word document with hyperlinks, is quite annoying sometimes.

If the hyperlink is accidentally click then it will open a browser and disrupt the momentum of reading the document.

And worse if the document link, points to a malicious website then it will be another issue.

Use the VBA code below to delete all hyperlinks on a word document.

The code will also display the number of lines on the document and also the total number of hyperlinks on the document.

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

Sub HyperLines()

 Dim nLines, i, x


 Selection.WholeStory


 i = Selection.Hyperlinks.Count


 nLines = Selection.Range.ComputeStatistics(Statistic:=wdStatisticLines)


 MsgBox "Number of Lines:" & nLines & " Number of Hyperlinks:" & i



 For x = 1 To nLines


 Selection.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=x


 On Error Resume Next
 Application.ActiveDocument.Hyperlinks(1).Delete


 Next x

End Sub


=…

Disable Replace Text in Word using VBA

In word while you are typing, Word is smart enough to replace typing errors.

But if you are not typing in English language, it will be a mess if word will automatically replace the word.

Solution is to disable the feature "Replace Text as you type."

This simple VBA code snippet will disable the options "Replace Text as you type".

Disable is set to "False".

Application.AutoCorrect.ReplaceText = False


It can be done manually by clicking on: (Word 2010 procedure)

1. File
2. Options
3. Proofing
4. Auto Correct Options
5. AutoCorrect Tab
6. Untick "Replace text as you type"
7. Click "OK" 2 times to exit

But the one liner VBA code is quite simple rather than clicking so many times.

To enable via VBA just set the value to True.

Word VBA macro get username

A Word VBA macro to get username and record the date and time the document was opened and closed.

Private Sub Document_Open() -- Will run the macro if the document is open

Private Sub Document_Close() -- Will run the macro if the document is close

To run the macro, create a text file on this location.

D:\DocMonitor\wordUserClose.txt

D:\DocMonitor\wordUserOpen.txt

Change the file name and the location to any path and any desired file name.

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

Private Sub Document_Open()

Dim xUserName As String
Dim xDate As String
xUserName = Application.UserName

Dim xfileSystem, xwriteStream

xDate = Now()

Set xfileSystem = CreateObject("Scripting.FileSystemObject")
'fs.CreateTextFile "D:\DocMonitor\wordUserOpen.txt"
Set xwriteStream = xfileSystem.OpenTextFile("D:\DocMonitor\wordUserOpen.txt", 8, 0)
xwriteStream.WriteLine vbCrLf

xwriteStream.WriteLine Open Date and Time
xwriteStream.WriteLine xDate
xwriteStream.WriteLine xUserName
xwriteStream.Close


Set xfileS…

Unable to print word 2010 document

Assuming the printer is setup properly, and able to print from PDF, notepad or other software and also able to print from printer driver test page.
But when printing a specific word document in Word 2010, it will just show "Not responding" the document will hang and will not print.
There is no restriction set on the document but cannot print.
Try steps below:

Click on the "File" tab, click on the "Review" menu.
From the "Review" tab, select "tracking" change the option from:
"Final: Show Markup" to "Final".

Check image below:


Save the document or save it to a different file name.
And try printing again, if everything goes right the document will be able to print.
Paper tray settings will also cause a problem with printing the document.

Documents from another party that retain printer settings on the document will not print if the printer of the other party does not have same settings on what is currently retain on …

How to select objects behind text in word

Objects that are set as "Behind Text", cannot receive focus or cannot be selected when clicking the object.

If the  image, picture or  other objects cannot be selected. Then the object is set to display "behind text".

To select the picture, image or other objects. Follow steps below:

In Word ribbon, go to "Home" tab.

Select "editing", and click on "Select objects".

Then click on the object that is set as "behind text", then the object should be selected.

See screen shot below:







Hope it helps.. It will come handy, when you're in hurry.

Excel find workbook path or location

Excel VBA get data from Word Table