Answered by:
VB: vb for ROW Number same as =ROW() not activecell.row
Question

hi, slow at vb, and try as may to find what looking for if already don't know what to call it.
i would like to be able to identify a: ROW NUMBER where a formula for each row is.
if it is in a UDF called rowX(), then how to make so if place it in cell A100, for eg: =rowx to show: 100
the problem would be the same as having: =ROW() in a cell, but to have the vb version of that.
tried to get to work: Application.WorksheetFunction.row()
Tried:
Function rowX()
'Function rowX(X)
rowX = application.WorksheetFunction.row()
'X = application.WorksheetFunction.row()
rowX
'X = application.WorksheetFunction.row(X)
'MsgBox X & Space(10), vbQuestion
'Application.WorksheetFunction.row()
'Application.WorksheetFunction.Min(myRange)
'Rows (Cells(application.row))
'Cell.Address(0, 0)
'Rows (cell.row)
'application.row.address(0, 0)
'cell.row.address(0, 0)
'application.Rows.address
'cells
'"row"&().row
'Target.row
'Rows.row
'application.row
End Function
 Edited by Davexx Friday, August 11, 2017 11:04 PM
Answers

Try
Function RowX(Optional r As Range) As Long If r Is Nothing Then Set r = Application.Caller End If RowX = r.Row End Function
Use like this to return the row number of cell A4 (i.e. 4):
=RowX(A4)
Use like this to return the row number of the cell with the formula:
=RowX()
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by Davexx Saturday, August 12, 2017 4:53 AM

The functions are quite simple:
Function goR(rA As Range) As Boolean Dim r As Long r = Application.Caller.Row goR = (r > rA.Row) End Function Function goRM(rA As Range, rB As Range) As Boolean Dim r As Long r = Application.Caller.Row goRM = (r >= rA.Row And r <= rB.Row) End Function
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by Davexx Saturday, August 12, 2017 4:53 AM
All replies

Try
Function RowX(Optional r As Range) As Long If r Is Nothing Then Set r = Application.Caller End If RowX = r.Row End Function
Use like this to return the row number of cell A4 (i.e. 4):
=RowX(A4)
Use like this to return the row number of the cell with the formula:
=RowX()
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by Davexx Saturday, August 12, 2017 4:53 AM

Thanks!! that worked mostly for what i am doing. i never would have got that. it works for what had question for: in a cell or a call for same function.. to another function.
 whats funny is i tried to make the function below, that took me maybe 15 minutes for using activecell (mistake), and spent last 7 hours trying to get this.
i tried to combine the 2 into one function, but unsuccessful. if i can get some help with that & i will mark this as answered either way. thanks.
what i was trying to do, with my version of excel.. to save space in a cell with too many:
=if(row(a3)>row(a$100),if(row(a3)>row(a$200),"section2","section1") for running out of space. i tried to combine the 2 functions as below. can you help me correct that?? thanks. not working:
'========== Can i get some help combining 2 functions, i could not get this to work:
Function goR(rA As RANGE) 'As Long 'go row greater than or equal to destination row AS: IF(goR(A$100),"YES","NO")
Dim r As RANGE: Set r = application.Caller: r = r.row '<< problem ?
goR = False: If r() >= rA.row Then goR = True 'not working combined, YES for rowx separate
'goR = False: If activecell.row() >= rA.row Then goR = True 'NO
End Function'========== WORK SEPARATELY:
Function goR(rA As RANGE) 'go row greater than or equal to destination row AS: IF(goR(A$100),"YES","NO")
goR = False: If rowX() >= rA.row Then goR = True 'YES for rowx separate
'goR = False: If activecell.row() >= rA.row Then goR = True 'NO
End Function
Function rowX(Optional r As RANGE) As Long 'YES, AS: =RowX(A4) or: =RowX()
If r Is Nothing Then
Set r = application.Caller
End If
rowX = r.row
End Function
Other: for row between 2 rows
Function goRM(rA As RANGE, rB As RANGE) 'go row mid inclusive AS: IF(goRM(A$100,A$200),"YES","NO")
goRM = False: If rowX() >= rA.row And rowX() <= rB.row Then goRM = True
'goRM = False: If selection.row >= rA.row And selection.row <= rB.row Then goRM = True
End Function Edited by Davexx Saturday, August 12, 2017 1:30 AM

The functions are quite simple:
Function goR(rA As Range) As Boolean Dim r As Long r = Application.Caller.Row goR = (r > rA.Row) End Function Function goRM(rA As Range, rB As Range) As Boolean Dim r As Long r = Application.Caller.Row goRM = (r >= rA.Row And r <= rB.Row) End Function
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Marked as answer by Davexx Saturday, August 12, 2017 4:53 AM

thanks much.. will double check last items this week end.. but too good not to be true :) thanks for the quick fix. (no true false on above seems funny, but i wouldn't know, boolean i guesse).
my sheet dropped by 500k (for only mayb 250 rows..), not sure if for just this/ may have compressed finally after x number of sorts.. but may have had stress in cells maxed out with many ROW functions in cell seem somehow more destructive  volatile..
reduced formula by 30% (15 with added a dozen variables), if funny to see looks like:
=IF(goR(A$1146),IF(goR(A$1640),IF(goR(A$1775),IF(goR(A$1992),IF(goR(A$2188),"X0",
"D"&IF(goR(A$2091),IF(goR(A$2162),3,2),1)),
"H"&IF(goR(A$1854),IF(goR(A$1948),3,2),1)),IF(goR(A$1667),
"M0"&IF(goR(A$1675),IF(goR(A$1695),IF(goR(A$1731),3,2),1),0),IF(goRM(A$1646,A$1652),IF(goR(A$1650),"MT1","MSP1"),"MS"&IF(goR(A$1655),IF(goR(A$1660),2,1),0)))),IF(goR(A$1368),IF(goR(A$1413),
"D0"&IF(goR(A$1456),IF(goR(A$1491),IF(goR(A$1591),3,2),1),0),IF(goRM(A$1374,A$1386),IF(goR(A$1379),IF(goR(A$1382),"DT2","DT1"),IF(goR(A$1376),"DSP2","DSP1")),"DS"&IF(goR(A$1389),IF(goR(A$1398),2,1),0))),IF(goR(A$1184),
"H0"&IF(goR(A$1209),IF(goR(A$1306),3,2),1),"P0"&IF(goR(A$1152),IF(goR(A$1167),3,2),1)))),IF(goR(A$1017),
IF(goR(A$1066),IF(goR(A$1096),"H00",IF(goR(A$1075),IF(goR(A$1087),"HT2","HT1"),IF(goR(A$1071),"HSP2","HSP1"))),"HS"&IF(goR(A$1034),IF(goR(A$1046),2,1),0)),IF(goR(A$984),IF(goR(A$989),"N2","N1"),IF(goR(A$927),"C2","C1"))))
 Edited by Davexx Saturday, August 12, 2017 5:21 AM