+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    06-28-2024
    Location
    Germany
    MS-Off Ver
    2021
    Posts
    21

    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
    Last edited by fazalnajeeb; 08-29-2024 at 05:37 AM. Reason: typo

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,845

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    Please attach a sample workbook. Instructions at the top of this page.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    06-28-2024
    Location
    Germany
    MS-Off Ver
    2021
    Posts
    21

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    Hi macropod,

    Attached you will find the sample workbook. Let me know if you have any questions. Thanks!

    cDLsampleBook1.xlsm

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,114

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    400 appears 3 times (A491, A500 and A1504)

    CORRECTION: my file "corrupted" (by me somehow)! Apologies.
    Last edited by JohnTopley; 08-29-2024 at 03:00 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  5. #5
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,988

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    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...
    Last edited by Sintek; 08-29-2024 at 02:44 AM.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,114

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    Look at A491: below shows A491 and A500: Using VBA "Find" will find 3 values!
    Attached Images Attached Images
    Last edited by JohnTopley; 08-29-2024 at 02:35 AM.

  7. #7
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,845

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    Quote Originally Posted by Sintek View Post
    @ 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

  8. #8
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,988

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

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

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,114

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    .... Mine "corrupted" maybe?

    UPDATE: downloaded file again and "400" at A491 disappears!
    Last edited by JohnTopley; 08-29-2024 at 02:56 AM.

  10. #10
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,988

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

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

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,653

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    @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
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    31,114

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

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

    More STRONG coffeee!!!

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,653

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    Lol!!!

  14. #14
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    14,988

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    More STRONG coffeee!!!
    Give that man a Bells!

  15. #15
    Registered User
    Join Date
    06-28-2024
    Location
    Germany
    MS-Off Ver
    2021
    Posts
    21

    Re: VBA find function to differentiate same value in a column (value appears twice in colu

    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.
    Last edited by fazalnajeeb; 08-29-2024 at 05:37 AM. Reason: solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how to find last time word appears in column
    By semajjames in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-08-2020, 08:02 AM
  2. Find Column that text appears in
    By chrisellis250 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-08-2019, 01:20 PM
  3. If a cell contains value I would like to differentiate the row/column it is in
    By slimjim909 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2018, 11:28 AM
  4. [SOLVED] Sumif function to total column e if text in column a appears in more than one row
    By SMITH.CRYSTAL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-23-2015, 03:58 PM
  5. Replies: 8
    Last Post: 09-03-2014, 07:25 AM
  6. Find number of times a value from a list appears in a seperate column
    By GSmith8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2013, 02:05 AM
  7. [SOLVED] Find various strings and copy the found string in column A of the row it appears in.
    By equanet in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-29-2012, 02:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1