A VBA code doesn't select the right columns in a sheet

[seigna]

Hi,

I am working on a code that is supposed to get the values from columns that correspond to the last three rows of a table I created. It needs to be under this form because the numbers will be random.

I have a table in the first excel sheet with different values. I calculate how many rows and columns there are.

Then I get the last three values from the second sheet, belonging to the column Index. I will use these indexes in order to construct a code that will obtain these indexes and indentify the columns they correspond to in the first excel sheet. THen, I want it to extract these values for me.

The problem is it gets the three FIRST values not last

How can I fix this?

Please Login or Register  to view this content.

[Norie]

In the workbook what should be the result?

[seigna]

it should give in the first column of the second sheet the values from the 3rd, 4th and 2nd column in that order...

[seigna]

That's what I'm trying to achieve by using:

Please Login or Register  to view this content.

I start at the last row in that table with the indexes, then I move up by one, and then one more. .. But maybe I forgot to add something.. I don't know.. it seems to me like this doesn't do that.

I hope this was clear enough...

The idea is it extracts:
.Cells(17,9) = 3
.Cells(16,9)=4
.Cells(15,9)=2

Then it uses those values, which I assign to Valeurs, to get the column which corresponds to that and place the values from that column into an array and display it on the SHeet Performance.

So, for .Cells(17,9)=3
it gets the third column from the table in Actions, and gets the values from that column into an array. Then, it displays them.

[Norie]

Sorry, I'm confused.

On the sheet 'Performance' you have a table with the headings 1st, 2nd and 3rd which has multiple rows.

Those rows seem to be made up of the value from the 1st column, 6th(last?) column and the 5th column from the table on sheet 'Actions'.

You mention a table with indexes, which table is that?

If you wanted to get columns 3, 4 and 2 in that order from the table on 'Actions' you could use this.

Please Login or Register  to view this content.

[seigna]

The second table on the sheet Performance

Please Login or Register  to view this content.

The values could change in that table index, so I cant just get them directly from the sheet Actions. I have to make VBA select for me the last THREE values in the column Indexes and identify the columns that correspond..

For instance, the last indexes could be 3, 2, 7 or 5, 4 2, or any other 3 values.

the table with 1st, 2nd, 3rd is to place in the values from the columns there. (

[Norie]

Change the code I posted to this.

Please Login or Register  to view this content.

[seigna]

This code works when I have values 3 4 and 2 in the last three rows of Indexes. however, somehow when I change those values to some other numbers, like 2, 5, 6 the code doesn't perform anything any longer... :S I need it to be flexible..

[Norie]

Strange, when I change the values to 2,5 and 6 and run the code it works.

[seigna]

Look at this for example.. the first column becomes empty

[seigna]

It happens only when I have 1 in those rows...

[Norie]

There is no column 7 in the table on 'Actions'.

[seigna]

OHHH NOOO... im sorry for that

Thank you so much for the help!

[seigna]

One more thing, Norie, how do I know that code refers to indexes in column 9? What if they were in column 7?

[Norie]

The code has the column to find the indexes hard-coded as I.

If that's going to change then you'll need to adjust the code, not sure how though.

One thing you could try is using actual tables, then it would be easy to refer to them wherever they were in the worksheet.

[seigna]

No the indexes are not going to change... it's that in my big file they are in column 7 :S

[Norie]

So change I as needed.

PS It would sitll be a good idea to try using tables, they would be much easier to work with especially if the amount of data is going to change from time to time.

[seigna]

Ahhhhh I just saw how it works! Thanks