nepotist
03-21-2011, 08:27 AM
Hello,
I wrote a macro to avoid length formulas in cells. The macro does return correct values when I type in the Userdefined function in a cell but copies the previous value when I try to double click the right side bottom corner of the current cell to update the entire column.
Can someone help me by pointing out what is wrong or what am I missing.
Function Class() As String
'Marco used to determine the class of roadway segment V.V
Dim i As Integer
Dim Cl As Variant
i = ActiveCell.Row
Cl = Cells(i, 19).Value
If (Cl = "F") Then
Class = "F"
ElseIf (Cl < 2) And Cl <> 0 Then Class = 1
ElseIf (Cl >= 2 And Cl <= 4.5) Then Class = 2
ElseIf Cl = 0 Then Class = 0
Else: Class = 3
End If
End Function
Function Facility_Type() As String
' MACRO used to determine the facility type of the roadwaysegment V.V
Dim i As Integer
i = ActiveCell.Row
Dim Area As String
Area = Cells(i, 16).Value
Dim Fac_Type As String
Dim Clas As String
Clas = Class()
If Clas = "F" Then
Fac_Type = "FW"
ElseIf (Cells(i, 16) = "UA" Or Cells(i, 16) = "TA") And Clas <> 0 Then
Fac_Type = "S2WAC" & Cla
ElseIf (Cells(i, 16) = "UA" Or Cells(i, 16) = "TA") And Clas = 0 Then
Fac_Type = "UFH"
ElseIf (Cells(i, 16) = "RUA" Or Cells(i, 16) = "RDA") And Clas <> 0 Then
Fac_Type = "IFH"
ElseIf (Cells(i, 16) = "RUA" Or Cells(i, 16) = "RDA") And Clas = 0 Then
Fac_Type = "UFH"
End If
Facility_Type = Fac_Type
End Function
Thank you
I wrote a macro to avoid length formulas in cells. The macro does return correct values when I type in the Userdefined function in a cell but copies the previous value when I try to double click the right side bottom corner of the current cell to update the entire column.
Can someone help me by pointing out what is wrong or what am I missing.
Function Class() As String
'Marco used to determine the class of roadway segment V.V
Dim i As Integer
Dim Cl As Variant
i = ActiveCell.Row
Cl = Cells(i, 19).Value
If (Cl = "F") Then
Class = "F"
ElseIf (Cl < 2) And Cl <> 0 Then Class = 1
ElseIf (Cl >= 2 And Cl <= 4.5) Then Class = 2
ElseIf Cl = 0 Then Class = 0
Else: Class = 3
End If
End Function
Function Facility_Type() As String
' MACRO used to determine the facility type of the roadwaysegment V.V
Dim i As Integer
i = ActiveCell.Row
Dim Area As String
Area = Cells(i, 16).Value
Dim Fac_Type As String
Dim Clas As String
Clas = Class()
If Clas = "F" Then
Fac_Type = "FW"
ElseIf (Cells(i, 16) = "UA" Or Cells(i, 16) = "TA") And Clas <> 0 Then
Fac_Type = "S2WAC" & Cla
ElseIf (Cells(i, 16) = "UA" Or Cells(i, 16) = "TA") And Clas = 0 Then
Fac_Type = "UFH"
ElseIf (Cells(i, 16) = "RUA" Or Cells(i, 16) = "RDA") And Clas <> 0 Then
Fac_Type = "IFH"
ElseIf (Cells(i, 16) = "RUA" Or Cells(i, 16) = "RDA") And Clas = 0 Then
Fac_Type = "UFH"
End If
Facility_Type = Fac_Type
End Function
Thank you