AWG Wire Gauge lookup table

[bassplr19]

I'm trying to convert AWG wire gauges to mm2 to check if a wire fits in a wire range. I'm using a lookup table where I've manually populated 14-1000 MCM wires to mm2.

My VBA is working on everything except "1/0" - it's working fine on 2/0, 3/0, and 4/0

Here's where it breaks:
conv_AWG = WorksheetFunction.Lookup(wire, wireTableAWG, wireTablemm)

While checking 4/0, I hover over wire and see that wire="4/0" and then while checking 1/0 right before it breaks I see that wire = "1/0"

 

[Herakles]

I'm trying to convert AWG wire gauges to mm2 to check if a wire fits in a wire range. I'm using a lookup table where I've manually populated 14-1000 MCM wires to mm2.

My VBA is working on everything except "1/0" - it's working fine on 2/0, 3/0, and 4/0

Here's where it breaks:
conv_AWG = WorksheetFunction.Lookup(wire, wireTableAWG, wireTablemm)

While checking 4/0, I hover over wire and see that wire="4/0" and then while checking 1/0 right before it breaks I see that wire = "1/0"

Are you able to use XL2BB to post sample data and appropriate code?

 

[bassplr19]

AWG	mm2
1000	500
750	400
600	300
500	240
300	185
250	150
4/0	107.2193
3/0	85.0288
2/0	67.4309
1/0	53.4751
1	42.4077
2	33.6308
3	26.6705
4	21.1506
5	16.7732
6	13.3018
7	10.5488
8	8.3656
9	6.6342
10	5.2612
11	4.1723
12	3.3088
13	2.624
14	2.0809

Input Power Wire	Drive Range	Column1
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
10	14 - 6	],Table2[@[Drive Range]])]TRUE
8	14 - 6	],Table2[@[Drive Range]])]TRUE
8	14 - 6	],Table2[@[Drive Range]])]TRUE
4	8 - 2	],Table2[@[Drive Range]])]TRUE
4	14 - 2/0	#VALUE!
2	14 - 2/0	#VALUE!
3	6 - 2/0	#VALUE!
2	6 - 2/0	#VALUE!
1/0	2 - 300	#VALUE!
2/0	2 - 300	#VALUE!
4/0	2 - 300	],Table2[@[Drive Range]])]FALSE
(2) 1/0	(2) 3 - 4/0	#VALUE!
(2) 3/0	(2) 2/0 - 300	#VALUE!
(2) 3/0	(2) 2/0 - 300	#VALUE!
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
12	14 - 6	],Table2[@[Drive Range]])]TRUE
8	14 - 6	],Table2[@[Drive Range]])]TRUE
8	14 - 6	],Table2[@[Drive Range]])]TRUE
6	14 - 6	],Table2[@[Drive Range]])]TRUE
4	45140	],Table2[@[Drive Range]])]TRUE
3	14 - 2/0	#VALUE!
2	14 - 2/0	#VALUE!
2	36679	#VALUE!
36526	36679	#VALUE!
36557	2 - 300	#VALUE!
36586	2 - 300	#VALUE!
(2) 1/0	(2) 3 - 4/0	#VALUE!
(2) 2/0	(2) 3 - 4/0	#VALUE!
(2) 3/0	(2) 2/0 - 300	#VALUE!
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
14	14 - 6	],Table2[@[Drive Range]])]TRUE
12	14 - 6	],Table2[@[Drive Range]])]TRUE
10	14 - 6	],Table2[@[Drive Range]])]TRUE
8	45140	],Table2[@[Drive Range]])]TRUE
8	45140	],Table2[@[Drive Range]])]TRUE
6	45140	],Table2[@[Drive Range]])]TRUE
8	45140	],Table2[@[Drive Range]])]TRUE
6	45140	],Table2[@[Drive Range]])]TRUE
4	45140	],Table2[@[Drive Range]])]TRUE
3	36679	#VALUE!
1	2 - 300	#VALUE!
36526	2 - 300	#VALUE!
36557	(2) 3 - 4/0	#VALUE!
(2) 1/0	(2) 3 - 4/0	#VALUE!
(2) 1/0	(2) 3 - 4/0	#VALUE!
(2) 2/0	(2) 2/0 - 300	#VALUE!
(2) 4/0	(2) 2/0 - 300	#VALUE!
(3) 2/0	(4) 2/0 - 300	#VALUE!
(3) 2/0	(4) 2/0 - 300	#VALUE!
(3) 3/0	(4) 2/0 - 300	#VALUE!
(3) 4/0	(4) 2/0 - 300	#VALUE!
(3) 4/0	(4) 2/0 - 300	#VALUE!
(4) 4/0	(4) 3/0 - 300	#VALUE!
(4) 4/0	(4) 4/0 - 300	],Table2[@[Drive Range]])]TRUE
(4) 350	(4) 4/0 - 300	],Table2[@[Drive Range]])]TRUE
(4) 350	(4) 4/0 - 300	],Table2[@[Drive Range]])]TRUE

