Hello all,
I have recorded the following formula to use it in my code.
It keeps on giving an error (Expected: end of statement) on the line marked in red.
Any ideas
Please Login or Register to view this content.Please Login or Register to view this content.
Keep this in a cell
A1 Cell
IFERROR(IF(AND(COUNTIF(INDIRECT("DBS!HA"&$AP25&":ZZ"&$AP25);M$9)>0;IFERROR(OFFSET(INDEX(INDIRECT("DBS!HA"&$AP25&":ZZ"&$AP25);MATCH(M$9;INDIRECT("DBS!HA"&$AP25&":ZZ"&$AP25);0));0;1)="X";FALSE));OFFSET(INDEX(INDIRECT("DBS!HA"&$AP25&":ZZ"&$AP25);MATCH(M$9;INDIRECT("DBS!HA"&$AP25&":ZZ"&$AP25);0));0;2);IFERROR(INDEX(INDIRECT("ROS!"&ADDRESS(10;MATCH(INDIRECT("DBS!"&ADDRESS($AP25;MATCH(LARGE(INDIRECT($AQ25);COUNTIF(INDIRECT($AQ25);">"&M$9)+1);INDIRECT($AQ25);0)+39));INDIRECT($AR25);0)+5)&":"&ADDRESS((MATCH(REPT("z";255);INDIRECT("ROS!"&ADDRESS(10;MATCH(INDIRECT("DBS!"&ADDRESS($AP25;MATCH(LARGE(INDIRECT($AQ25);COUNTIF(INDIRECT($AQ25);">"&M$9)+1);INDIRECT($AQ25);0)+39));INDIRECT($AR25);0)+5)&":"&ADDRESS(79;MATCH(INDIRECT("DBS!"&ADDRESS($AP25;MATCH(LARGE(INDIRECT($AQ25);COUNTIF(INDIRECT($AQ25);">"&M$9)+1);INDIRECT($AQ25);0)+39));INDIRECT($AR25);0)+5)))+9);MATCH(INDIRECT("DBS!"&ADDRESS($AP25;MATCH(LARGE(INDIRECT($AQ25);COUNTIF(INDIRECT($AQ25);">"&M$9)+1);INDIRECT($AQ25);0)+39));INDIRECT($AR25);0)+5));MOD(M$9-1+OFFSET(INDIRECT("DBS!"&ADDRESS($AP25;MATCH(LARGE(INDIRECT($AQ25);COUNTIF(INDIRECT($AQ25);">"&M$9)+1);INDIRECT($AQ25);0)+39));0;1)-LARGE(INDIRECT($AQ25);COUNTIF(INDIRECT($AQ25);">"&M$9)+1);ROUNDUP((MATCH(REPT("z";255);INDIRECT("ROS!"&ADDRESS(10;MATCH(INDIRECT("DBS!"&ADDRESS($AP25;MATCH(LARGE(INDIRECT($AQ25);COUNTIF(INDIRECT($AQ25);">"&M$9)+1);INDIRECT($AQ25);0)+39));INDIRECT($AR25);0)+5)&":"&ADDRESS(79;MATCH(INDIRECT("DBS!"&ADDRESS($AP25;MATCH(LARGE(INDIRECT($AQ25);COUNTIF(INDIRECT($AQ25);">"&M$9)+1);INDIRECT($AQ25);0)+39));INDIRECT($AR25);0)+5))))/7;0)*7)+1;1);""));"")
Refer that cell in your macro code
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hello SixthSense, This looks very promosing but gives an error "Application-defined or object-defined error"
And will the references automatically adjust when populating a range with it?
I tested it and it is working fine
If your formula is constructed in that way (to auto adjust) by way of relative reference then surely it will auto adjust
If I use it like this, I get the same result in all cells.
I'd like it to adjust the references like it would have done when used in a code.
Please Login or Register to view this content.
What is the value of range A1?
Because you have not used any equal symbol in front of A1.
Refer my post #2 content once again.
I added the equal sign back just to see what happens.
There was no error but now getting the same results in all cells
(I have used relative references in my formula)
I also tried this:
Value in A1:Please Login or Register to view this content.
But results in the same error as beforePlease Login or Register to view this content.
Your formula is workine fine only thing you have to do is make it as relative reference
I'm not sure why but the problem seems to result from the use of "=" in my code.
It does not replace the # into an = sign in the following example.
It removes the # sign though?
Please Login or Register to view this content.
I'm not sure why but the problem seems to result from the use of "=" in my code.
It does not replace the # into an = sign in the following example.
It removes the # sign though?
Please Login or Register to view this content.
I solved it in the following way.
A1 houses the formula including the equal sign.Please Login or Register to view this content.
Thanks again for your help SixthSense!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks