How to turn Functions into Code

[autigers]

I have the following Formula that I wish to put in code and then transfer the sums to a seperate sheet in the same workbook. After the data tranfer is complete, I would like to clear the input sheet ....
=IF(C2="","",IF
(E2="","",IF(E2
<D2,IF(F2="",
E2+1-D2,(E2+
1)-(D2+$V$2)
),IF(F2="",E2-
D2,D2-(E2+
$V$2)))))

Any help would be greatly appreciated.

 

[autigers]

I was unable to place a screen print due to workplace restrictions on file downloads.
I have tried everything I can find on this forum ... (probably don't understand it) ...
Thanks for your help ...

 

[GlennUK]

I don't understand ... you say you want code to loop through the formula and perform the math and put the results somewhere else ( or is it in the same place??? ), which is the same as saying let Excel calculate the results of formulae, and do a copy/Edit/Paste Special Values action isn't it?

 

[autigers]

okay ...
I enter times in colums D & E. In Column G is where the If takes place to calculate the time in that row(s) only.
What I am trying to achieve first is this ...
Code - instead of formulas that would do the same thing as the formula in this thread ....

I hope that makes sense ....
After getting this first step complete then move on to relocating the data entered ...

Thank you

 

[autigers]

I have tried this but it keeps putting the formula all the way across the sheet ... arrrgggghhh...

Private Sub Worksheet_Activate()
Cells(1, 1).Value = "Date"
Cells(1, 2).Value = "Scheduled"
Cells(1, 3).Value = "Overtime"
Cells(1, 4).Value = "Others"
Cells(1, 5).Value = "Light Duty"
Cells(1, 6).Value = "STD"
Cells(1, 7).Value = "Vacation"
Cells(1, 8).Value = "Call Off"
Cells(1, 9).Value = "Temp Assign"
Cells(1, 10).Value = "Total"
Range("A2").Select
Do
    If IsEmpty(ActiveCell) = False Then
        ActiveCell.Offset(, 9)
        ActiveCell.Value = "=(B2+C2+D2+E2)-(F2+G2+H2+I2)"
    End If
Loop Until IsEmpty(ActiveCell) = True
End Sub

 

[autigers]

OMG !!! I must apologize as I have been on this thing for hours now !!! That obviously isn't the problem we are discussing .... very similar but not the right one ....

My apologies ...

 

[tusharm]

To do this task by hand, select G2, hold down SHIFT and click the down-arrow button. Copy. Right click | Paste special... values only.

If you must do with with code, turn on the macro recorder before doing the above. Then, generalize the code as needed.

An alternative would be the untested

    with Range(Range("G2"),Range("G2").end(xldown))
    .value=.value
        end with

The latter ...
It now resides in every row in column G. From Rows G2:G150.
I would like to have the code loop through the range in the formula and perform the math placing the results in
Same Worksheet "DailyInput" Column G.

I will try to provide a screen shot of the sheet .... Visual is sometimes better.

Thanks

 

[autigers]

I guess that will have to do ... I was looking for something more on the lines of a change evnt or something ....

Okay ... next step
I can use a SUMIF formula to calculate an associates total hours for a shift as I currently do. I prefer to have this hard coded in.
(The idea behind everything being coded is that the workbook I have been using is shared on network. We have had a large numbers of links setting themselves to other locations which in turn messes with other links associated to the workbook ....

So .... The SUMIF works great but as I said if there were a way to code all of this that would be better. Plus I notice that my file size increases drastically when using formulas versus the code...
I am including a screenshot

TimeTrac.xls
	A	B	C	D	E	F	G	H	I	J
1	WMS Logon	OT	Job	Start	Finish	Lunch	Job Hours	Total Hours	November 22, 2005
2	APPJCL		D	7:45 PM	12:00 AM		4:15	4:15:00
3	APPJMO3		DC	7:45 PM	5:45 AM	X	9:30
4			UR
DailyInput

 

[autigers]

Does anyone think this can be done ?

 

[acw]

Hi

You could use the code to put the formula into G2, fill down, copy and paste values. Same for the formula in H2. Have the code enter the formula, then copy value paste the result.

If the range is dependent on data being placed in column G, then it could be added to a worksheet change event. How / what would you expect to be the trigger for any action???


Tony

 

[autigers]

Hi ACW,
Thank you for your patience
The trigger for column G would need to occur when a value is placed into cell in column F (Finish Time) ...
Column will be triggered once a value is found in G ....