Dynamic Ranges and Modifying Recorded Macros

mtgingrass

2017/09/16

Build Templates

In this tutorial, we will build upon the use of ranges and combine recording macros with modifying your own macros.

In this tutorial you will learn the basics of:

Watch the Video for step by step instructions.

[embed]https://youtu.be/q8u4BlB2D2g[/embed]

Code

Copy and paste the code below to get you started.

<code>
Option Explicit

Sub template_builder()
    Range("A1").Value = "Mark G"
    Range("A2").Value = "=today()"
    Range("A2").Select    
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00"
End Sub</code>

Adding Formulas,

Changing a cells value with Range.Value method works with formulas as well as regular inputs. Try Range(“A2”).Value = “=now()” or Range(“A6”).Value = “=B2 + B3”

Dynamic Ranges

Hard coding Ranges will limit the use of the Range object. Remember, every key-stroke can be recorded via the macro recorder. If you know a shortcut key to select all, or navigate to a certain position, etc. Then it can be automated via VBA.

Watch the tutorial video to see a great example of this.

Modifying Recorded Macros

Probably the most useful tool in our toolkit at the moment is recording macros. If you don’t know the code, just record a macro and let the code be giving to you. It’s that easy.

The hard part is the modification of the macros to suit a dynamic need. Recorded macros should be modified to suit your needs.

comments powered by Disqus