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

[SnayJ]

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).

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

 

[SkipVought]

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???

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's actually all you need.

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

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" & [highlight #FCE94F](ActiveCell.Row)[/highlight]).Value and it should have been Bealtotalsplit = Bealtotalsplit + TheSheet.Range("W" & [highlight #FCE94F](row_review))[/highlight].Value.

Thanks man, it's working now.

SnayJ

 

[SkipVought]

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



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

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.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Since you are "New to VBA coding in Excel", a couple of points/suggestions:
1. Always use [tt]Option Explicit[/tt] 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]RED[/red] variables here are Veriants:

Dim TheSheet As Worksheet
Dim [red]LastRow, Licnum[/red]
Dim Bealtotalsplit As Currency
Dim row_review As Long, [red]item_in_review[/red]

Have fun.

---- Andy

There is a great need for a sarcasm font.