Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
YTD budget formula based on current actual input | Excel Worksheet Functions | |||
formula pulling data based on 1 input on another tab. | Excel Worksheet Functions | |||
insert a picture based on a formula | Excel Worksheet Functions | |||
Macro to insert cells based on an input | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |