Need the Row Number

[Sebol]

I am trying to get the row number that I am calling "esRow" from a column I use to populate a dropdown list on a different worksheet in the same workbook.

When the selection is made, I need to store the row number into esRow to use as you can see in the code. I have been stuck on this for hours, please help!

I understand that you need to use ActiveCell.Row, however, I cannot see how to use that in the format I am.

Sub DataCollection()

Dim c As Long
Dim DstWks As Worksheet
Dim LastRow As Long
Dim R As Long
Dim SrcWkb As Workbook
Dim StartRow As Long
Dim wkbname As Variant
Dim tstFiles As Variant
Dim DataV As String
Dim Response As Integer
Dim DoOnce As Boolean
Dim Tech As String
Dim DstWkb As Workbook
Dim firstRow As Integer
Dim esRow As Integer
Dim grRow As Integer
Dim neRow As Integer




DoOnce = False
'Starting Column and row for the destination workbook
c = 5
R = grRow
'Set References to destination workbook worksheet objects
Set DstWks = ThisWorkbook.Worksheets("SED Data Collection")
DataV = DstWks.Range("E184").Text

'Change Drive
ChDrive "T"
'Change Directory
ChDir "T:\Data\2010"
'Get the Workbooks to Open
tstFiles = Application.GetOpenFilename(FileFilter:="Test Files (*.tst), *.tst", MultiSelect:=True)
    If VarType(tstFiles) = vbBoolean Then Exit Sub
    
    'Loop through each workbook and copy the data to this workbook
    For Each wkbname In tstFiles 'For every .tst file workbook selected
        Set SrcWkb = Workbooks.Open(Filename:=wkbname, UpdateLinks:=False, ReadOnly:=True)
        SrcWkb.Activate
        
        If DoOnce = False Then
        'Data Validation Set
        'Displays a message box with the yes and no options.
      Response = MsgBox(prompt:="You are about to paste Data from " & MyFullName & _
      " to " & DataV & ", Is this Correct? Select 'Yes' or 'No'.", Buttons:=vbYesNo)


        Tech = Worksheets("Report").Range("J12").Text
      If Worksheets("Report").Range("J14").Text = "Enter Here:" Then
        MsgBox ("Test Number Missing, Data not entered by: " & Tech)
      If Worksheets("Report").Range("C17").Text = "Enter Here:" Then
        MsgBox ("Leak Down % Missing, Data not entered by: " & Tech)
      
      ' If statement to check if the yes button was selected.
      If Response = vbNo Then
         Exit Sub
      End If
      End If
      End If
      DoOnce = True
      End If
        
    firstRow = 2
    ResultRow = (firstRow + ((esRow - 1) * 26))
    grRow = ResultRow + 2
    neRow = ResultRow + 14
    
    
    StartRow = 25 'Begin Range
    LastRow = 33 'End Range
    R = grRow 'Destination Row
        If LastRow >= StartRow Then
            With SrcWkb.Worksheets("Report")
            DstWks.Cells(R, c).Resize(LastRow - StartRow + 1, 1).Value = _
            .Range(.Cells(StartRow, "C"), .Cells(LastRow, "C")).Value
            End With
        End If
    StartRow = 38 'Begin Range
    LastRow = 46 ' End Range
    R = neRow 'Destination Row
        If LastRow >= StartRow Then
            With SrcWkb.Worksheets("Report")
            DstWks.Cells(R, c).Resize(LastRow - StartRow + 1, 1).Value = _
            .Range(.Cells(StartRow, "C"), .Cells(LastRow, "C")).Value
            End With
        End If
    c = c + 1 'Column +1 to go to Next Column
    SrcWkb.Close SaveChanges:=False 'Close source workbook disregarding changes

    Next wkbname 'Next Workbook
    
    MsgBox ("Macro Complete! Have a Nice Day.")

End Sub

 

[stilesbn]

I'm assuming you want esRow to be the row of whatever the active cell is when you are running the macro. In your macro currently you are using esRow as a value before you've given it any value. If you just want it to be the row of the active cell then add

esRow = ActiveCell.Row

to this code in the middle of your code.

firstRow = 2
ResultRow = (firstRow + ((esRow - 1) * 26))
grRow = ResultRow + 2
neRow = ResultRow + 14

 

[Sebol]

No no. On a worksheet titled "List" I have a list of products. If they select the product in the dropdown list (which is on worksheet "Cover Page" and ultimately runs the macro) that the name matches the product in List!A3, then I want it to return me the number 3 as esRow.