# Making a helper column with preset formula

#### Dallie

##### New Member
Hi,

I am wanting any help please. I need some help to speed up days worth of calculations by making a macro that will automatically provide a helper column with preset formulas on the first column that is empty. It will probably need an input box asking which column and row that I will be referencing as this may change. Generally my data starts in row 3 with row 2 as the header.

For example if choosing column A the macro will automatically create a helper column with the preset formula in the first empty column which is C:
 A B C 1 2 Car (mins) Truck (mins) 3 12 15 =A3*15 4 10 18 =A4*15 5 6 =A5*15 6 8 =A6*15

<tbody>
</tbody>

Or if choosing column B the macro will create a helper column in C with the preset formula:
 A B C 1 2 Car (mins) Truck (mins) 3 12 15 =B3*15 4 10 18 =B4*15 5 6 6 8

<tbody>
</tbody>

I assume I have to create a loop in the range and go down the row but I am far out of my depth.

Any help is appreciated!
Thanks

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### footoo

##### Well-known Member
Select any cell(s) in the relevant column and run this :
Code:
``````Sub v()
Dim c%: c = Selection.Column
Dim r&: r = Cells(Rows.Count, c).End(xlUp).Row
If r < 3 Then Exit Sub
Dim fc%: fc = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column + 1
Range(Cells(3, fc), Cells(r, fc)).Formula = "=" & Cells(3, c).Address(0, 0) & "*15"
End Sub``````

#### Dallie

##### New Member
Thanks a lot Footoo! This is great. As I am a noob and I am trying to work out the code to make me better understand for the future could you please explain what the coding "Dim c%:" or "Dim r&:" means? What dimensions are these set too?

#### footoo

##### Well-known Member
[FONT=&quot]Variable Data Types[/FONT][FONT=&quot] [/FONT][FONT=&quot]A Type Declaration Character is a character appended to a variable name indicating the variable's data type. While declaring a variable using the Dim statement, for certain data types only (see below), you can use "type-declaration character" instead of the "As" clause. However this is not commonly used.[/FONT][FONT=&quot] [/FONT]
 Data Type Type-Declaration Character/Suffixes Integer % Long & Single ! Double # String \$ Currency @

<tbody>
</tbody>
[FONT=&quot] [/FONT][FONT=&quot]Example using Type-Declaration Character: Dim studentName\$ instead of Dim studentName As String[/FONT]

Replies
6
Views
91
Replies
6
Views
100
Replies
5
Views
54
Replies
11
Views
110
Replies
4
Views
59

1,109,436
Messages
5,528,758
Members
409,834
Latest member
vexceled

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...