Hi All,
I'm new to VBA and am having trouble trying to get excel 2007 to work
out final headings for a boat that hits a cross current, but can't get
the VBA to work.

I input 2 pieces of data, the Ship heading and the Current heading.
From the Ship data one of 4 quadrants is chosen. This works ok.
Then, for each quadrant and depending on the size of the current
heading relative to the ship heading, one of 4 different actions has
to be taken.
eg 1) Ship=current then final = (180-ship)+current
2) Current ship and current <=180 then final = (180-current)
+ship
3) Current 180 and Current <= (180+ship) then final = ship-
(current-180)
4) Current (180+ship) and Current <=360 then final = (current
-180)-ship

Unfortunately, the relative parameters change for each of the 4
quadrants so each will have to be written separately.

But I can't get this second bit to work: I've tried 'if' and 'case' to
no avail. I only get 1) to work or have to cycle through 1), 2) etc
and choose the correct answer.
The answer from this is then fed into the final equation to work out
the course correction. This bit works fine as a normal Excel equation.

So, any help and suggestions you can offer would be gratefully
received.

Tony

What have you tried so far? Please post your code and we can update that.
--
HTH,
Barb Reinhardt




" wrote:

Hi All,
I'm new to VBA and am having trouble trying to get excel 2007 to work
out final headings for a boat that hits a cross current, but can't get
the VBA to work.

I input 2 pieces of data, the Ship heading and the Current heading.
From the Ship data one of 4 quadrants is chosen. This works ok.
Then, for each quadrant and depending on the size of the current
heading relative to the ship heading, one of 4 different actions has
to be taken.
eg 1) Ship=current then final = (180-ship)+current
2) Current ship and current <=180 then final = (180-current)
+ship
3) Current 180 and Current <= (180+ship) then final = ship-
(current-180)
4) Current (180+ship) and Current <=360 then final = (current
-180)-ship

Unfortunately, the relative parameters change for each of the 4
quadrants so each will have to be written separately.

But I can't get this second bit to work: I've tried 'if' and 'case' to
no avail. I only get 1) to work or have to cycle through 1), 2) etc
and choose the correct answer.
The answer from this is then fed into the final equation to work out
the course correction. This bit works fine as a normal Excel equation.

So, any help and suggestions you can offer would be gratefully
received.

Tony

On 10 Sep, 13:01, Barb Reinhardt
wrote:
What have you tried so far? *Please post your code and we can update that.
--
HTH,
Barb Reinhardt

" wrote:
Hi All,
I'm new to VBA and am having trouble trying to get excel 2007 to work
out final headings for a boat that hits a cross current, but can't get
the VBA to work.

I input 2 pieces of data, the Ship heading and the Current heading.
From the Ship data one of 4 quadrants is chosen. This works ok.
Then, for each quadrant and depending on the size of the current
heading relative to the ship heading, one of 4 different actions has
to be taken.
eg *1) Ship=current then final = (180-ship)+current
* * *2) Current ship and current <=180 then final = (180-current)
+ship
* * *3) Current 180 and Current <= (180+ship) then final = ship-
(current-180)
* * *4) Current (180+ship) and Current <=360 *then final = (current
-180)-ship

Unfortunately, the relative parameters change for each of the 4
quadrants so each will have to be written separately.

But I can't get this second bit to work: I've tried 'if' and 'case' to
no avail. I only get 1) to work or have to cycle through 1), 2) etc
and choose the correct answer.
The answer from this is then fed into the final equation to work out
the course correction. This bit works fine as a normal Excel equation.

So, any help and suggestions you can offer *would be gratefully
received.

Tony

Here's what I've got for the first quadrant. It's probably very
inelegant; I did say I'm new to VBA.

For this to run the same value of Ship has to be used all the time and
less than 89. I've left out the Select Case for Quadrants 2,3,4 as
they do nothing.

Sub datacalc()

Dim Ship, Current, output, Number

Ship = InputBox("Enter Ship Heading: ")
Current = InputBox("Enter Current Heading: ")

