none
VB: vb for ROW Number same as =ROW() not activecell.row RRS feed

  • 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
    Friday, August 11, 2017 10:29 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
    Saturday, August 12, 2017 12:16 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
    Saturday, August 12, 2017 4:40 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
    Saturday, August 12, 2017 12:16 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
    Saturday, August 12, 2017 1:24 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
    Saturday, August 12, 2017 4:40 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
    Saturday, August 12, 2017 4:55 AM