Inserting formula into dynamic column with dynamic rows

Topher V

New Member
Joined
Jan 8, 2010
Messages
4
I would like to insert a formula into the next empty column in a sheet and autofill down until Column A is blank. Thoughts?
 

Topher V

New Member
Joined
Jan 8, 2010
Messages
4
=VLOOKUP(A2,Attempts!$A:$B,2,FALSE)

I should probably mention that I'm looking for VBA code to do this.
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
Sub InsFmla()
Dim LR As Long, LC As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, LC + 1), Cells(LR, LC + 1)).Formula = "=VLOOKUP(A2,Attempts!$A:$B,2,FALSE)"
End Sub
 

Topher V

New Member
Joined
Jan 8, 2010
Messages
4
That works except it does not move to a different column once column B is used. Basically, every day I want the data to go into a new column. Is there a way to have the macro search for the next open column?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Do you have data in row 1. If not what is the first used row?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
In that case maybe

Rich (BB code):
Sub InsFmla()
Dim LR As Long, LC As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
LC = Cells(2, Columns.Count).End(xlToLeft).Column
Range(Cells(2, LC + 1), Cells(LR, LC + 1)).Formula = "=VLOOKUP(A2,Attempts!$A:$B,2,FALSE)"
End Sub
 

Forum statistics

Threads
1,082,631
Messages
5,366,665
Members
400,910
Latest member
TywithXl

Some videos you may like

This Week's Hot Topics

Top