Creating functions

[Guest]

Hi,

I have written the following code in a module: (essentially all it does is
find the next blank line for me to use)

Public Function SR()

Dim row, col As Integer

col = 4
row = 7

While Worksheets("Main").Cells(row, col).Value <> ""
row = row + 1
Wend

End Function

Can someone please tell me:

- is this the right place to put it (it does seem to work)
- how I return the value back to my calling private sub (Button click)

Cheer

Steve

 

[Tom Ogilvy]

Public Function SR()

Dim row, col As Integer

col = 4
row = 7

While Worksheets("Main").Cells(row, col).Value <> ""
row = row + 1
Wend
sr = row
End Function

Public Sub Main()
Dim lastrow as Long
lastrow = Sr
msgbox LastRow
End sub

Depending on how your data is laid out, You might change SR to

Public Function Sr()
Dim rng as Range
set rng = Worksheets("Main").Cells(rows.count,4).End(xlup)(2)
if rng.row < 7 then
set rng = Worksheets("Main").Range("D7")
end if
sr = rng.row
End Function

 

[Guest]

Spot on

Many thanks

Public Function SR()

Dim row, col As Integer

col = 4
row = 7

While Worksheets("Main").Cells(row, col).Value <> ""
row = row + 1
Wend
sr = row
End Function

Public Sub Main()
Dim lastrow as Long
lastrow = Sr
msgbox LastRow
End sub

Depending on how your data is laid out, You might change SR to

Public Function Sr()
Dim rng as Range
set rng = Worksheets("Main").Cells(rows.count,4).End(xlup)(2)
if rng.row < 7 then
set rng = Worksheets("Main").Range("D7")
end if
sr = rng.row
End Function