Function inWireRange(wire As Variant, wireRange As Variant)
    Dim minWire As Variant
    Dim maxWire As Variant
    Dim multWires As Collection
    Dim multRange As Collection
    Dim noWires As Integer
    Dim noRange As Integer
    
    ' Clean wire range
    wireRange = Replace(wireRange, " ", "")
    wire = Trim(wire)
    wireRange = Trim(wireRange)
    
    Set multWires = noCond(wire)
    Set multRange = noCond(wireRange)
    
    ' Find no. conductors and wire size of wire to check
    noWires = multWires(1)
    wire = multWires(2)
    
    ' Find no. conductors and wire range to check against
    noWireRange = multRange(1)
    wireRange = multRange(2)
          
    ' Check no of conductors
    If noWires > noWireRange Then
            inWireRange = False
            Exit Function
    End If
    
    ' Separate out if multiple torque ranges "OR"
    If InStr(wireRange, "OR") > 0 Then
        
        minWire = Left(wireRange, InStr(wireRange, "-") - 1)
        maxWire = Right(wireRange, Len(wireRange) - InStrRev(wireRange, "-"))
    ElseIf InStr(wireRange, "-") > 0 Then 'Split the range into separate variables

        minWire = Left(wireRange, InStr(wireRange, "-") - 1)
        maxWire = Right(wireRange, Len(wireRange) - InStr(wireRange, "-"))
    Else ' If range is only one wire, turn into "Range"
        minWire = wireRange
        maxWire = wireRange
    End If
    
    ' Convert AWG to mm2 for comparison
    minWire = conv_AWG(minWire)
    maxWire = conv_AWG(maxWire)
    wire = conv_AWG(wire)
    

    ' Check order of wire range
    If minWire > maxWire Then
        minWireTemp = maxWire
        maxWire = minWire
        minWire = minWireTemp
    End If
    
    If (wire >= minWire) And (wire <= maxWire) Then
        inWireRange = True
    Else
        inWireRange = False
    End If
        
End Function
' Convert AWG to mm2 for comparison
Function conv_AWG(wire)
    Dim wireTableAWG As Range
    Dim wireTablemm As Range
    
    Set wireTableAWG = Range("AWG_Conv[AWG]")
    Set wireTablemm = Range("AWG_Conv[mm2]")
 
    If IsNumeric(wire) Then
        wire = Int(wire)
    Else
        wire = Trim(wire) ' Make sure there are no spaces
    End If
    
    If InStr(wire, "/") > 0 Then
        wire = Left(wire, 1) + "O"
    End If
 
    conv_AWG = WorksheetFunction.Lookup(wire, wireTableAWG, wireTablemm)
End Function
' Separate no of conductors from wire range
Function noCond(wire)
    Dim outputW As Collection
    Set outputW = New Collection
    
    wire = Replace(wire, " ", "")
    
    If InStr(wire, "(") > 0 Then
        noWires = Mid(wire, InStr(wire, ")") - 1, 1)
        wireRange = Right(wire, Len(wire) - InStr(wire, ")"))
    Else
        noWires = 1
        wireRange = wire
    End If
    
    outputW.Add noWires
    outputW.Add wireRange
    
    Set noCond = outputW
End Function

 

[bassplr19]

Strike out

If InStr(wire, "/") > 0 Then
        wire = Left(wire, 1) + "O"
    End If

I was experimenting

 

[bassplr19]

I cheated:

    If wire = "1/0" Then
        conv_AWG = 53.471
    ElseIf wire = "2/0" Then
        conv_AWG = 67.4309
    ElseIf wire = "3/0" Then
        conv_AWG = 85.0288
    ElseIf wire = "4/0" Then
        conv_AWG = 107.2193
    Else
        conv_AWG = WorksheetFunction.Lookup(wire, wireTableAWG, wireTablemm)
    End If