PDA

View Full Version : Formula Update



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

Bob Phillips
03-21-2011, 09:21 AM
Function Class(rng As Range) As String
'Marco used to determine the class of roadway segment V.V

Dim i As Integer
Dim Cl As Variant

If (rng.Value = "F") Then
Class = "F"
ElseIf (rng.Value < 2) And rng.Value <> 0 Then Class = 1
ElseIf (rng.Value >= 2 And rng.Value <= 4.5) Then Class = 2
ElseIf rng.Value = 0 Then Class = 0
Else: Class = 3
End If

End Function


=Class(S2)

nepotist
03-21-2011, 10:40 AM
Thank you XLD.

I have another question, I am trying to write another macro that would look up values based on the arugment supplied. I want the argument value to vary between "S",'M" and "L", can you provide a link that talks about such information. Thank you

Bob Phillips
03-21-2011, 11:14 AM
Just use an argument and a Case structure


Function LookupValue(val as String)

Select Case val

Case "S": 'do something
Case "M": 'do something else
Case "L": 'do something other else
End Select
End Function

nepotist
03-21-2011, 12:15 PM
Thank you!!!