if exists find last values in variable rows

**ABBOV** · 09-02-2010, 07:57 AM

Hello,

I'm new to VBA, managed to convert some script to my one but I need some help with the following:

There an input sheet called " Invoer", this sheet shows all the sales of an department. I would like to make a summary of all the names en their sales. The problem is that the totals of every name are in different rows and columns.

Example:
Name 1 finds it totals at 3 rows down and 5 colums left.
name 2 finds it totals at 5 rows down and 4 columns left.
Name 3 finds it totals at 3 rows down and 7 coloms left.

I know how to use the find function but not when it's so variable.

I included the an example (with an extra problem ;-))

Thanks in advance

ABBOV

**StephenR** · 09-02-2010, 08:09 AM

So the relevant total is always the intersection between "Conversie" and "Totaal" (plus the number underneath)?

**MarvinP** · 09-02-2010, 09:02 AM

In your code try:

Please Login or Register  to view this content.

**ABBOV** · 09-02-2010, 12:47 PM

Originally Posted by StephenR

So the relevant total is always the intersection between "Conversie" and "Totaal" (plus the number underneath)?

Stephen,

Thats correct, that are the 2 values that has to be summarised with the name above.

**ABBOV** · 09-02-2010, 12:48 PM

Originally Posted by MarvinP

In your code try:

Please Login or Register  to view this content.

Marvin,

What's the difference?

**MarvinP** · 09-02-2010, 03:38 PM

Hi ABBOV,

Your title said "variable row".
When you code with Range("B1:B1000") this is fixed and does not change.

If you code with Range("B1:B" & ActiveSheet.UsedRange.Rows.count)
the number of rows varies based on the value after the "&".

I was seeing if this syntax might help you.

**ABBOV** · 09-02-2010, 04:56 PM

Originally Posted by MarvinP

Hi ABBOV,

Your title said "variable row".
When you code with Range("B1:B1000") this is fixed and does not change.

If you code with Range("B1:B" & ActiveSheet.UsedRange.Rows.count)
the number of rows varies based on the value after the "&".

I was seeing if this syntax might help you.

Marvin,

Thanks, your right, it's not the complete answer to my problem but it helps me understand vba.

Best Regards,

ABBOV

**ABBOV** · 09-03-2010, 04:26 AM

Hello,

I think I found the right formula: (starting from the first found name.)

=INDEX(R[1]C[-1]:R[15]C[10],MATCH(""Conversie"",R[1]C[-1]:R[15]C[-11],),MATCH(""Totaal"",R[1]C[-1]:R[1]C[15]))"

Now it has to be fitted in. But i have got no clues.

Best Regards.

ABBOV

**StephenR** · 09-03-2010, 06:25 AM

Try this.

Please Login or Register  to view this content.

**snb** · 09-03-2010, 08:11 AM

or

Please Login or Register  to view this content.

**ABBOV** · 09-03-2010, 09:33 AM

Stephen,

Thanks, the code works and I'm almost there, only i found a small bug.

When the value of "c" is not found in "Uitvoerblad row K". nothing is copied to row D.
If the next value of "c" is found the associated aspectcode is copied to row D, only it's copied to the first free row. not the the row that is associated with the name (in value "c")

Because I don't need the names if there not in in row K (on "uitvoerblad"), maybe it's possible to ignore the values that are not found. I tried to do it myself by moving the "value v" part to the top but that results in different errors.

Do you have got any ideas?

Thanks in advance.

ABBOV

**ABBOV** · 09-03-2010, 09:55 AM

Originally Posted by snb

or

Please Login or Register  to view this content.

SNB,

Thanks, it realy shortens the code (and now i really don't understand it)
Sadly it does not give the desired effect, the wrong percentage is copied, now it copies the first percentage in column "conversie", not the total percentage. (last value in column "conversie").

However, you managed to solve the problem with the incorrect "associated aspect nr's" (see my reply to Stephen earlier)

Thanks

ABBOV

**StephenR** · 09-03-2010, 10:11 AM

Try this. It would probably have been simpler just to loop through the names on the first sheet if you're not interested in any others.

Please Login or Register  to view this content.

**ABBOV** · 09-03-2010, 10:48 AM

Stephen,

That does the trick, thank you very much for helping. Now i'm gonna study on the code to learn it myself.

Best Regards.

ABBOV
Arjan B. Busger op Vollenbroek

**snb** · 09-03-2010, 11:03 AM

I think you'd better clean up the 'invoer'sheet :
delete column A
remove spaces in columns after the last numerical data.

It's even simpler with:

Please Login or Register  to view this content.

if exists find last values in variable rows

LinkBack

Thread Tools

Rate This Thread

Display

if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Re: if exists find last values in variable rows

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions