WorksheetFunction.Sum not working

[HimanshuK]

Hi All,

I am trying to run the below code. All variables are getting value except 'rsum"

Dim k As Integer, rsum As Double    
Dim svalue As String    
Dim numcol As Long   
 Dim strrng As Variant   
 Dim endrng As Variant        
   k = 7           

   Do While Sheets("BG").Cells(k, 11) <> ""     
svalue = Sheets("BG").Cells(k, 11).Value      
numcol = Application.WorksheetFunction.Match(svalue, Sheets(ws).range("$1:$1"), 0)        
strrng = Sheets(ws).Cells(rstr, numcol).Address(False, False)       
endrng = Sheets(ws).Cells(rend, numcol).Address(False, False)                       
rsum = Application.WorksheetFunction.Sum(range(strrng, endrng))        
Sheets("bg").Cells(k, 12).Value = rsum                
k = k + 1        
Debug.Print svalue        
Debug.Print strrng        
Debug.Print endrng        
Debug.Print k       
 Debug.Print rsum                        
Loop    
End Sub

Can anyone give me ideas why? I am using debug.print to get values. Except for rsum everything else seems okay!!

 

[Fishboy]

Hi All,

I am trying to run the below code. All variables are getting value except 'rsum"

Dim k As Integer, rsum As Double    
Dim svalue As String    
Dim numcol As Long   
 Dim strrng As Variant   
 Dim endrng As Variant        
   k = 7           

   Do While Sheets("BG").Cells(k, 11) <> ""     
svalue = Sheets("BG").Cells(k, 11).Value      
numcol = Application.WorksheetFunction.Match(svalue, Sheets(ws).range("$1:$1"), 0)        
strrng = Sheets(ws).Cells(rstr, numcol).Address(False, False)       
endrng = Sheets(ws).Cells(rend, numcol).Address(False, False)                       
rsum = Application.WorksheetFunction.Sum(range(strrng, endrng))        
Sheets("bg").Cells(k, 12).Value = rsum                
k = k + 1        
Debug.Print svalue        
Debug.Print strrng        
Debug.Print endrng        
Debug.Print k       
 Debug.Print rsum                        
Loop    
End Sub

Can anyone give me ideas why? I am using debug.print to get values. Except for rsum everything else seems okay!!

Hi HimanshuK,

Try changing this line:

rsum = Application.WorksheetFunction.Sum(range(strrng, endrng))

To this:

rsum = Application.WorksheetFunction.Sum(Sheets(ws).range(strrng, endrng))

 

[GTO]

Cross-posted Here

HimanshuK, please read Here