Insert formula based on input box
 

There are two sheets: "Hours" and "Table"
In my program, there is an input box that pops up and asks the user for a
date. This date will be inputted into $A$1 of the Hours tab.

In the Table tab, there is a list of employees and all the dates for the
year. (Employees in columns, and dates in the rows).

If the date in Column A of the Table tab matches the date in $A$1 of the
Hours tab, I want to enter a lookup formula in each column of the row that
matches (but ONLY in that particular row).

For example:

A B C D E
Ed Matt Dave Sam
1/1/09
1/2/09
1/3/09 "formula" "formula" "formula" "formula"
1/4/09

So, when the user entered "1/3/09" in the input box on the Hours tab for
$a$1, it found it on the Table tab and entered the formula for the entire row
that 1/3/09 was found on.

(if it helps, the formula is going to be something along the lines of
=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HOU RS!$A:$A,0)),"")
basically just to return the total hours after matching the employee number
and the date.

I started writing some kind of looping search, but it doesn't seem to work.

Any help is appreciated!

Insert formula based on input box
 

Try :

Sub test()
Dim Dat As Date, myRow As Long, i As Long, Col As Integer
Col = Range("IV1").End(xlToLeft).Column
Dat = CDate(InputBox("Enter date"))
If IsNumeric(Application.Match(Dat * 1, [A2:A65000], 0)) Then
[A1] = Dat
myRow = Application.Match(Dat * 1, [A2:A65000], 0) + 1
For i = 2 To Col
Cells(myRow, i).Formula = _

"=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HO URS!$A:$A,0)),"""")"
Next i
End If
End Sub

Regards.
Daniel

There are two sheets: "Hours" and "Table"
In my program, there is an input box that pops up and asks the user for a
date. This date will be inputted into $A$1 of the Hours tab.

In the Table tab, there is a list of employees and all the dates for the
year. (Employees in columns, and dates in the rows).

If the date in Column A of the Table tab matches the date in $A$1 of the
Hours tab, I want to enter a lookup formula in each column of the row that
matches (but ONLY in that particular row).

For example:

A B C D E
Ed Matt Dave Sam
1/1/09
1/2/09
1/3/09 "formula" "formula" "formula" "formula"
1/4/09

So, when the user entered "1/3/09" in the input box on the Hours tab for
$a$1, it found it on the Table tab and entered the formula for the entire row
that 1/3/09 was found on.

(if it helps, the formula is going to be something along the lines of
=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HOU RS!$A:$A,0)),"")
basically just to return the total hours after matching the employee number
and the date.

I started writing some kind of looping search, but it doesn't seem to work.

Any help is appreciated!

Insert formula based on input box
 

Thanks for your help. Just as I read your response, I finally got my loop
program as follows:
Sub tester()
Sheets("Table").Select
Range("A2").Select
Do Until ActiveCell.Value = Range("A1").Value
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1=HOURS!R1C1,INDEX(HOURS!C7,MATCH(R1C,HOURS !C1,0)),"""")"
ActiveCell.Offset(0, 1).Select
Selection.FillRight
End Sub

The only problem now is that I need to figure out how to autofill to the
right 120 columns - the rows will be variable so I am not sure how to do it.
"Daniel.C" wrote:

Try :

Sub test()
Dim Dat As Date, myRow As Long, i As Long, Col As Integer
Col = Range("IV1").End(xlToLeft).Column
Dat = CDate(InputBox("Enter date"))
If IsNumeric(Application.Match(Dat * 1, [A2:A65000], 0)) Then
[A1] = Dat
myRow = Application.Match(Dat * 1, [A2:A65000], 0) + 1
For i = 2 To Col
Cells(myRow, i).Formula = _

