help with formula/program?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

This may or may not be a programming question... it could be a straight excel formula question.

I have a worksheet containing two columns for Onshore Hours and for Offshore hours

I want it so that I can enter Onshore hours, and if there are any offshore hours, i want it to subtract onshore hours from offshore hours and list it in the onshore hours cell

Example

Onshore Hours Offshore Hour
Row 1: 500 0
(Here i entered in 500 onshore hours. I have entered 0 offshore hours and thus the 500 i entered stays!
Row 2: 500 5
(Here i entered in 500 onshore hours. I have also entered 50 offshore hours. I want it so that my 500 onshore hours automatically changes to 450 (500-50; onshore - offshore hours

Those this make sense? Can i do it with simple excel formulas or does it require VB? I assume it is simple but I have no idea where to start. Any help would be greately appreciated
 
right click on the worksheet tab and select view code.

In the resulting module put in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Row <> 1 And Target.Column = 1 Then
Application.EnableEvents = False
Target.Value = Target.Value - Target.Offset(0, 1).Value
End If
ErrHandler:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

Terrel said:
Hello,

This may or may not be a programming question... it could be a straight excel formula question..

I have a worksheet containing two columns for Onshore Hours and for Offshore hours.

I want it so that I can enter Onshore hours, and if there are any offshore
Click to expand...
hours, i want it to subtract onshore hours from offshore hours and list it
in the onshore hours cell.
Example:

Onshore Hours Offshore Hours
Row 1: 500 0
(Here i entered in 500 onshore hours. I have entered 0 offshore hours and thus the 500 i entered stays!)
Row 2: 500 50
(Here i entered in 500 onshore hours. I have also entered 50 offshore
Click to expand...
hours. I want it so that my 500 onshore hours automatically changes to 450
(500-50; onshore - offshore hours)
Those this make sense? Can i do it with simple excel formulas or does it
Click to expand...
require VB? I assume it is simple but I have no idea where to start. Any
help would be greately appreciated.
 
Whoops, I see you have data in row 1 as well instead of your labels (onshore
hours and offshore hours). So remove the restriction for row 1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Column = 1 Then ' <= alter this line
Application.EnableEvents = False
Target.Value = Target.Value - Target.Offset(0, 1).Value
End If
ErrHandler:
Application.EnableEvents = True

End Sub

--
Regards,
Tom Ogilvy

Terrel said:
Hello,

This may or may not be a programming question... it could be a straight excel formula question..

I have a worksheet containing two columns for Onshore Hours and for Offshore hours.

I want it so that I can enter Onshore hours, and if there are any offshore
Click to expand...
hours, i want it to subtract onshore hours from offshore hours and list it
in the onshore hours cell.
Example:

Onshore Hours Offshore Hours
Row 1: 500 0
(Here i entered in 500 onshore hours. I have entered 0 offshore hours and thus the 500 i entered stays!)
Row 2: 500 50
(Here i entered in 500 onshore hours. I have also entered 50 offshore
Click to expand...
hours. I want it so that my 500 onshore hours automatically changes to 450
(500-50; onshore - offshore hours)
Those this make sense? Can i do it with simple excel formulas or does it
Click to expand...
require VB? I assume it is simple but I have no idea where to start. Any
help would be greately appreciated.
 
Hey Tom

I tested your code and it works great. I need some help with some revisions to the code though

My spreadsheet is more indepth.

What I have i

JANUARY FEBRUARY... DECEMBE
ON OFF ON OFF ON .... ON OF
A B C D E F ... X
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If target.row = 14 or target.row = 16 then
If Target.Column mod 2 = 0 and Target.column < 25 Then
if not isempty(target) then
Application.EnableEvents = False
Target.Value = Target.Value - Target.Offset(0, 1).Value
End if
End If
End if
ErrHandler:
Application.EnableEvents = True

End Sub

worked for me.

--
Regards,
Tom Ogilvy


Terrel said:
Hey Tom,

I tested your code and it works great. I need some help with some revisions to the code though.

My spreadsheet is more indepth.

What I have is

JANUARY FEBRUARY... DECEMBER
ON OFF ON
Click to expand...
OFF ON .... ON OFF
A B C D
Click to expand...
E F ... X Y
.
.
.
Row 14 blah 500 50 430 30...
Row 15 blah blah blah blah blah...
Row 16 blah 400 0 500 40...
.
.
.

Basically my spreadsheet covers all 12 months of the year. Each Month has
Click to expand...
a column for onshore hours and offshore hours. And the two rows that I
want this code to work with are row 14 and row 16 only. What should I do to
make the code you gave me to work for each month on tow 14 and 16?
 
I want it so that I can enter Onshore hours, and if there are any offshore
hours, i want it to subtract onshore hours from offshore >hours and list it
in the onshore hours cell.

Seems very clear. You now want the exact opposite of what you said.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
If target.row = 14 or target.row = 16 then
If Target.Column mod 2 = 1 and Target.column < 26 and Target.column > 1
Then
if not isempty(target) and not isempty(Target.offset(0,-1)) then
Application.EnableEvents = False
Target.Offset(0,-1).Value = Target(0,-1).Value - Target.Value
End if
End If
End if
ErrHandler:
Application.EnableEvents = True
End Sub

Modifications untested.

--
Regards,
Tom Ogilvy


Terrel said:
hmm

The code works... however...

Usually we are given the onshore hours.. And We get the details of
Click to expand...
offshore hours later on and thus when we add in offshore hours, we want the
onshore hours to change automatically..
In this code... it assumes that we are given offshore hours first and then
Click to expand...
we put in onshore hours... and it substracts itself... so logically it is
not what i need...hmm. What i need is: Onshore hours already exists. If i
add an offshore hour, I then want the onshore hours to change automatically
by subtracting what already exists by the offshore hour in the column
adjacent to it.
I apologize for being unclear. I think its a few quick lines to fix..
Click to expand...
i'll try play around with it.. If you can help out again.. then cool. I
feel like i've bothered you a lot already hehe. Thanks for your
assistance.
 
Back
Top