VBA Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
Thanks very much Martin.

Martin Fishlock said:
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.
Click to expand...
 
Back
Top