VBA for search -> add -> delete

[keresztesi]

Hi,

I'd like to have a vba code for the following:

1. the code should search for numeric value in column "V" (there is either only one cell with number or all cells are blank). If there's a nr. then:
2. in the row where the number is (for example it's the 20th) it searches again for a number in range W20:AA20
3. then it searches again for numeric values in range W2:AA1000 and if it finds a value that is greater then the one found in range W20:AA20 then it adds the value from column "V"
4. finally it deletes the numeric value from column "V"

For example:

BEFORE:
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

AFTER:
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thx in advance!

Zoli

 

[steve the fish]

Is the 5 in column V meant to be in the before one?

 

[keresztesi]

Oh, yes!

Is the 5 in column V meant to be in the before one?

 

[steve the fish]

So are we doing greater than 4? What if it finds two numbers in the row? Your example shows rows with more than 1 number.

 

[keresztesi]

1. yes, greater than 4
2. is it possible to select which value to calculate with?,

So are we doing greater than 4? What if it finds two numbers in the row? Your example shows rows with more than 1 number.

 

[steve the fish]

Not in a way i can think of that isnt messy.

 

[keresztesi]

It isn't a way to give the selected number in a pop-up field?
Or is it a better way to have an extra column for that?

Not in a way i can think of that isnt messy.

 

[steve the fish]

Not that i can think of as i say.

 

[keresztesi]

Neither pop-up or extra column?

I believe with extra column where I should give it manually which the code should select could be a solution, or not?

Not that i can think of as i say.

 

[steve the fish]

If you put a value in column AB for example then its ok yes.