Sumif not working on my macros but working when i am pressing F8

A

Ahmed786

New Member
Joined
Jun 2, 2022
Messages
26
Office Version
  1. 2016
Platform
  1. Windows
Hi Dear
i am new to VBA. when i am doing test by pressing F8 my macros are running fine but when i am running my macros worksheet function sumif is not calculating its showing zero
below is my macro
VBA Code: 
Dim sht As Worksheet
Dim shtName As String
Dim i As Long
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim SettlementRange As Range
Dim DoctorshareRange As Range

Set SettlementRange = Range("V:V")
Set DoctorshareRange = Range("Q:Q")

i = Sheets.Count

Dim cell As Range
 
  
For i = 1 To i

    If Sheets(i).Name = "vida data after macros" Then
       Sheets("vida data after macros").Select
       Set ws = Worksheets("vida data after macros")
        For Each cell In Range("G1:G6893")
        If InStr(cell.Value, "DR.ADLA BAKRI A. HASSAN") > 0 Then
            cell.Activate
            Exit For
        End If
     
    Next cell
 ActiveCell.Offset(1, -5).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
       Sheets("Dr.Adla Bakri").Select
    ActiveSheet.Range("A9").Select
    ActiveSheet.Paste
   With Selection
   .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
     
      
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
With Selection.Borders(xlInsideVertical)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Font
        .Name = "Arial"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
      Range("V1").Select
    ActiveCell.FormulaR1C1 = "Invoice"
    Range("V3").Select
    ActiveCell.FormulaR1C1 = "Settlement"
    Set rng1 = Range("V:V").Find("Invoice", SearchDirection:=xlPrevious)
          rng1.Activate
        ActiveCell.Offset(1, 0).Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(0, -5).Range("A1").Select
    ActiveCell = _
WorksheetFunction.SumIf(SettlementRange, "invoice", DoctorshareRange)
       Selection.Font.Bold = True
       ActiveCell.Select
    ActiveCell.Name = "Adlainvoice"
  Set rng2 = Range("V:V").Find("Settlement", SearchDirection:=xlPrevious)
     rng2.Activate
     ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -5).Range("A1").Select
       ActiveCell = _
   WorksheetFunction.SumIf(SettlementRange, "Settlement", DoctorshareRange)
      ActiveCell.Name = "Adlasettlement"
       Selection.Font.Bold = True
       Range("A4:V8").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("A5").Select
    Selection.NumberFormat = "[$-en-US]mmm-yy;@"
    Range("A8:V8").Select
  
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A8").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Font.Bold = True
  ActiveSheet.Cells.EntireColumn.AutoFit
  ActiveSheet.Columns("B").Delete
ActiveSheet.Columns("E").Delete
ActiveSheet.Columns("F:H").Delete
ActiveSheet.Columns("I").Delete
ActiveSheet.Columns("L").Delete
 Rows("1:3").Select
    Selection.Delete Shift:=xlUp
       End If
     Next i
      Sheets("vida data after macros").Select
For Each cell In Range("G1:G6893")
        If InStr(cell.Value, "DR.AHMED J. JAMAL") > 0 Then
            cell.Activate
            Exit For
        End If
     
    Next cell
 ActiveCell.Offset(1, -5).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
       Sheets("Dr.Ahmed Jamal").Select
    ActiveSheet.Range("A9").Select
    ActiveSheet.Paste
   With Selection
   .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
     
      
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
With Selection.Borders(xlInsideVertical)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Font
        .Name = "Arial"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
      Range("V1").Select
    ActiveCell.FormulaR1C1 = "Invoice"
    Range("V3").Select
    ActiveCell.FormulaR1C1 = "Settlement"
    Set rng1 = Range("V:V").Find("Invoice", SearchDirection:=xlPrevious)
          rng1.Activate
        ActiveCell.Offset(1, 0).Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(0, -5).Range("A1").Select
    ActiveCell = _
WorksheetFunction.SumIf(SettlementRange, "invoice", DoctorshareRange)
       Selection.Font.Bold = True
       ActiveCell.Select
    ActiveCell.Name = "Ahmedjamalinvoice"
  Set rng2 = Range("V:V").Find("Settlement", SearchDirection:=xlPrevious)
     rng2.Activate
     ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -5).Range("A1").Select
       ActiveCell = _
   WorksheetFunction.SumIf(SettlementRange, "Settlement", DoctorshareRange)
      ActiveCell.Name = "Ahmedjamalsettlement"
       Selection.Font.Bold = True
       Range("A4:V8").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("A5").Select
    Selection.NumberFormat = "[$-en-US]mmm-yy;@"
    Range("A8:V8").Select
  
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A8").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Font.Bold = True
  ActiveSheet.Cells.EntireColumn.AutoFit
  ActiveSheet.Columns("B").Delete
