Good afternoon,
I put together something that take five user inputs, and then performs a series of find operations to locate the correct values from pre-existing tables. These values are then used to modify an original value, which is finally referenced against one of two tables to determine the correct sizing.
However, I am getting a runtime error (Type mismatch), which seems to be originating from the bolded text below based observations from on breakpoints, watching, and stepping into the code. While searching through this and other forums, I know a number of people struggle with .find and range issues, but I am not sure if that is the case here. Can someone help me out?
I am sure there is a lot more that can be done to improve my very sloppy code, but I don't mind working through that myself, I am just stumped by this run-time error.
Much appreciated!
-SD
I put together something that take five user inputs, and then performs a series of find operations to locate the correct values from pre-existing tables. These values are then used to modify an original value, which is finally referenced against one of two tables to determine the correct sizing.
However, I am getting a runtime error (Type mismatch), which seems to be originating from the bolded text below based observations from on breakpoints, watching, and stepping into the code. While searching through this and other forums, I know a number of people struggle with .find and range issues, but I am not sure if that is the case here. Can someone help me out?
Code:
Option Explicit
Sub runCalc()
'General user inputs'
Dim location As String
Dim numCon As Integer
Dim ambTemp As String 'ambTemp is a string as the base case is "30 or less", and the value isn't used in a calculation, just as a selector.'
Dim lineAmp As Double
Dim cableRating As Integer
'Worksheet values used in case selector'
Dim cableTable As Worksheet
Dim tableType As Worksheet
Dim caseType As Integer
'For cell selectors'
Dim condRng As Range
Dim tempRng As Range
Dim cableTempRng As Range
Dim cableTableRng As Range
Dim cableTableRow As Integer
'Doubles for actual calcs'
Dim adjustedAmpage As Double
Dim tempCorr As Double
Dim condCorr As Double
[B]'Type mismatch appears to occur at at lineamp'[/B]
[B] lineAmp = Worksheets(Sheet1).Cell("E2").Value[/B]
location = Worksheets(Sheet1).Cell("E3").Value
numCon = Worksheets(Sheet1).Cell("E4").Value
ambTemp = Worksheets(Sheet1).Cell("E5").Value
cableRating = Worksheets(Sheet1).Cell("E6").Value
If location = "Free air" Then
cableTable = "Table 1"
tableType = "5B"
caseType = 1
'Check for conductor limitations.'
If numCon > 4 Then
numCon = 4
End If
Else
cableTable = "Table 2"
tableType = "5C"
caseType = 2
End If
Select Case caseType
'Case 1 is for a open air conductors, and cannot be greater than 4'
'Removed all After:= cases'
Case 1
If numCon = 1 Then
condCorr = 1
Else
With tableType.Range("A:A")
Set condRng = .Find(What:=numCon, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not condRng Is Nothing Then
MsgBox condRng.Row
Else
MsgBox "Value not found!"
End If
End With
condCorr = Cells(condRng.Row, condRng.Column + 2).Value
End If
If ambTemp = "30 or less" Then
tempCorr = 1
Else
With Sheets("Temperature Table").Range("A:A")
Set tempRng = .Find(What:=ambTemp, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not tempRng Is Nothing Then
MsgBox tempRng.Row
Else
MsgBox "Value not found!"
End If
End With
With Sheets("Temperature Table").Range("1:1")
Set cableTempRng = .Find(What:=cableRating, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cableTempRng Is Nothing Then
MsgBox cableTempRng.Column
Else
MsgBox "Value not found!"
End If
End With
tempCorr = Cells(tempRng.Row, cableTempRng.Column).Value
End If
adjustedAmpage = lineAmp / (tempCorr * condCorr)
'Case 2 is for covered conductors'
Case 2
If numCon <= 3 Then
condCorr = 1
Else
With tableType.Range("B:B")
Set condRng = .Find(What:=.Cell.Value <= numCon, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not condRng Is Nothing Then
MsgBox condRng.Row
Else
MsgBox "Value not found!"
End If
End With
condCorr = Cells(condRng.Row, condRng.Column + 1).Value
End If
If ambTemp = "30 or less" Then
tempCorr = 1
Else
With Sheets("Temperature Table").Range("A:A")
Set tempRng = .Find(What:=ambTemp, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not tempRng Is Nothing Then
MsgBox tempRng.Row
Else
MsgBox "Value not found!"
End If
End With
With Sheets("Temperature Table").Range("1:1")
Set cableTempRng = .Find(What:=cableRating, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cableTempRng Is Nothing Then
MsgBox cableTempRng.Column
Else
MsgBox "Value not found!"
End If
End With
tempCorr = Cells(tempRng.Row, cableTempRng.Column).Value
End If
adjustedAmpage = lineAmp / (tempCorr * condCorr)
End Select
With cableTable.Range("2:2")
Set cableTableRng = .Find(What:=cableRating, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
For cableTableRow = 1 To Range(cableTable).End(xlDown).Row - 1
If Cells(cableTableRow, cableTableRng.Column).Value >= (adjustedAmpage / 0.8) Then
Sheets(Sheet1).Cell("E11").Value = Cells(cableTableRow, "A").Value
Exit For
End If
Next
End SubI am sure there is a lot more that can be done to improve my very sloppy code, but I don't mind working through that myself, I am just stumped by this run-time error.
Much appreciated!
-SD
