Hello

Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"
---This is the formula I am using today and it works fine to do matching.

However, I wanted to be able to replace the 8000 in that formula with an
actual row count that I am obtaining and placing in a field called BR.

Someone suggested I use the INDIRECT function and I just can't get it to
work. I have tried everything...adding the WorksheetFunction...placing double
quotes...placing an ISERROR.

I have gotton everything from syntax error to Run time error
1004....Application-defined or object defined error trying to use this
INDIRECT.......
Cells(2, "k") = "=IF(ISERROR(WorksheetFunction.INDIRECT(""$b$2:$b$ "" &
S1))="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH (WorksheetFunction.INDIRECT(""$b$2:$b$"" & S1)),Personnel!$A$1:$A$1000,0))))"

All I want to do is take this following formula that works and replace the
8000 in that formula with an actual row count that I am obtaining and placing
in a field called BR.
Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"

Is there something that can do this besides the INDIRECT or can someone help
setup the formula with the INDIRECT so it works?

Not sure what the field BR is but get your row count into a vba variable and
simply concatenate it into the formula like following.

Note: It is one line of code.

myRowCount = 8008 'Assign row number to a variable

Cells(2, "k") =
"=IF($b$2:$b$" & myRowCount &
"="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH($b $2:$b$" & myRowCount &
",Personnel!$A$1:$A$1000,0))))"

--
Regards,

OssieMac


"Bud" wrote:

Hello

Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"
---This is the formula I am using today and it works fine to do matching.

However, I wanted to be able to replace the 8000 in that formula with an
actual row count that I am obtaining and placing in a field called BR.

Someone suggested I use the INDIRECT function and I just can't get it to
work. I have tried everything...adding the WorksheetFunction...placing double
quotes...placing an ISERROR.

I have gotton everything from syntax error to Run time error
1004....Application-defined or object defined error trying to use this
INDIRECT.......
Cells(2, "k") = "=IF(ISERROR(WorksheetFunction.INDIRECT(""$b$2:$b$ "" &
S1))="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH (WorksheetFunction.INDIRECT(""$b$2:$b$"" & S1)),Personnel!$A$1:$A$1000,0))))"

All I want to do is take this following formula that works and replace the
8000 in that formula with an actual row count that I am obtaining and placing
in a field called BR.
Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"

Is there something that can do this besides the INDIRECT or can someone help
setup the formula with the INDIRECT so it works?

Check your other post.

Bud wrote:

Hello

Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"
---This is the formula I am using today and it works fine to do matching.

However, I wanted to be able to replace the 8000 in that formula with an
actual row count that I am obtaining and placing in a field called BR.

Someone suggested I use the INDIRECT function and I just can't get it to
work. I have tried everything...adding the WorksheetFunction...placing double
quotes...placing an ISERROR.

I have gotton everything from syntax error to Run time error
1004....Application-defined or object defined error trying to use this
INDIRECT.......
Cells(2, "k") = "=IF(ISERROR(WorksheetFunction.INDIRECT(""$b$2:$b$ "" &
S1))="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH (WorksheetFunction.INDIRECT(""$b$2:$b$"" & S1)),Personnel!$A$1:$A$1000,0))))"

All I want to do is take this following formula that works and replace the
8000 in that formula with an actual row count that I am obtaining and placing
in a field called BR.
Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"

Is there something that can do this besides the INDIRECT or can someone help
setup the formula with the INDIRECT so it works?

--

Dave Peterson

Thank You so much!

"OssieMac" wrote:

Not sure what the field BR is but get your row count into a vba variable and
simply concatenate it into the formula like following.

Note: It is one line of code.

myRowCount = 8008 'Assign row number to a variable

Cells(2, "k") =
"=IF($b$2:$b$" & myRowCount &
"="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH($b $2:$b$" & myRowCount &
",Personnel!$A$1:$A$1000,0))))"

--
Regards,

OssieMac


"Bud" wrote:

Hello

Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"
---This is the formula I am using today and it works fine to do matching.

However, I wanted to be able to replace the 8000 in that formula with an
actual row count that I am obtaining and placing in a field called BR.

Someone suggested I use the INDIRECT function and I just can't get it to
work. I have tried everything...adding the WorksheetFunction...placing double
quotes...placing an ISERROR.

I have gotton everything from syntax error to Run time error
1004....Application-defined or object defined error trying to use this
INDIRECT.......
Cells(2, "k") = "=IF(ISERROR(WorksheetFunction.INDIRECT(""$b$2:$b$ "" &
S1))="""","""",(INDEX(Personnel!$B$1:$B$1000,MATCH (WorksheetFunction.INDIRECT(""$b$2:$b$"" & S1)),Personnel!$A$1:$A$1000,0))))"

All I want to do is take this following formula that works and replace the
8000 in that formula with an actual row count that I am obtaining and placing
in a field called BR.
Cells(2, "k") =
"=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))"

Is there something that can do this besides the INDIRECT or can someone help
setup the formula with the INDIRECT so it works?