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:

	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

 

[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]

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]