Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

'#VALUE!' errors.

Status
Not open for further replies.
fiel

fiel

Programmer
Jun 3, 2007
96
US
I'm trying to have a function where 1 cell is based on the values of three cells. Where the final value (LoadValue) is equal to LoadLevel. However I need to LoadValue to return a value such that LoadLevel * DC >= Left. I keep getting a #VALUE! error. My code below is shown below.

Public Function LoadValue(Temperature As Integer, DC As Double, Left As Double) As Variant

'A temporary value to be used is defined
Dim LoadLevel As Double

'Nothing occurs if the ambient temperature is to high
If (DC = "---") Then
result = MsgBox("Improper matching, please select a lower ambient temperature")

LoadValue = 0

Else

Do While (LoadValue * DC) < Left
' For temperatures as 90
If Temperature = 90 Then

LoadLevel = 14

If (LoadValue * DC) < Left Then
LoadLevel = 18
End If

If (LoadValue * DC) < Left Then
LoadLevel = 25
End If

If (LoadValue * DC) < Left Then
LoadLevel = 30
End If

If (LoadValue * DC) < Left Then
LoadLevel = 40
End If

If (LoadValue * DC) < Left Then
LoadLevel = 55
End If

If (LoadValue * DC) < Left Then
LoadLevel = 75
End If

If (LoadValue * DC) < Left Then
LoadLevel = 95
End If

If (LoadValue * DC) < Left Then
LoadLevel = 110
End If

If (LoadValue * DC) < Left Then
LoadLevel = 130
End If

If (LoadValue * DC) < Left Then
LoadLevel = 150
End If

If (LoadValue * DC) < Left Then
LoadLevel = 170
End If

If (LoadValue * DC) < Left Then
LoadLevel = 195
End If

If (LoadValue * DC) < Left Then
LoadLevel = 225
End If

If (LoadValue * DC) < Left Then
LoadLevel = 260
End If

If (LoadValue * DC) < Left Then
LoadLevel = 290
End If

If (LoadValue * DC) < Left Then
LoadLevel = 320
End If

If (LoadValue * DC) < Left Then
LoadLevel = 350
End If

If (LoadValue * DC) < Left Then
LoadLevel = 380
End If

If (LoadValue * DC) < Left Then
LoadLevel = 430
End If

If (LoadValue * DC) < Left Then
LoadLevel = 475
End If

If (LoadValue * DC) < Left Then
LoadLevel = 520
End If

If (LoadValue * DC) < Left Then
LoadLevel = 535
End If

If (LoadValue * DC) < Left Then
LoadLevel = 555
End If

If (LoadValue * DC) < Left Then
LoadLevel = 585
End If

If (LoadValue * DC) < Left Then
LoadLevel = 615
End If

If (LoadValue * DC) < Left Then
LoadLevel = 665
End If

If (LoadValue * DC) < Left Then
LoadLevel = 705
End If

If (LoadValue * DC) < Left Then
LoadLevel = 735
End If

If (LoadValue * DC) < Left Then
LoadLevel = 750
End If

' This is for temperatures at 75
ElseIf Temperature = 75 Then

LoadLevel = 20

If (LoadValue * DC) < Left Then
LoadLevel = 25
End If

If (LoadValue * DC) < Left Then
LoadLevel = 35
End If

If (LoadValue * DC) < Left Then
LoadLevel = 50
End If

If (LoadValue * DC) < Left Then
LoadLevel = 65
End If

If (LoadValue * DC) < Left Then
LoadLevel = 85
End If

If (LoadValue * DC) < Left Then
LoadLevel = 100
End If

If (LoadValue * DC) < Left Then
LoadLevel = 115
End If

If (LoadValue * DC) < Left Then
LoadLevel = 130
End If

If (LoadValue * DC) < Left Then
LoadLevel = 150
End If

If (LoadValue * DC) < Left Then
LoadLevel = 175
End If

If (LoadValue * DC) < Left Then
LoadLevel = 200
End If

If (LoadValue * DC) < Left Then
LoadLevel = 230
End If

If (LoadValue * DC) < Left Then
LoadLevel = 255
End If

If (LoadValue * DC) < Left Then
LoadLevel = 285
End If

If (LoadValue * DC) < Left Then
LoadLevel = 310
End If

If (LoadValue * DC) < Left Then
LoadLevel = 335
End If

If (LoadValue * DC) < Left Then
LoadLevel = 380
End If

If (LoadValue * DC) < Left Then
LoadLevel = 420
End If

If (LoadValue * DC) < Left Then
LoadLevel = 460
End If

