While summing application defined error vba

[GirishDhruva]

Hi Everyone,

Below is my code where i am trying to find the worksheet name (which might differ month on month) and from that worksheet i need to get the sum of the particular column.

                Set targetWS = Sheets("Total_Summ_Workings")

''To Find the worksheet name
                For Each ws In ThisWorkbook.Sheets
                    If ws.Name Like "???'##" Then
                        ws.Activate
                        Set sourceWS = ActiveSheet
                        lr = sourceWS.Rows.Count
                        d = 1 & Split(ws.Name, "'")(0) & Split(ws.Name, "'")(1) ' takes month from the worksheet name
                    End If
                Next
                
''To Find the sum of particular column based on the column name

               
lrow = targetWS.Cells(Rows.Count, "A").End(xlUp).Row
                For i = 4 To lrow
                    StrFind = targetWS.Range("A" & i)
                    With sourceWS
                        Col = 0
                        On Error Resume Next
                        Col = .Cells.Find(StrFind).Column
                        On Error GoTo 0
                        If Not Col = 0 Then
                            Set Fnd = .Range(.Cells(2, Col), .Cells(lr, Col))
                            [B].Cells(lr + 1, Col) = Application.sum(Fnd)[/B] ' I am getting error in this row
                            .Cells(lr + 1, Col).Copy targetWS.Range("C" & i)
                        End If
                    End With
                Next i

If i run the above code i am getting an application defined error in the highlighted row

Could someone in this forum suggest me where i am going wrong and what changes should i make to run the code.

Regards,
Dhruva.

 

[mohadin]

Hi
this line

 Cells(2, 1).Offset(lr) = Application.Sum(Fnd)

 

[mohadin]

Or

[b2].Offset(lr+1) = Application.Sum(Fnd)

 

[GirishDhruva]

Hi @mohadin both didn't work.

Below is my complete code

Option Explicit
Sub try()

Dim targetWS As Worksheet
Dim sourceWS As Worksheet
Dim ws As Worksheet
Dim lr As Long
Dim d As Variant
Dim lrow As Long
Dim i As Long
Dim StrFind As Variant
Dim Col As Long
Dim Fnd  As Range

Set targetWS = Sheets("Total_Summ_Workings")

''To Find the worksheet name
For Each ws In ThisWorkbook.Sheets
    If ws.Name Like "???'##" Then
        ws.Activate
        Set sourceWS = ActiveSheet
        lr = sourceWS.Rows.Count
        d = 1 & Split(ws.Name, "'")(0) & Split(ws.Name, "'")(1) ' takes month from the worksheet name
    End If
Next
                
''To Find the sum of particular column based on the column name

               
lrow = targetWS.Cells(Rows.Count, "A").End(xlUp).Row
For i = 4 To lrow
    StrFind = targetWS.Range("A" & i)
    With sourceWS
        Col = 0
        On Error Resume Next
        Col = .Cells.Find(StrFind).Column
        On Error GoTo 0
        If Not Col = 0 Then
            Set Fnd = .Range(.Cells(2, Col), .Cells(lr, Col))
            .Cells(2, 1).Offset(lr) = Application.sum(Fnd) ' I am getting error in this row
            .Cells(lr + 1, Col).Copy targetWS.Range("C" & i)
        End If
    End With
Next i

End Sub

Thanks for your effort.

 

[mohadin]

Well,
The part modified if working ok
I dont know the variable coming from other part of your cod cause I dont see your workbook
so you may debug your code step by step or you may upload your file with dummy data to check it

 

[GirishDhruva]

Hi @mohadin,

It worked for me as i had missed a link with with one of my variable declared.

Thanks for your effort and time.

 

[mohadin]

You are very welcome
Thank you for the feedback
Be Happy