Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

offset function then sum is not working correctly

Status
Not open for further replies.

hrm1220

Technical User
Aug 31, 2005
134
US
I have a 2 sheets of data that I need to see if the criteria is met. I'm unable to add columns to either one of the sheets . I've tried the conditional format sum but couldn't get it to work due to the conditional format looking at a different sheet. So, I've tried this function below, but get a #Value error and hoping some one can go thru it and tell me where I went wrong.

Code:
Function GetSumFA(Rng As Range, factor As String) As Double
	[COLOR=green] Rng is in “Main” sheet[/color]
    Dim R As Range
    Dim rng_x_start As Range [COLOR=green]‘in sheet “test”[/color]
    Dim rng_xy_CF As Range [COLOR=green]‘in sheet “test”[/color]
    Dim rng_xyz_FA As Range [COLOR=green]‘in sheet “test”[/color]
    Dim Total As Double
    Dim cat As String
    Dim sumval As Double
    Dim boo As Boolean
    
    For Each R In Rng.Cells
        r_row = R.Row
        r_col = R.Column
        cat = Cells(r_row, 2).Value & Cells(r_row, 1).Value
        sumval = R.Value
        Set rng_xyz_FA = Application.Names("rng_xyz_FA ").RefersToRange
        Set rng_xy_CF = Application.Names("rng_xy_CF ").RefersToRange
        Set rng_x_start = Application.Names("rng_x_start ").RefersToRange
      If IsNumeric(R.Value) Then
         With test
             boo = rng_xyz _FA.Offset(Application.Match(cat, rng_xy_CF, _
             0) - 1, Application.Match(factor, rng_x_start, 0) - 26)
         End With
         If boo = False Then
          Total = Total + sumval
         End If
      End If
    Next R
    
    GetSumFA = Total
End Function


Thanks in advance for any help you can provide.
 



Hi,

Look at the ranges you are setting. Use the Watch Window to see is the value you are assigning to the range is REALLY a range. I'm guessing its NOT.
You also have a SPACE in your string...
Code:
        Set rng_xyz_FA = Application.Names([b]"rng_xyz_FA "[/b]).RefersToRange
        Set rng_xy_CF = Application.Names([b]"rng_xy_CF "[/b]).RefersToRange
        Set rng_x_start = Application.Names([b]"rng_x_start "[/b]).RefersToRange
ReferstoRange is a STRING I believe. Why not this?
Code:
        Set rng_xyz_FA = [rng_xyz_FA]
        Set rng_xy_CF = [rng_xy_CF]
        Set rng_x_start = [rng_x_start]
For that matter, why bother setting these ranges, when you can use the bracketed range names directly???

Skip,

[glasses] [red][/red]
[tongue]
 
Skip, thanks for your input. The offset calculation works. The part that isn't working is the summing. I'm not sure if this is due to the offset being done in the "test" sheet and the summing needing to be done in the "Main" sheet. The function is in the "Main" sheet and part of the criteria is being done in the "test" sheet. My question is does the function not work due to it looking at a different sheet ("test")to see if one part of the criteria
is met and if the other part of the criteria is met in the "Main" sheet, then sum?
 



"SUMMING" ususlly refers to the SUM function.

Your procedure does not seem to be performing arithmetic calculations.

Please post the code that you are currently using.

Step into your code.

Use the Watch WIndow to check the variables as they are being assigned.

Is boo ever false?

Skip,

[glasses] [red][/red]
[tongue]
 
Skip,

where the sum is in the code is
Code:
 If boo = False Then
          Total = Total + sumval
         End If
boo is false. It's looking up data and returning the value of TRUE or FALSE. The range is all calculations and so is it the fact that the range has calculations that is not able to sum them? Should sumval not equal to R.Value since r is a cell with calculations in it?

I appreciate your help and sorry for being vague.
 



You ought to be able to observe the value in sumval and Total by stepping thru your procedure.

"The range is all calculations..."

Do you mean that the range, Rng, contains FORMULAS? Should not make a difference.

You know, you might be able to accomplish what you want, either using the SUMIF spreadsheet function (if you have ONE criteria) or SUMPRODUCT is you have multiple criteria without any code at all.

Skip,

[glasses] [red][/red]
[tongue]
 
I'll try that approach. Thanks for your help.
 
Code:
Function GetSumFA(Rng As Range, factor As String) As Double
     'Rng is in “Main” sheet
    Dim R As Range
    Dim rng_x_start As Range    'in sheet “test”
    Dim rng_xy_CF As Range      'in sheet “test”
    Dim rng_xyz_FA As Range     'in sheet “test”
    Dim Total As Double
    Dim cat As String
    Dim boo As Boolean
    Dim iCol As Integer
    
    With test   [b]'these ranges do not change so should not be within the for...next[/b]
        Set rng_xyz_FA = .Range("rng_xyz_FA")
        Set rng_xy_CF = .Range("rng_xy_CF")
        Set rng_x_start = .Range("rng_x_start")
    End With
    [b]
    'Application.Match(factor, rng_x_start, 0) - 26  NEVER CHANGES -- set a ColVal variable
    iCol = Application.Match(factor, rng_x_start, 0) - 25
    [/b]
    For Each R In Rng.Cells
        With R
            If IsNumeric(.Value) Then
                cat = [b]Main.[/b]Cells(.Row, 2).Value & [b]Main.[/b]Cells(.Row, 1).Value   [b]'is the sheet MAIN or TEST???[/b]
                boo = rng_xyz_FA.Offset(Application.Match(cat, rng_xy_CF, 0) - 1, iCol)
                If boo = False Then
                   Total = Total + .Value
                End If
            End If
        End With
    Next R
    
    GetSumFA = Total
    [b]
    'release storage
    Set rng_xyz_FA = Nothing
    Set rng_xy_CF = Nothing
    Set rng_x_start = Nothing[/b]
End Function

Skip,

[glasses] [red][/red]
[tongue]
 
Skip,

Thanks for your help, but this still doesn't add up the "total=total+.Value" and I haven't be able to figure out why.

"cat = Main.Cells(.Row, 2).Value & Main.Cells(.Row, 1).Value" is on the "Main" sheet.

I'll continue working on this and if I figure it out I'll post the correct code.

Thanks again for tyring.
 



What values, at any one time, are assigned to R.value and Total, using the Watch Window?????

Skip,

[glasses] [red][/red]
[tongue]
 
Have you stepped through to make sure the boo is reading false?

Why are you adding 2 cells together

cat = Cells(r_row, 2).Value & Cells(r_row, 1).Value

and then looking them up
Have you checked to make sure the "cat" value is in the table and if you did mean to add them together is there a spacing issue.

ck1999
 
Skip,

when I look at the watch window I get the value use. When I type in "?R.value" in the immediate window I get the value. But when I step through the function R.value is blank.

ck1999,
yes I want the 2 values combined, and "cat" value is in the other sheet.
 



Well as you step thru and observe values, you ought to be able to see WHY the arithmetic is not happening.

Skip,

[glasses] [red][/red]
[tongue]
 
Skip,

Thanks for your help. Your code works. There was one cell that had the wrong data. I appreciate your time and patience with me.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top