Making a helper column with preset formula

Dallie

New Member
Joined
Apr 12, 2018
Messages
13
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:
ABC
1
2Car (mins)Truck (mins)
31215=A3*15
41018=A4*15
56=A5*15
68=A6*15

<tbody>
</tbody>

Or if choosing column B the macro will create a helper column in C with the preset formula:
ABC
1
2Car (mins)Truck (mins)
31215=B3*15
41018=B4*15
56
68

<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
 

Some videos you may like

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
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Apr 12, 2018
Messages
13
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
Joined
Sep 21, 2016
Messages
3,050
Office Version
  1. 2016
Platform
  1. Windows
[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 TypeType-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]
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top