Row() function use in EXCEL VBA

[syparth]

I have User Defined Function - getRefi(Row()).
I want to assign this function as value to some sepecific cells in worksheet.
I wrote a function as given below, but excel is not recognises the Row() funciton, while it works fine in workbook.

Public Function displayExpectedValues()
startRowValue = InputBox("Enter FirstRow Number to read data")
EndRowValue = InputBox("Enter Last Row Number to read Data")

For Each c In Sheets("Reasult Reader").Range("Q" & startRowValue &":Q" & EndRowValue )
    c.Offset(0, 1) = getIZV(Application.WorksheetFunction.Row())  ' excel is not recognizing row() function
    c.Offset(0, 2) = getIZV(Row()) ' excel is not recognizing row() function
    startRowValue = startRowValue  + 1
    if startRowValue  = EndRowValue Then
          Exit for
    End If
Next c
End Function

[JosephP]

please add code tags to your post

[MrShorty]

According to http://msdn.microsoft.com/en-us/libr.../ff836235.aspx the Row() worksheet function is NOT on the list of worksheet functions available to VBA. It seems likely that you will need a different approach to get the row number.

If this is really a function procedure that you intend to call from a spreadsheet cell, I would get rid of the input boxes and pass that information to the function through the argument list. I would probably also structure the other input and output differently.

If this is not intended to be called from a spreadsheet cell, I would probably change it to a sub procedure.

[JosephP]

c.row will give you the number you want

[syparth]

I have made a bit little changes to the function and have used "c.row" but it is thowing some different error now.

Public Function displayExpectedValues()
startRowValue = InputBox("Enter FirstRow Number to read data")
EndRowValue = InputBox("Enter Last Row Number to read Data")

c = Sheets("Result Reader").Range("Q" & CStr(startRowValue))

Do While startRowValue < EndRowValue
    c.Offset(0, 1) = getIZV(c.Row) ' Object Required error
    c.Offset(0, 3) = getIZV(c.Row)
    c.Offset(0, 2) = c.Offset(0, 1).Value + c.Offset(0, 3).Value
    startRowValue = startRowValue + 1
Loop
End Function

[MrShorty]

You haven't specifically dimensioned c as anything, so it defaults to a variant, which can hold just about any data type. So when you let c=sheet("result reader")..., it probably assumes you mean that c should equal the value of whatever is in that cell. To see this, step through your code, with a watch set for c. I expect under "type" it will say something like "variant/double" or "variant/string" depending on what is in that cell.

What you want is to set c as an object reference to the specified range/cell object. In order to do this, you must use the Set statement.

Set c=sheets("result reader").range("Q1")

. You might search VBA help for the set statement for further details. Using the set statement, I would expect you to see "variant/range" for the c's data type.