If Statement with Multiple And , help please

[bimo]

hi Everyone

I get Error message "ApplicationDefined or Object Defined Error" when trying to run this code, any idea where the error is please ? I vaguely think it is the Multiple And...

Thank you

Please Login or Register  to view this content.

[AB33]

When I run the code, I did not get an error.

[AB33]

Remove the And if these lines as it has not any value

Please Login or Register  to view this content.

[stnkynts]

The primary thing that is messing you up is Cells(i, -4).Value. In your situation, there is no -4 column. It doesn't exist. Why don't you let us know what cell references you are actually wanting to use so we can rewrite the code for you. A sample workbook would make things easier.

[bimo]

When I run the code, I did not get an error.

you right it works now, Strange ?! Thanks for checking

[AB33]

stnkynts is right.
The code probably did not error because is it is returning the first condition, but if it goes to else if, it will fail.
You can not go from column A to left, only right wing part is allowed to the right from column A.

[bimo]

Remove the And if these lines as it has not any value

Please Login or Register  to view this content.

you right no need for And

[bimo]

you right it works now, Strange ?! Thanks for checking

didn't get an error but the code does not what it is supposed to

[bimo]

The primary thing that is messing you up is Cells(i, -4).Value. In your situation, there is no -4 column. It doesn't exist. Why don't you let us know what cell references you are actually wanting to use so we can rewrite the code for you. A sample workbook would make things easier.

here is the whole Code with a sample workbook I managed to get rid of error message related to trying to look for cells outside the work sheet but the code does not what it supposed to,

the conditions are on column C and I and value to transpose are on column M

Can you please see where the arguments falls and whether I could have smaller code ? not sure if VBA allow for two variables/dimensional arguments and if so how..

Thank you

Please Login or Register  to view this content.

[AB33]

"the conditions are on column C and I and value to transpose are on column M "
Need more and detailed explanation
You have duplicates in column C and I
What is the expected result? what values/column/rows do you want to transpose to column M?

[bimo]

"the conditions are on column C and I and value to transpose are on column M "
Need more and detailed explanation You have duplicates in column C and I
What is the expected result? what values/column/rows do you want to transpose to column M?

6 conditions mean that if values are repeated 6 times in column 6 And "discount" are repeated 6 times in Column I Then values in Column M ( down to the last repeated items) need to be transposed into Row i staring from column M

Attached is the results sought

Thanks,

[stnkynts]

Unless I am missing an important piece of information, this should work for you:

Please Login or Register  to view this content.

[bimo]

Unless I am missing an important piece of information, this should work for you:

Please Login or Register  to view this content.

genius!! Thank you!

[bimo]

Unless I am missing an important piece of information, this should work for you:

Please Login or Register  to view this content.

just a question please , do you think these instructions can handle large data ? 52K lines

[stnkynts]

Yeah but it will take it a little bit. There are a couple of more efficient ways of doing it but I don't have the time at the moment to work something up. Try it on the 52k lines and see how long it takes and report back.

[bimo]

Yeah but it will take it a little bit. There are a couple of more efficient ways of doing it but I don't have the time at the moment to work something up. Try it on the 52k lines and see how long it takes and report back.

there is actually an issue, when there is one value in I Column, it Copies all the blank cells bellows and try to paste them

[bimo]

there is actually an issue, when there is one value in I Column, it Copies all the blank cells bellows and try to paste them

I have now modified the code, to deal with one and two repetition, and also I needed it to start pasting on one row higher.. it takes about 15min for the Macro to complete Thanks again for your help

Please Login or Register  to view this content.

[bimo]

Yeah but it will take it a little bit. There are a couple of more efficient ways of doing it but I don't have the time at the moment to work something up. Try it on the 52k lines and see how long it takes and report back.

had to modify the code, got confused when it is only one and two filled cells tried to copy and paste too many ..

it takes about 15 min to do it.

thanks again for your help

Please Login or Register  to view this content.