ActiveSheet.Columns("E").Delete
ActiveSheet.Columns("F:H").Delete
ActiveSheet.Columns("I").Delete
ActiveSheet.Columns("L").Delete
 Rows("1:3").Select
    Selection.Delete Shift:=xlUp
   
     Sheets("vida data after macros").Select
For Each cell In Range("G1:G6893")
        If InStr(cell.Value, "DR.JAMAL SALEH") > 0 Then
            cell.Activate
            Exit For
        End If
     
    Next cell
 ActiveCell.Offset(1, -5).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
       Sheets("Dr. Jamal Saleh").Select
    ActiveSheet.Range("A9").Select
    ActiveSheet.Paste
   With Selection
   .HorizontalAlignment = xlLeft
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
     
      
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
With Selection.Borders(xlInsideVertical)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Font
        .Name = "Arial"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
      Range("V1").Select
    ActiveCell.FormulaR1C1 = "Invoice"
    Range("V3").Select
    ActiveCell.FormulaR1C1 = "Settlement"
    Set rng1 = Range("V:V").Find("Invoice", SearchDirection:=xlPrevious)
          rng1.Activate
        ActiveCell.Offset(1, 0).Select
    Selection.EntireRow.Insert
    ActiveCell.Offset(0, -5).Range("A1").Select
    ActiveCell = _
WorksheetFunction.SumIf(SettlementRange, "invoice", DoctorshareRange)
       Selection.Font.Bold = True
       ActiveCell.Select
    ActiveCell.Name = "Jamalsalahinvoice"
  Set rng2 = Range("V:V").Find("Settlement", SearchDirection:=xlPrevious)
     rng2.Activate
     ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -5).Range("A1").Select
       ActiveCell = _
   WorksheetFunction.SumIf(SettlementRange, "Settlement", DoctorshareRange)
      ActiveCell.Name = "Jamalsalahsettlement"
       Selection.Font.Bold = True
       Range("A4:V8").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 11
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Range("A5").Select
    Selection.NumberFormat = "[$-en-US]mmm-yy;@"
    Range("A8:V8").Select
  
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A8").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Font.Bold = True
  ActiveSheet.Cells.EntireColumn.AutoFit
  ActiveSheet.Columns("B").Delete
ActiveSheet.Columns("E").Delete
ActiveSheet.Columns("F:H").Delete
ActiveSheet.Columns("I").Delete
ActiveSheet.Columns("L").Delete
 Rows("1:3").Select
    Selection.Delete Shift:=xlUp
   
   
End Sub
 
Last edited by a moderator:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Looks like you are patching code from various sources. The main problem is that you did not specify the reference properly.

For example:
1) Sheets("Sheet1").Range("A1")
2) Range("A1")

#1 has reference to a sheet named Sheet1.
#2 has no reference. Therefore, it will refer to any active sheet (Sheet being displayed at the moment).

Therefore, when running a macro, Activate or Select such as command Sheets("Sheet2").Activate will make Sheet2 active. However, this is not a good way to program. It slows down execution. You can get away by defining the reference in the beginning of the execution. The ActiveCell will refer to the cell on active sheet which might not the one cell you wanted.

You code has declaration
Dim sht As Worksheet
Dim shtName As String

However sht is not used at all in your code. At one point you have statement
Set ws = Worksheets("vida data after macros")

and the parameter ws was not declared. This makes me think that the code is patches from several sources.

The best was to code is to avoid using Activate or Select unless unavoidable. For example, you have declarations
Dim ws1 as Worksheet, ws2 as Worksheet
Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet1")

Instead of
ws1.Activate
Range("A1").Copy
ws2.Activate
Range("A1").PasteSpecial (xlPasteValues)

You can just use
ws2.Range("A1") = ws1.Range("A1")

It is hard to understand what you were trying to do looking at just the code there but try go through the code and make use the parameter declarations to revise and simplify your codes.
 
Upvote 0
Thank u very much. i will try my best to learn more.
sumif function result is not working properly. i have an idea that i should run macro 1 by 1 . just tell me how can i run several macros by one click
 
Upvote 0
Ahmed786 said:
Thank u very much. i will try my best to learn more.
sumif function result is not working properly. i have an idea that i should run macro 1 by 1 . just tell me how can i run several macros by one click
Click to expand...
You can create a master macro that will call other sub-macros in any order you like using Call function
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
576
RoryA
RoryA
K
  • Solved
formating question
Replies
3
Views
299
Micron
M
P
  • Question Question
Issue with button not running in spread sheet only in test mode
Replies
1
Views
360
6StringJazzer
6StringJazzer
K
  • Solved
create a button that do actions
2 3
Replies
22
Views
1K
kerm007
K
J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
996
kevin9999
K

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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
Back
Top