Select Case Ship

Case 0 To 89: GoTo Quadrant1:

End Select


Quadrant1:

If Ship = Current Then
GoTo abc:

ElseIf Ship <= Current And Current <= 180 Then
GoTo def:

ElseIf Current 180 And Current <= (Ship + 180) Then
GoTo ghi:

ElseIf Current (Ship + 180) And current <= 360 Then
GoTo jkl:
End If


abc: output1 = (180 - Ship) + Current
MsgBox "output1= " & output1

def: output2 = (180 - Current) + Ship
MsgBox "output2= " & output2

ghi: output3 = Ship - (Current - 180)
MsgBox "output3= " & output3

jkl: output4 = (Current - 180) - Ship
MsgBox "output4= " & output4

End

End Sub

When I run it I get 4 different answers no matter what value of
current I put in for the same value of Ship.

Thanks for your help.

Tony

You're making it harder than it needs to be. Try this

Sub datacalc()

Dim Ship, Current, output, Number

Ship = InputBox("Enter Ship Heading: ")
Current = InputBox("Enter Current Heading: ")

If Ship = Current Then
output = (180 - Ship) + Current
ElseIf Ship <= Current And Current <= 180 Then
output = (180 - Current) + Ship
ElseIf Current 180 And Current <= (Ship + 180) Then
output = Ship - (Current - 180)
ElseIf Current (Ship + 180) And Current <= 360 Then
output = (Current - 180) - Ship
End If

MsgBox ("Output = " & output)

End Sub
--
HTH,
Barb Reinhardt




" wrote:

On 10 Sep, 13:01, Barb Reinhardt
wrote:
What have you tried so far? Please post your code and we can update that.
--
HTH,
Barb Reinhardt

" wrote:
Hi All,
I'm new to VBA and am having trouble trying to get excel 2007 to work
out final headings for a boat that hits a cross current, but can't get
the VBA to work.

I input 2 pieces of data, the Ship heading and the Current heading.
From the Ship data one of 4 quadrants is chosen. This works ok.
Then, for each quadrant and depending on the size of the current
heading relative to the ship heading, one of 4 different actions has
to be taken.
eg 1) Ship=current then final = (180-ship)+current
2) Current ship and current <=180 then final = (180-current)
+ship
3) Current 180 and Current <= (180+ship) then final = ship-
(current-180)
4) Current (180+ship) and Current <=360 then final = (current
-180)-ship

Unfortunately, the relative parameters change for each of the 4
quadrants so each will have to be written separately.

But I can't get this second bit to work: I've tried 'if' and 'case' to
no avail. I only get 1) to work or have to cycle through 1), 2) etc
and choose the correct answer.
The answer from this is then fed into the final equation to work out
the course correction. This bit works fine as a normal Excel equation.

So, any help and suggestions you can offer would be gratefully
received.

Tony

Here's what I've got for the first quadrant. It's probably very
inelegant; I did say I'm new to VBA.

For this to run the same value of Ship has to be used all the time and
less than 89. I've left out the Select Case for Quadrants 2,3,4 as
they do nothing.

Sub datacalc()

Dim Ship, Current, output, Number

Ship = InputBox("Enter Ship Heading: ")
Current = InputBox("Enter Current Heading: ")

Select Case Ship

Case 0 To 89: GoTo Quadrant1:

End Select


Quadrant1:

If Ship = Current Then
GoTo abc:

ElseIf Ship <= Current And Current <= 180 Then
GoTo def:

ElseIf Current 180 And Current <= (Ship + 180) Then
GoTo ghi:

ElseIf Current (Ship + 180) And current <= 360 Then
GoTo jkl:
End If


abc: output1 = (180 - Ship) + Current
MsgBox "output1= " & output1

def: output2 = (180 - Current) + Ship
MsgBox "output2= " & output2

ghi: output3 = Ship - (Current - 180)
MsgBox "output3= " & output3

jkl: output4 = (Current - 180) - Ship
MsgBox "output4= " & output4

End

