VBA Formula

[Guest]

Hello from Steved

The Below formula is in Cell B6

Question is it possible to have this in visual basic, the reason I ask is
that I cut a paste and need to retype the formula each time, i do the pasting.

=IF(ISNA(VLOOKUP(--A6,'All Bus Models'!$A$5:$E$5000,4,FALSE)),"Out Of
Service",VLOOKUP(--A6,'All Bus Models'!$A$5:$E$5000,4,FALSE))
Thanks

 

[Guest]

Steved,

It quite an easy function:

' lookup is the value to lookup call it
' =busmodel("Routemaster R2RH")
Public Function BusModel(lookup) as String
Const wsn As String = "All Bus Models"
Const r As String = "$A$5:$E$5000"
On Error GoTo error_line
BusModel = WorksheetFunction.VLookup(lookup, _
Worksheets(wsn).Range(r), 4, False)
Exit Function
error_line:
BusModel = "Out Of Service "
End Function

you can change it to supply addresses in the call to the function rather
than hard
code them in the function.

 

[Guest]

Thanks very much Martin.

Steved,

It quite an easy function:

' lookup is the value to lookup call it
' =busmodel("Routemaster R2RH")
Public Function BusModel(lookup) as String
Const wsn As String = "All Bus Models"
Const r As String = "$A$5:$E$5000"
On Error GoTo error_line
BusModel = WorksheetFunction.VLookup(lookup, _
Worksheets(wsn).Range(r), 4, False)
Exit Function
error_line:
BusModel = "Out Of Service "
End Function

you can change it to supply addresses in the call to the function rather
than hard
code them in the function.