# Insert formula based on input box

K

#### Keep It Simple Stupid

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,HOURS!\$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!

D

#### Daniel.C

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,HOURS!\$A:\$A,0)),"""")"
Next i
End If
End Sub

Regards.
Daniel

K

#### Keep It Simple Stupid

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 said:
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,HOURS!\$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,HOURS!\$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!

D

#### Daniel.C

My code has a second loop just to do this.
Daniel
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 said:
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,HOURS!\$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,HOURS!\$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!

K

#### Keep It Simple Stupid

You're right. Thanks for the help!

Daniel.C said:
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,HOURS!\$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,HOURS!\$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!