"=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HO URS!$A:$A,0)),"""")"
Next i
End If
End Sub

Regards.
Daniel

There are two sheets: "Hours" and "Table"
In my program, there is an input box that pops up and asks the user for a
date. This date will be inputted into $A$1 of the Hours tab.

In the Table tab, there is a list of employees and all the dates for the
year. (Employees in columns, and dates in the rows).

If the date in Column A of the Table tab matches the date in $A$1 of the
Hours tab, I want to enter a lookup formula in each column of the row that
matches (but ONLY in that particular row).

For example:

A B C D E
Ed Matt Dave Sam
1/1/09
1/2/09
1/3/09 "formula" "formula" "formula" "formula"
1/4/09

So, when the user entered "1/3/09" in the input box on the Hours tab for
$a$1, it found it on the Table tab and entered the formula for the entire row
that 1/3/09 was found on.

(if it helps, the formula is going to be something along the lines of
=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HOU RS!$A:$A,0)),"")
basically just to return the total hours after matching the employee number
and the date.

I started writing some kind of looping search, but it doesn't seem to work.

Any help is appreciated!

Insert formula based on input box
 

My code has a second loop just to do this.
Daniel

Thanks for your help. Just as I read your response, I finally got my loop
program as follows:
Sub tester()
Sheets("Table").Select
Range("A2").Select
Do Until ActiveCell.Value = Range("A1").Value
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = _
"=IF(RC1=HOURS!R1C1,INDEX(HOURS!C7,MATCH(R1C,HOURS !C1,0)),"""")"
ActiveCell.Offset(0, 1).Select
Selection.FillRight
End Sub

The only problem now is that I need to figure out how to autofill to the
right 120 columns - the rows will be variable so I am not sure how to do it.
"Daniel.C" wrote:

Try :

Sub test()
Dim Dat As Date, myRow As Long, i As Long, Col As Integer
Col = Range("IV1").End(xlToLeft).Column
Dat = CDate(InputBox("Enter date"))
If IsNumeric(Application.Match(Dat * 1, [A2:A65000], 0)) Then
[A1] = Dat
myRow = Application.Match(Dat * 1, [A2:A65000], 0) + 1
For i = 2 To Col
Cells(myRow, i).Formula = _

"=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HO URS!$A:$A,0)),"""")"
Next i
End If
End Sub

Regards.
Daniel

There are two sheets: "Hours" and "Table"
In my program, there is an input box that pops up and asks the user for a
date. This date will be inputted into $A$1 of the Hours tab.

In the Table tab, there is a list of employees and all the dates for the
year. (Employees in columns, and dates in the rows).

If the date in Column A of the Table tab matches the date in $A$1 of the
Hours tab, I want to enter a lookup formula in each column of the row that
matches (but ONLY in that particular row).

For example:

A B C D E
Ed Matt Dave Sam
1/1/09
1/2/09
1/3/09 "formula" "formula" "formula" "formula"
1/4/09

So, when the user entered "1/3/09" in the input box on the Hours tab for
$a$1, it found it on the Table tab and entered the formula for the entire
row that 1/3/09 was found on.

(if it helps, the formula is going to be something along the lines of
=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HOU RS!$A:$A,0)),"")
basically just to return the total hours after matching the employee number
and the date.

I started writing some kind of looping search, but it doesn't seem to work.

Any help is appreciated!

Insert formula based on input box
 

You're right. Thanks for the help!

"Daniel.C" wrote:

Try :

Sub test()
Dim Dat As Date, myRow As Long, i As Long, Col As Integer
Col = Range("IV1").End(xlToLeft).Column
Dat = CDate(InputBox("Enter date"))
If IsNumeric(Application.Match(Dat * 1, [A2:A65000], 0)) Then
[A1] = Dat
myRow = Application.Match(Dat * 1, [A2:A65000], 0) + 1
For i = 2 To Col
Cells(myRow, i).Formula = _

"=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HO URS!$A:$A,0)),"""")"
Next i
End If
End Sub

Regards.
Daniel

There are two sheets: "Hours" and "Table"
In my program, there is an input box that pops up and asks the user for a
date. This date will be inputted into $A$1 of the Hours tab.

In the Table tab, there is a list of employees and all the dates for the
year. (Employees in columns, and dates in the rows).

If the date in Column A of the Table tab matches the date in $A$1 of the
Hours tab, I want to enter a lookup formula in each column of the row that
matches (but ONLY in that particular row).

For example:

A B C D E
Ed Matt Dave Sam
1/1/09
1/2/09
1/3/09 "formula" "formula" "formula" "formula"
1/4/09

So, when the user entered "1/3/09" in the input box on the Hours tab for
$a$1, it found it on the Table tab and entered the formula for the entire row
that 1/3/09 was found on.

(if it helps, the formula is going to be something along the lines of
=IF($A4=HOURS!$A$1,INDEX(HOURS!$G:$G,MATCH(B$1,HOU RS!$A:$A,0)),"")
basically just to return the total hours after matching the employee number
and the date.

I started writing some kind of looping search, but it doesn't seem to work.

Any help is appreciated!