variable location for a formula

[spacecaptainsuperguy]

I am trying to automate a report that is calculating a payroll expense and whether or not it is over budget or under budget.

The way I envision this working is 2 input boxes collecting information about the particular day or week's payroll budget to then put in cell H40 (in this example...H40 is variable). In this example the formula in H40 is =190.29 x 6 which is the daily payroll number (input #1) times the number of days in the pay period (input #2). Where this formula will end up is variable based on how many people worked and the number of days gone by etc. What I assume would be the easiest way would be to have the code look in column B for 'Totals Department: FB' and then generate that formula (input#1 x input#2) six cells to the right in column H. I am not yet familiar enough with VBA to figure out how to go about setting something like that up. Any help you can offer is greatly appreciated.

SpaceCaptainSuperGuy

F&B Current Payroll.xls
	A	B	C	D	E	F	G	H	I	J
34	TS	Smith,Taylor-Jean	FB	FWAIT	6.25	25.00	Apr15/2008
35			FB	FWAIT	7.50	30.00	Apr17/2008
36			FB	FWAIT	8.25	33.00	Apr18/2008
37			FB	FWAIT	10.00	40.00	Apr19/2008
38			FB	FWAIT	4.25	17.00	Apr20/2008
39		TotalsJob/Function:FWAIT			218.50	1,163.92
40		TotalsDepartment:FB			218.50	1,163.92		1,141.74	-22.18	OverBudget
JNS5252

 

[xld]

Sub CreateFormula()
Dim inp1 As Double, inp2 As Double

    With ActiveSheet
    
        Set cell = .Columns(2).Find("Totals Department: FB", LookIn:=xlValues)
        If Not cell Is Nothing Then
        
            inp1 = Application.InputBox("Supply value #1", Type:=1)
            If inp1 <> 0 Then
        
                inp2 = Application.InputBox("Supply value #2", Type:=1)
                If inp2 <> 0 Then
                
                    cell.Offset(0, 6).Formula = "=" & inp1 & "*" & inp2
                End If
            End If
        End If
    End With
End Sub

 

[Gates Is Antichrist]

Don't set the formula in code. Set the (H40) formula to reference two cells and set the two cells in the code. Let H40 move up and down as inserting and deleting rows dictate, and the code need not worry about it.

If you name cell I1 as Input1 and J1 as Input2 then your formula will be
=Input1 * Input2
and the code will essentially go
range("Input1") = inputbox("Enter first parameter")

I recommend error checking/handling.

 

[spacecaptainsuperguy]

Wow. Short and sweet. It works great. Thank you very much XLD. Also thank you Gates. I will check to see if your method works as well.

SpaceCap.

 

[Gates Is Antichrist]

Good. I'm not disagreeing with XLD's answer, as he did exactly what you asked for, and accurately. I'm just suggesting that another approach may be more versatile. With the range names, you can insert and delete rows to your heart's content, but the ranges will still work - or they may never move at all. AAR you won't have to care where they are after you initially create them.

Meanwhile, you won't care where H40 is either now. It won't be "bound in matrimony" to being 5 columns away from the text string. And the text string can be changed, even if just to add whitespace. In each case, the code won't break with this approach.

But XLD's answer is still fine