If (LoadValue * DC) < Left Then
LoadLevel = 475
End If

If (LoadValue * DC) < Left Then
LoadLevel = 490
End If

If (LoadValue * DC) < Left Then
LoadLevel = 520
End If

If (LoadValue * DC) < Left Then
LoadLevel = 545
End If

If (LoadValue * DC) < Left Then
LoadLevel = 590
End If

If (LoadValue * DC) < Left Then
LoadLevel = 625
End If

If (LoadValue * DC) < Left Then
LoadLevel = 650
End If

If (LoadValue * DC) < Left Then
LoadLevel = 665
End If

' This is for temperatures at 60
ElseIf Temperature = 60 Then

LoadLevel = 20

If (LoadValue * DC) < Left Then
LoadLevel = 25
End If

If (LoadValue * DC) < Left Then
LoadLevel = 30
End If

If (LoadValue * DC) < Left Then
LoadLevel = 40
End If

If (LoadValue * DC) < Left Then
LoadLevel = 55
End If

If (LoadValue * DC) < Left Then
LoadLevel = 70
End If

If (LoadValue * DC) < Left Then
LoadLevel = 85
End If

If (LoadValue * DC) < Left Then
LoadLevel = 95
End If

If (LoadValue * DC) < Left Then
LoadLevel = 110
End If

If (LoadValue * DC) < Left Then
LoadLevel = 125
End If

If (LoadValue * DC) < Left Then
LoadLevel = 145
End If

If (LoadValue * DC) < Left Then
LoadLevel = 165
End If

If (LoadValue * DC) < Left Then
LoadLevel = 195
End If

If (LoadValue * DC) < Left Then
LoadLevel = 215
End If

If (LoadValue * DC) < Left Then
LoadLevel = 240
End If

If (LoadValue * DC) < Left Then
LoadLevel = 260
End If

If (LoadValue * DC) < Left Then
LoadLevel = 280
End If

If (LoadValue * DC) < Left Then
LoadLevel = 320
End If

If (LoadValue * DC) < Left Then
LoadLevel = 355
End If

If (LoadValue * DC) < Left Then
LoadLevel = 385
End If

If (LoadValue * DC) < Left Then
LoadLevel = 400
End If

If (LoadValue * DC) < Left Then
LoadLevel = 410
End If

If (LoadValue * DC) < Left Then
LoadLevel = 435
End If

If (LoadValue * DC) < Left Then
LoadLevel = 455
End If

If (LoadValue * DC) < Left Then
LoadLevel = 495
End If

If (LoadValue * DC) < Left Then
LoadLevel = 520
End If

If (LoadValue * DC) < Left Then
LoadLevel = 545
End If

If (LoadValue * DC) < Left Then
LoadLevel = 560
End If
End If

LoadValue = LoadLevel

Loop

End If

End Function


 
Sort by date Sort by votes




The problam is in this statement...
Code: 
If (DC = "---") Then
DC is Double. You're comparing to TEXT. [red]TILT![/red]

Also what if Temperature is something other than those descrete values in your if statements?

Skip,

[glasses] To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [tongue]
 
Upvote 0 Downvote
Actually I only require those three temperatures. The problem now that I'm getting is if I change between the values for temperature, Excel will freeze on me. Is there some sort of endless loop going on here that I'm not aware of?
 
Upvote 0 Downvote




Put a break in your function, step and observe what happens.

Skip,

[glasses] To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [tongue]
 
Upvote 0 Downvote
Hi,

your loop goes on while (LoadValue * DC) < Left.

ALL your if statements have the same criterion, hence they are ALL TRUE and LoadLevel (and LoadValue) will be constant at 560 (eg. T=60), if this is smaller than left, you have your eternal loop or ALL FALSE, in which case LoadLevel will be 14. Once again eternal loop if smaller then left

Cheers,

Roel



 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BugZap13
  • Locked
  • Question
Excel formula to count occurrences of a value in a different sheet
Replies
5
Views
859
bs6600
bs6600
scubafrog79
  • Locked
  • Question
Formula for checking cells for value and then subtract value 1
Replies
8
Views
544
SkipVought
S
grnzbra
  • Locked
  • Question
Set Cell Equal to Selected Value from Dropdown.
Replies
2
Views
342
combo
combo
PSchubert
  • Locked
  • Question
Excel: Formula only works with certain numerical values
Replies
12
Views
1K
dhookom
dhookom
Rick_Stanich
  • Locked
  • Question
Find the nth occurrence of a string and report value to the left. 1
Replies
5
Views
353
Rick_Stanich
Rick_Stanich

Part and Inventory Search

Sponsor

Back
Top