• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code for sum the value with two criteria

Hi

The below linked excel file Module1, I have a below code to find out the Yellow highlighted cells without any values in that,

After finding those cells I need to check each highlighted cells intersect column "A" Value (Date) and then it has to check those dates in another sheet then respective RM2 to RM52 has to sum up the values in the highlighted cells.

Expected results are shown in D7 and D22. Likewise, I need to get the values in all the yellow highlighted cells.


Thanks in advance.

Code:
Option Explicit

Sub SelectColoredCells()
    Dim rCell As Range, PDate As Range
    Dim lColor As Long
    Dim rColored As Range
    Dim High As Date

    lColor = RGB(255, 255, 0)

    Set rColored = Nothing
    For Each rCell In Sheet6.Range("D1:CY300")
        If rCell.Interior.Color = lColor And rCell.Value = "" Then
            If rColored Is Nothing Then
                Set rColored = rCell
            Else
                Set rColored = Union(rColored, rCell)
            End If
        End If
    Next
    If rColored Is Nothing Then
        MsgBox "No cells match the color"
    Else
        rColored.Select
    End If       
End Sub
 
With the below code Pick_Data, I just find the left last cell date value and top last cell RM number showing in the MsgBox. The below code I have created after watching the macro recording steps.

After finding the intersect date and RMName, How do I get the desired results as mentioned in my previous post?

Guide me to get the desired result.

Thanks in advance.


Code:
Option Explicit

Sub Find_Cells()
    Dim rCell As Range
    Dim lColor As Long
    Dim rColored As Range

    lColor = RGB(255, 255, 0)
    Set rColored = Nothing
    For Each rCell In Sheet6.Range("D1:CY300")
        If rCell.Interior.Color = lColor And rCell.Value = "" Then
            If rColored Is Nothing Then
                Set rColored = rCell
            Else
                rColored.Select
            End If
        End If
    Next
    If rColored Is Nothing Then
        MsgBox "No cells match the color"
    Else
        rColored.Select
    End If
End Sub

Sub Pick_Data()
Dim Fdate As Date
Dim Frname As Variant

Call Find_Cells

    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    
    
    Fdate = ActiveCell.Value

Call Find_Cells

    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, -1).Range("A1").Select
    
    Frname = ActiveCell.Value
    MsgBox ("Date is" & Fdate & " RM Name is " & Frname)

 
End Sub
 
Wait if someone decyphers your need or explain technically at least how you operate manually to obtain your result​
in order even seven years old children clearly catch your need …​
 
In the cells, D22 the below excel formula gives the desired result

=SUMIF('Master Data'!$B$9:$B$9580,"<"&A22,'Master Data'!$CH$9:$CH$10170)


Like D22 I have more than 1000 cells in the same sheet which is highlighted in yellow colour and with no values.

I want the same results in all yellow highlighted cells like D22.
 
For example what must be the formula in D7 ?​
Why don't you create a formula in the first row data - #2 - then copy it down ?​
 
In the cell C2: C6 the RM Prices are same and C7 got changed, So D7 I need to show the sum value from sheets Master Data RM2 up to A6 date.

In this case, D1 to D6 must be blank and D7 has to show values. Like that all the yellow highlighted cells must show the values.
 
A starter demonstration based on column D to paste to the Sheet6 (RM Price) worksheet module :​
Code:
Sub Demo1()
       Dim F$, Rg As Range, R&
    With Sheet5
        F = Replace(Replace("=SUMIF('#N'!$B$8:$B$#L,""<""&A#,'#N'!$CH$8:$CH$#L)", "#N", .Name), "#L", .[B7].End(xlDown).Row)
    End With
    With Application.FindFormat
        .Clear
        .Interior.ColorIndex = 6
    With Me.UsedRange.Columns(4)
           Set Rg = .Find("", SearchFormat:=True)
        If Not Rg Is Nothing Then
                   R = Rg.Row
            Do
                       Rg.Formula = Replace(F, "#", Rg.Row)
                   Set Rg = .Find("", Rg, SearchFormat:=True)
            Loop Until Rg.Row = R
                   Set Rg = Nothing
        End If
    End With
        .Clear
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
As I usually post only working code relative to the attachment …​
But it seems to not need any code just using a smart formula which is faster than waiting for any code on a VBA forum !​
 
Back
Top