Monthly Archives: April 2019

VBA

Automating with macro and shortcut key

Most of Excel users have sometimes changed the calculated value to static value, I bet. This is easy.

ISFORMULA formula is checking whether the value is calculated by a formula or a static value.

Number 2 in D3 is a calculated value, ISFORMULA returns the value TRUE. To make it static 2

Activate the cell D3.

Select Copy or press control + C.

Then take paste values and press enter to empty the clipboard.

Now ISFORMULA also finds that it is not calculated anymore.

If you want to do the process above, create a macro.

Take view – macros -record macro. Activate Use relative references.

Store the macro in your personal workbook. Then the macro is available in every Excel workbook. Add also a shortcut key.

Repeat the steps above to copy and paste as values.

After the steps just stop recording.

 Check the VBA code from the developer-visual basic. It should look like this.

Sub Own_name()

    Selection.Copy

    Selection.PasteSpecial Paste:=xlPasteValues

    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

Instead of recording macro, you can also copy the code above.

To run macro quickly, you can add a shortcut key. Now even a simple task has been automated with macro. If you repeat copy-paste as values several times a day, like I do, macro with shortcut key will save your time.

Excel

IF with VLOOKUP

Vlookup is simple.

You have to define the lookup value. For the first case in the line number 6, the lookup value is 2. The table, where the value is looked up is A2:B4. Column index is 2, as the values are looked up one row right from the lookup value. False means that we are looking for exact value 2 in row A not a value closest to 2. Then the VLOOKUP returns the value qw.

If VLOOKUP does not find the value 3, it returns the value #N/A. Some other value might cosmetically be better like “not found” or just blank cell.

We can use ISERROR function. The formula tells us whether the function returns a reasonable value or not.

IF the VLOOKUP returns a value with error, then print “not found” or blank. IF the VLOOKUP returns a value without error, then use the normal VLOOKUP function.

To copy paste the IF sentence:

=IF(ISERROR(VLOOKUP(A6;A2:B4;2;FALSE));”Not found”;VLOOKUP(A6;A2:B4;2;FALSE))

If a blank cell is better than any text, then just delete Not found text and use the quotation marks like “”.

Excel

Changing date format from mm.dd.yyyy to dd.mm.yyyy

Date formats in Excel have caused issues for me. For example the format used in US month.day.year should be converted to day.month.year format. Eg. 2.12.2019 should be 12.2.2019.

You can always take home-number | format cells – number -date selection. However, sometimes format cells functionality has not helped me.

One option is to consider date as a normal keystring.

There are four options.

  1. Second and fourth digits are dots like 3.4.2019. In this case IF-function should take the third digit then add dot, then the first digit from left and add dot then take four digits from  the right.
  2. Third and fifth digits are dots like 12.2.2019. IF sentence picks up the fourth digit the adds the dot. Then takes two digits from left, adds the dot and takes four digits from the right.
  3. Second and fifth digits are dots like 3.20.2019. IF statement selects the third and fourth digits then adds a dot. Digit from left is selected, then a dot is added. Four digits from right is chosen.
  4. Third and sixth digits are dots like 11.25.2019. IF command takes fourth and fifth digits and then adds a dot. Two digits from select are picked and then a dot is placed. Finally, as earlier, four digits from right are selected.

=IF(AND(FIND(“.”;A4)=2;(FIND(“.”;A4;FIND(“.”;A4)+1))=4);(MID(A4;3;1)&”.”&(LEFT(A4;1))&”.”&(RIGHT(A4;4)));IF(AND(FIND(“.”;A4)=3;(FIND(“.”;A4;FIND(“.”;A4)+1))=5);(MID(A4;4;1)&”.”&(LEFT(A4;2))&”.”&(RIGHT(A4;4)));IF(AND(FIND(“.”;A4)=2;(FIND(“.”;A4;FIND(“.”;A4)+1))=5);(MID(A4;3;2)&”.”&(LEFT(A4;1))&”.”&(RIGHT(A4;4)));IF(AND(FIND(“.”;A4)=3;(FIND(“.”;A4;FIND(“.”;A4)+1))=6);(MID(A4;4;2)&”.”&(LEFT(A4;2))&”.”&(RIGHT(A4;4)))))))

In case you have similar case, but you have slash / instead of dot. Just copy the formula line above and paste that to notepad. Then press control + H to replace all the dots with slashes.

When you implement this IF sentence to your Excel, you can change the target cell from A4 to the cell address where you have the data, it useful to change the cell with note pad as explained above.

IF sentence is bit long to be presented in screen shots, but you can test by yourself. Like writing value 1.23.2019 to A4 cell. Then paste the IF command above the next cell. See what happens.

This solution is somewhat complex, hopefully some easier solutions could be found too. I needed to tackle some different date formats, and then I created the IF sentence above.