INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

(OP)
Hello All,

I am not sure if this is possible and I have not been able to find something in the the threads that would help me. My question is the following:

I have 4000 rows of data, one row contains text (column A) and another with numbers (column B) and a third column with text (column C) located in sheet 1 In sheet 2 there is a table that contains a number (total value) the text in sheet 1. We currently take the text and the value in sheet 2 and filter for the text in sheet 1. Starting at the bottom of sheet 1 we manually highlight the cells in column b until the sum is equal to or greater than the value in sheet 2. Once we find identify the cells in sheet 1 that sum to > or equal to s value in sheet 2 we update the sheet 1 column C value to "NO" text. For example:

Sheet 2
A 500

Sheet 1
A 100
A 300
A 1000
A 100
A 500
A 300
A 200
A 100

The code should change the last three rows column C value to NO

Appreciate your help

RE: Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

Hi,

You do realize that...

A 1000 < A500

Yes?

RE: Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

Sorry, forgot the the SPACE

A 1000 < A 500

RE: Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

Oh, yes, I also forgot to sat what application?

RE: Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

I forgot to ASK.

Wow, I'm loosing it!

RE: Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

(OP)
Hi Skip,

Thank you for replying back! The data is sorted with the loudest items on the bottom and the newest on top. So we can not take the 1000. We need to identify the bottom of the list that sum up to > or equal 1000. Also, I am using excel and want to use VBA. Any help you can provide will be greatly appreciated.

RE: Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

If every value in column A is "A" then there is no value in those values, so you should just ignore them.

Once you start working with only numbers for math it becomes a relatively easy exercise to loop upwards through the used range, keeping a running totalizer to decide what to do in column C.

RE: Is this Possiblle Using VBA? Loop and highlight based on Sun Criteria?

"We cannot take 1000"

Therefore, assuming that the NUMBERS are in column B,

=SUMIF(B:B,"<1000")

If all the values are im column A, why not first PARSE thems into 2 columns via Data > Text to columns > DELIMITED > SPACE

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close