Return Row Number

Question

I'm trying to search for a particular value in a column once I find that value I want to get the Row number store in to a variable and use that value to place another value that I find in a specific column with that row number. Here is what I have so far

Sub gage()

    Dim MyColumn As String, x As Long

    Dim OutColumn As String, y As Long

    Dim Row As Integer

    'Column we're looking in

     MyColumn = "F"

    'column to output data

     OutColumn = "Q"

     y = 34

For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1

    If Len(Cells(x, MyColumn)) = 4 Then

    If Mid(Cells(x, MyColumn), 1, 1) Like "[0-9]" And _

        Mid(Cells(x, MyColumn), 2, 1) Like "[0-9]" And _

        Mid(Cells(x, MyColumn), 3, 1) Like "[A-Za-z]" And _

        Mid(Cells(x, MyColumn), 4, 1) Like "[A-Za-z]" Then

            'If Len(Cells(x, MyColumn)) = 2 Then

                y = ActiveCell.Row

                Cells(y, OutColumn) = Left(Cells(x, MyColumn), 2)

                y = y + 1

            'End If

     End If

 End If

Next x

End Sub

Answer 1

Hi,

You can get the row like this

Sub gage()

     Dim MyColumn As String, x As Long

     Dim OutColumn As String, y As Long

     Dim Row As Integer

     'Column we're looking in

      MyColumn = "F"

     'column to output data

      OutColumn = "Q"

 For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1

     If Len(Cells(x, MyColumn)) = 4 Then

     If Mid(Cells(x, MyColumn), 1, 1) Like "[0-9]" And _

         Mid(Cells(x, MyColumn), 2, 1) Like "[0-9]" And _

         Mid(Cells(x, MyColumn), 3, 1) Like "[A-Za-z]" And _

         Mid(Cells(x, MyColumn), 4, 1) Like "[A-Za-z]" Then

                 y = Cells(x, MyColumn).Row

                 Cells(y, OutColumn) = Left(Cells(x, MyColumn), 2)

                 y = y + 1

             'End If

      End If

  End If

 Next x

 End Sub