functions in vba

[patel51]

how do you set the return value of the function if you are dealing with booleans

[rbrhodes]

Just like any other function! <g>

What are you trying to do? There is little or no information in your question.

[patel51]

Ok, I have to write code for a function, which I have labeled FindStreetRow.
I have already entered in my info in a seperate sheet in cells G10: G24.

The FindStreetRow function has a street name(a string) as its parameter and returns the row (an integer) correspoding to that street in cells G10:G24 in the a separate worksheet. If it can't find the street name in cells G10:G24, I want it to return 0.

But I know that first I need to write a proper function declaration for FindStreetRow. So far this is what I have, please let me know if its close:
Function FindStreetRow() As String

Afterwards, I want to do the following code inside my function declaration:

Declare a local integer variable that will be used as the counter variable of a For Next loop.
Use a For Next loop to go though each row in G10:G24.
Set the start and end values of the counter variable properly to go through each row in G10:G24. Thus, the counter variable will refer to the rows of all the street one by one.
Use an If statement to check if the parameter of the function matches the street name in the cell with column index 7 (column G) and row index stored in the counter variable.
If there is a match, return the value of the counter variable and exit.

If there isnt a match, I want it to return a value 0 in FindStreetRow.

I know its a lot, but I just began working with For next loops so its kinda hard lol.

[Bob Phillips]

What does this have to do with Booleans.

You need to pass the street name as the parameter and return a Long result

[vba]

Function FindStreetRow(Streetname As String) As Long
[/vba]

But why do a UDF? A function can do this.

[patel51]

Ok, so lets say i want to pass 6 as the parameter for the FindStreetRow function, after calling the function FindStreetRow.

how would i write the code for that?

would it be FindStreetRow(6)

[Bob Phillips]

But 6 isn't a street name. What exactly do yo want to do?

[rbrhodes]

Here's an educational example. Enter street name as string ie:

= FindStreetName("Street where I live")


1) Is simple (For/Next)

2) Uses variables and For/Next

3) Uses For/Each ( more efficient and therefore faster)


[VBA]
Option Explicit

'1

Function FindStreetRow(StreetName As String) As Long

Dim Counter As Long
'Rows
For Counter = 10 To 24
If Sheet2.Cells(Counter, 7) = StreetName Then
'Return row number
FindStreetRow = Counter
'We're done
Exit For
End If
Next Counter

End Function

'2

Function FindStreetRowVar(StreetName As String) As Long
Dim Ws As Worksheet
Dim Counter As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim SearchCol As Long
'//Use variables as only have to be changed once
' (here) to affect all placements
'Start in row 10
FirstRow = 10

'End in Row 24
LastRow = 24

'Start in Column G
SearchCol = 7

'Create object
Set Ws = Sheet2

'//End

'Rows
For Counter = FirstRow To LastRow
If Sheet2.Cells(Counter, SearchCol) = StreetName Then
'Return row number
FindStreetRowVar = Counter
'We're done
Exit For
End If
Next Counter
'Destroy object
Set Ws = Nothing

End Function

'3

Function FindStreetRowRng(StreetName As String) As Long
Dim cel As Range
Dim rng As Range
'Create Range object
Set rng = Sheet2.Range("G10:G24")

'Do all
For Each cel In rng
'If found
If cel = StreetName Then
'Return row where found
FindStreetRowRng = cel.Row

'We're done
Exit For
End If
Next cel

'Destroy objects
Set cel = Nothing
Set rng = Nothing
End Function

[/VBA]

[mikerickson]

[VBA]Dim RowNumber as Variant
Dim myRange as Range

Set myRange = Range("G10:G24")

RowNumber = Application.Match("soughtValue", myRange, 0)

If IsNumeric(RowNumber) Then
MsgBox "soughtValue found " & RowNumber & " cells below G9." & _
vbCr & "in cell " & myRange.Cells(RowNumber,1).Address
Else
MsgBox "soughtValue not in the range."
End If[/VBA]