Indirect and matching formula isn't working

B

Bud

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?
 
O

OssieMac

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))))"
 
D

Dave Peterson

Check your other post.
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?
 
B

Bud

Thank You so much!

OssieMac said:
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))))"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top