reference a cell with a variable row in another workbook

[jackohl]

I am new at vba.
I need to locate the row number of certain employees in every workbooks
Employee names are sorted differently and are in another column for some workbooks.
Workbook names are located in column AC populated using dir().

From the code below, you will notice that $AC14 is static.
I want to replace $AC14 with something like "AC" & x + 14.
However, I am at a lost on how to accomplish this.
Any suggestion will be highly appreciated.

' $B$7 = employee name from drop down list
' $AA$7 = sheet name from drop down list

Private Sub GetEmployeeRow()
x = 0
Do While Range("AC" & x + 14).Value <> ""
If Left(Range("AC" & x + 14), 1) = "2" Then
Range("AA" & x + 14).Formula = "=MATCH($B$7,INDIRECT(""'[""&$AC14&""]""&$AA$7&""'!B:B""),0)"
End If
If Left(Range("AC" & x + 14), 1) = "C" Then
Range("AA" & x + 14).Formula = "=MATCH($B$7,INDIRECT(""'[""&$AC14&""]""&$AA$7&""'!C:C""),0)"
End If
' criteria for other workbooks
x = x + 1
Loop
End Sub

Thank you in advance.

 

[DanteAmor]

Try this

Private Sub GetEmployeeRow()
    x = 0
    Do While Range("AC" & x + 14).Value <> ""
        If Left(Range("AC" & x + 14), 1) = "2" Then
            Range("AA" & x + 14).Formula = "=MATCH($B$7,INDIRECT(""'[""&$AC" & x + 14 & "&""]""&$AA$7&""'!B:B""),0)"
        End If
        If Left(Range("AC" & x + 14), 1) = "C" Then
            Range("AA" & x + 14).Formula = "=MATCH($B$7,INDIRECT(""'[""&$AC" & x + 14 & "&""]""&$AA$7&""'!C:C""),0)"
        End If
        ' criteria for other workbooks
        x = x + 1
    Loop
End Sub

 

[DanteAmor]

I am new at vba.
I need to locate the row number of certain employees in every workbooks
Employee names are sorted differently and are in another column for some workbooks.
Workbook names are located in column AC populated using dir().

From the code below, you will notice that $AC14 is static.
I want to replace $AC14 with something like "AC" & x + 14.
However, I am at a lost on how to accomplish this.
Any suggestion will be highly appreciated.

' $B$7 = employee name from drop down list
' $AA$7 = sheet name from drop down list

Private Sub GetEmployeeRow()
x = 0
Do While Range("AC" & x + 14).Value <> ""
If Left(Range("AC" & x + 14), 1) = "2" Then
Range("AA" & x + 14).Formula = "=MATCH($B$7,INDIRECT(""'[""&$AC14&""]""&$AA$7&""'!B:B""),0)"
End If
If Left(Range("AC" & x + 14), 1) = "C" Then
Range("AA" & x + 14).Formula = "=MATCH($B$7,INDIRECT(""'[""&$AC14&""]""&$AA$7&""'!C:C""),0)"
End If
' criteria for other workbooks
x = x + 1
Loop
End Sub

Thank you in advance.

How about, in a single formula

Private Sub GetEmployeeRow1()
 With Range("AA14:AA" & Range("AC" & Rows.Count).End(xlUp).Row)
  .Formula = "=MATCH($B$7,INDIRECT(""'[""&$AC" & x + 14 & "&""]""&$AA$7&""'!"" & " & _
           "IF(LEFT(AC14,1)=""2"",""B:B"",IF(LEFT(AC14,1)=""C"",""C:C""))),0)"
 End With
End Sub

 

[jackohl]

Sir Dante Amor,

Your first code works. It meets my current needs.
Thank you very much.
I'll be analyzing your second code as it needs modification in order to accommodate the criteria for other workbooks.
Again, thank you very much.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.