Making a helper column with preset formula

[Dallie]

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:[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]Car (mins)[/TD]
[TD="align: center"]Truck (mins)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]=A3*15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD]18[/TD]
[TD]=A4*15[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]=A5*15[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]=A6*15[/TD]
[/TR]
</tbody>[/TABLE]

Or if choosing column B the macro will create a helper column in C with the preset formula:
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]Car (mins)[/TD]
[TD="align: center"]Truck (mins)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]=B3*15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD]18[/TD]
[TD]=B4*15[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[footoo]

Select any cell(s) in the relevant column and run this :

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]

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]

[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][TABLE="width: 100%"]
<tbody>[TR]
[TD="align: center"]Data Type[/TD]
[TD="align: center"]Type-Declaration Character/Suffixes[/TD]
[/TR]
[TR]
[TD="align: center"]Integer[/TD]
[TD="align: center"]%[/TD]
[/TR]
[TR]
[TD="align: center"]Long[/TD]
[TD="align: center"]&[/TD]
[/TR]
[TR]
[TD="align: center"]Single[/TD]
[TD="align: center"]![/TD]
[/TR]
[TR]
[TD="align: center"]Double[/TD]
[TD="align: center"]#[/TD]
[/TR]
[TR]
[TD="align: center"]String[/TD]
[TD="align: center"]$[/TD]
[/TR]
[TR]
[TD="align: center"]Currency[/TD]
[TD="align: center"]@[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot] [/FONT][FONT=&quot]Example using Type-Declaration Character: Dim studentName$ instead of Dim studentName As String[/FONT]