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

Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

(OP)

New to VBA coding in Excel, I can't seem to figure out how to code this.

I want to go thru each row of the "Office View" worksheet. In this table, column C is for License Numbers and column W is for Bonuses (currency).

The code I'm using below is resulting in only the Column W Value for the ActiveRow, not from looping.

Also I run this code from a button. I click on a cell to choose the ActiveRow to transfer certain cell data to a from. But want to add up a filtered count of Column W and put it on the form. The filter is equal to the Licnumber of the ActiveRow. (Found in Column C).


CODE

row_review = 2
    Dim TheSheet As Worksheet
    Set TheSheet = Sheets("Office View")
    Dim LastRow, Licnum
    Dim Bealtotalsplit As Currency
    LastRow = Sheets("Office View").Range("A" & Rows.Count).End(xlUp).Row
    Licnum = Sheets("Office View").Range("C" & (ActiveCell.Row)).Value
    Bealtotalsplit = 0
    
    Do
    DoEvents
    row_review = row_review + 1
    item_in_review = TheSheet.Range("C" & row_review)
        If item_in_review = Licnum Then
            Bealtotalsplit = Bealtotalsplit + Sheets("Office View").Range("W" & (ActiveCell.Row)).Value
        End If
    Loop Until LastRow
    
    OFFICEVIEWTRANSfrm!BEALSPLITbox.Value = Format(Bealtotalsplit, "CURRENCY") 

Appreciate the help.


SnayJ

RE: Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

Hi,

My changes to your code:
1) declare ALL variables prior to any executable code
2) You Set TheSheet object. Then use it in all cases and finally Set object(s) to Nothing.
3) Your Loop Until had no expression Loop Until LastRow = row_review
4) I really don't understand why you're looping at all. You already have a selected row and that's the only row that gets executed in your If statement???

CODE

'
    Dim TheSheet As Worksheet
    Dim LastRow, Licnum
    Dim Bealtotalsplit As Currency
    Dim row_review As Long, item_in_review
    
    Set TheSheet = Sheets("Office View")
    row_review = 2
    LastRow = TheSheet.Range("A" & Rows.Count).End(xlUp).Row
    Licnum = TheSheet.Range("C" & (ActiveCell.Row)).Value
    
    Bealtotalsplit = Bealtotalsplit + TheSheet.Range("W" & (ActiveCell.Row)).Value
    
'.........
    Set TheSheet = Nothing 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

Here's actually all you need.

CODE

'
    Dim Bealtotalsplit As Currency
            
    Bealtotalsplit = Sheets("Office View").Range("W" & (ActiveCell.Row)).Value 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

(OP)
Skip,

Appreciate the help. I got it working with your comment on the Loop Expression. Plus your confusion on my If statement got me to see my mistake.

I had Bealtotalsplit = Bealtotalsplit + TheSheet.Range("W" & (ActiveCell.Row)).Value and it should have been Bealtotalsplit = Bealtotalsplit + TheSheet.Range("W" & (row_review)).Value.

Thanks man, it's working now.

SnayJ

RE: Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

But still, the only row that your If allows, is the row of yeh ActiveCell. Same outcome.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

(OP)
No Skip, when I changed it to Row.Review it works fine. It cycles thru each record and adds column W from the row being viewed... not the active row. Which is what I wanted. Thanks though.

RE: Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

Well you never submitted a sample of the data you're processing, so we're kind of hampered.

The only case where you would get more than one row is if you have multiple values in column C, and the selected row had a value in column C and that value existed in more than one row.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Code help... looping thru range, checking for criteria in 1 cell, using data in another cell to add.

Since you are "New to VBA coding in Excel", a couple of points/suggestions:
1. Always use Option Explicit at the top of your code. To automate it, in VBA editor go to Tools - Options... and on the Editor tab, check "Require Variable Dec;aration" checkbox.
2. Always declare a variable As (whatever type you need). Very seldom you will need a Variant, but if you don't specify the type, Variant is what you get.

All RED variables here are Veriants:

CODE

Dim TheSheet As Worksheet
Dim LastRow, Licnum
Dim Bealtotalsplit As Currency
Dim row_review As Long, item_in_review 

Have fun.

---- Andy

There is a great need for a sarcasm font.

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