VBA find function to differentiate same value in a column (value appears twice in column)

Hi People,

I have this VBA script that I am trying to make for a task. The problem is that it gives me the values from the first 400.00 for both temp rows. The 400.00 value appears twice (for 2 cases: positive and negative scan) in the column A, and it finds both times the first 400.00 and executes the script for just the first found 400.00 i think. Thereby, giving me the same calculated mean values twice.

Please Login or Register  to view this content.

I would appreciate if you can guide me and point my error in the script. Thank you!

Best regards,
FNa

Please attach a sample workbook. Instructions at the top of this page.

400 appears 3 times (A491, A500 and A1504)

CORRECTION: my file "corrupted" (by me somehow)! Apologies.

400 appears 3 times (A491, A500 and A1504)

@ John...I only see 2 (400) entries...(A500 & A1504)

Edit...Corrected OP reference...
@ fazalnajeeb...I'm not actually sure what you are wanting...
If it is to extract the 2 (400) values then why not just filter Col A for this...
Perhaps you should explain a little more...

Look at A491: below shows A491 and A500: Using VBA "Find" will find 3 values!

Originally Posted by Sintek

@ macropod...I'm not actually sure what you are wanting...
If it is to extract the 2 (400) values then why not just filter Col A for this...
Perhaps you should explain a little more...

I think you should have addressed that to fazalnajeeb

@ macropod...Oops...My bad...
@ John...Are we seeing same file?
Untitled.png

.... Mine "corrupted" maybe?

UPDATE: downloaded file again and "400" at A491 disappears!

Strange...Post 3 download...for me...provides image in post 8

@JT

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A
490	401.00
491	400.90
492	400.80
493	400.70
494	400.60
495	400.50
496	400.40
497	400.30
498	400.20
499	400.10
500	400.00

Sheet: cDL_result_ao

I guess I (somehow) changed the value: 400.90 is correct and is in logical sequence.

More STRONG coffeee!!!

Lol!!!

More STRONG coffeee!!!

Give that man a Bells!

Hi Sintek,

So basically this script will be integrated into another script which uses the averaged calculated value to make graphs and other calculations. The original script only selects values based on cell references e.g. A500, A1504, but now I want it to take average values of +-2.5 range of the found value i.e. 400.00. (I write 400.00 instead of 400 because excel find function does not work if you search for 400; this has to do with the formatting but since this cDL_result sheet is entirely prepared by another VBA made by someone else, I don't want to change anything in the script that makes this very sheet)

Therefore, I am trying to work around with VBA find function instead of formula (which would have been much simpler to do). And when I ensure that the code runs correctly, I will replace the specific part in the original script. That's the plan!

As to what I want, it is explained in the post 1 and from post 3 attached file, you can get better idea when you run the code. It gives the same values averaged for the first found 400.00 for both cases while I need it to give two temporary rows (K1:S1, K2:S2) with both cases of 400.00 found value.

P.S.:

Please Login or Register  to view this content.

This code was provided by macropod, but I cannot see it in above posts now. Do you suggest I integrate a formula in macro code instead of find function?

EDIT: I changed the formatting from number to text in the script that makes the cDL_result sheet, and then edited the find function code a bit in the post 1 shared script. And it works now!! Thank you all for your help.