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.
Thanks in advance for any help you can provide.
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.