title: Need to return the row value in a table in Excel using VBA. Could be any table any where on sheet cannot use activecell
body: <p>Using VBA in Excel:</p>
  
  <p>I have many sheets that have multiple tables. The only thing these tables have in common is a ProductID column and LotNumber column. The data in the Products ID column needs to be validated and or manipulated. I need a way to get the Table Row Index number of the current TABLE of the currently selected cell. I can not use ActiveCell reference because the table rows do not match the sheet rows. I can not use find the last used cell in a column because there are other tables in the way.</p>
  
  <p>I need to be able to use this sub in many different workbooks.</p>
  
  <p>Here is the code I have so far.</p>
  
  <pre><code>Const tblUNLABELED As String = "tblUnLabel"
  
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      StopAppEvents
      Dim ws As Worksheet
  
      Set ws = ActiveSheet
      Dim intRow As Integer
      Dim strTest As String
      If Not Intersect(ActiveCell, _
                       ActiveSheet.Range(tblUNLABELED &amp; "[LotNumber]")) Is Nothing Then
          'go to the product entered in this table row and Ucase(ProductID)
          strTest = ws.ListObjects(tblUNLABELED).Range(intRow, "[ProductID]")
          ws.ListObjects(tblUNLABELED).Range(intRow, "[ProductID]") = UCase(strTest)
      End If
  
      RestartAppEvents
  
  End Sub
  </code></pre>
  
  <p>Any help would be welcomed.</p>