Excel: function prob, cell outside of range

[543212345]

What i'm trying to do:
I need to count a value in a cell outside of the range being passed to the function. Ie, i have a multiplier in the 3rd row of each column, and if there is a value in the column in the range, i want to add the multiplier.

So basically, i just need a way to use the y value to determine an absolute reference and get that value out of that cell. I tried using activate and offsets on activecell but i couldn't seem to get the right syntax to get what i wanted.

Any help would be greatly appreciated, thank you.

Public Function Countmult(r As Range)

    Dim i As Integer
    Dim x As Integer
    Dim y As Integer
    Dim Mult As Integer
    
    Mult = 1
    
    For x = 1 To r.Rows.Count
        For y = 1 To r.Columns.Count
            If (r.Cells(x, y).Value > 0) Then
                ' Get the value from row 3, column y in spreadsheet
                ' assign it to Mult
                                
                i = i + (raidMult)            
            End If
        Next y
    Next x
    
    Countmult = i
    
End Function

 

[Zathras]

Assuming the range always begins below row 3:
[blue]

  i = i + ActiveSheet.Cells(3, r.Cells(x, y).Column)

[/color]

 

[TonyJollans]

Hi 543212345,

Your problem, I think, is because r.Cells(x, y) gives you the cell in row x, column y relative to the start of your range, and you want to use the absolute column number. This should get you the cell you require:

Cells(3, r.Cells(2, 3).Column)

Enjoy,
Tony

 

[TonyJollans]

Zathras .. you type quicker than me!

 

[543212345]

Excellent, that is exactly what i needed.

Thank you Tony and Zathras