While summing application defined error vba

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
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.

VBA Code:
                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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi
this line
VBA Code:
 Cells(2, 1).Offset(lr) = Application.Sum(Fnd)
 
Last edited:

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Or
VBA Code:
[b2].Offset(lr+1) = Application.Sum(Fnd)
 
Last edited:

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
Hi @mohadin both didn't work.

Below is my complete code
VBA 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-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Mar 26, 2019
Messages
308
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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
You are very welcome
Thank you for the feedback
Be Happy
 

Watch MrExcel Video

Forum statistics

Threads
1,127,323
Messages
5,624,014
Members
416,006
Latest member
PCaffrey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top