End Sub

When I run it I get 4 different answers no matter what value of
current I put in for the same value of Ship.

Thanks for your help.

Tony

On 10 Sep, 19:40, Barb Reinhardt
wrote:
You're making it harder than it needs to be. *Try this

Sub datacalc()

Dim Ship, Current, output, Number

Ship = InputBox("Enter Ship Heading: ")
Current = InputBox("Enter Current Heading: ")

If Ship = Current Then
* * output = (180 - Ship) + Current
ElseIf Ship <= Current And Current <= 180 Then
* * output = (180 - Current) + Ship
ElseIf Current 180 And Current <= (Ship + 180) Then
* * output = Ship - (Current - 180)
ElseIf Current (Ship + 180) And Current <= 360 Then
* * output = (Current - 180) - Ship
End If

MsgBox ("Output = " & output)

End Sub
--
HTH,
Barb Reinhardt

" wrote:
On 10 Sep, 13:01, Barb Reinhardt
wrote:
What have you tried so far? *Please post your code and we can update that.
--
HTH,
Barb Reinhardt

" wrote:
Hi All,
I'm new to VBA and am having trouble trying to get excel 2007 to work
out final headings for a boat that hits a cross current, but can't get
the VBA to work.

I input 2 pieces of data, the Ship heading and the Current heading.
From the Ship data one of 4 quadrants is chosen. This works ok.
Then, for each quadrant and depending on the size of the current
heading relative to the ship heading, one of 4 different actions has
to be taken.
eg *1) Ship=current then final = (180-ship)+current
* * *2) Current ship and current <=180 then final = (180-current)
+ship
* * *3) Current 180 and Current <= (180+ship) then final = ship-
(current-180)
* * *4) Current (180+ship) and Current <=360 *then final = (current
-180)-ship

Unfortunately, the relative parameters change for each of the 4
quadrants so each will have to be written separately.

But I can't get this second bit to work: I've tried 'if' and 'case' to
no avail. I only get 1) to work or have to cycle through 1), 2) etc
and choose the correct answer.
The answer from this is then fed into the final equation to work out
the course correction. This bit works fine as a normal Excel equation.

So, any help and suggestions you can offer *would be gratefully
received.

Tony

Here's what I've got for the first quadrant. It's probably very
inelegant; I did say I'm new to VBA.

For this to run the same value of Ship has to be used all the time and
less than 89. I've left out the Select Case for Quadrants 2,3,4 as
they do nothing.

*Sub datacalc()

Dim Ship, Current, output, Number

Ship = InputBox("Enter Ship Heading: ")
Current = InputBox("Enter Current Heading: ")

* * * * Select Case Ship

* * * * Case 0 To 89: GoTo Quadrant1:

* * * * End Select

Quadrant1:

* * * * If Ship = Current Then
* * * * *GoTo abc:

* * * * *ElseIf Ship <= Current And Current <= 180 Then
* * * * *GoTo def:

* * * * * ElseIf Current 180 And Current <= (Ship + 180) Then
* * * * *GoTo ghi:

* * * * *ElseIf Current (Ship + 180) And current <= 360 Then
* * * * *GoTo jkl:
End If

abc: *output1 = (180 - Ship) + Current
* * * * * * * * MsgBox "output1= " & output1

def: output2 = (180 - Current) + Ship
* * * * * * * * MsgBox "output2= " & output2

ghi: output3 = Ship - (Current - 180)
* * * * * * * * MsgBox "output3= " & output3

jkl: output4 = (Current - 180) - Ship
* * * * * * * *MsgBox "output4= " & output4

End

End Sub

*When I run it I get 4 different answers no matter what value of
current I put in for the same value of Ship.

Thanks for your help.

Tony

Hi Barb,

Thanks for your suggestion. I've tried what you suggest and now I only
get the first part to work. The only answer is to output = (180 -
Ship) + Current no matter what
value of current I put in. It's as though the VBA is ignoring the
ElseIf commands. Could my Excel VBA be b-----rd?

Regards

Tony