SumIF VBA Paste Values Help

[xaelic]

Hello

I'm new to using anything beyond simple copy paste functions in VBA. I want to do a sumif calculation using VBA where the output is values pasted in a defined range.

The goal is to copy a values in range with a fixed set of dates to another range which has more dates but have those values align with the column in which their original dates.

[TABLE="width: 600"]
<tbody>[TR]
[TD]31-Jan-18[/TD]
[TD]28-Feb-18[/TD]
[TD]31-Mar-18[/TD]
[TD]30-Apr-18[/TD]
[TD]31-May-18[/TD]
[TD]DatePaste[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]RangePaste[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31-Jan-18[/TD]
[TD]28-Feb-18[/TD]
[TD]30-Apr-18[/TD]
[TD]DateCopy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]ValuesCopy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The code I am using is in the latest excel :

Sub SumIFPaste()


Dim RangePaste As Range
Dim Datecopy As Range
Dim DatePaste As Variant
Dim ValuesCopy As Range




RangePaste = Application.WorksheetFunction.SumIf(Datecopy, DatePaste, ValuesCopy)


End Sub

I am new to this syntax on what is required to adapt this, if you could please show me how to make this work that would be amazing.

Thank you

X

 

[mumps]

Are the copy and paste ranges on the same sheet or on different sheets? Does the data start in row 1 of column A? Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[xaelic]

Hi

Thank you for responding. I uploaded the excel file here:

https://drive.google.com/open?id=1E6P5kla5aJmnZ9hsnGhYCNR-BYy3KPrq

The location of the copy and paste data are on different sheets and each range is defined by a name.

Except now the result keeps returning zeros.

Thanks again in advance for your help.

X

'Const RangePaste = "RangePaste"
Const DateCopy = "Datecopy"
Const DatePaste = "DatePaste"
Const ValuesCopy = "ValuesCopy"




Sub SumIFPaste()


Dim DatePaste As Variant
Dim RangePaste As Long


'Set DatePaste = DatePaste


'Set ValuesCopy = ValuesCopy


'Range(RangePaste).Value = Application.WorksheetFunction.SumIf(Range(DateCopy), DatePaste, Range(ValuesCopy))


RangePaste = WorksheetFunction.SumIf(Range(DateCopy), DatePaste, Range(ValuesCopy))




End Sub

 

[mumps]

Try:

Sub findDate()
    Application.ScreenUpdating = False
    Dim strdate As String
    Dim rDate As Range
    Dim foundDate As Range
    For Each rDate In Range("DateCopy")
        strdate = CStr(rDate)
        Set foundDate = Sheets("PasteDataHere").Rows(3).Find(Format(rDate, "d-mmm-yy"), LookIn:=xlValues, lookat:=xlWhole)
        If Not foundDate Is Nothing Then
            foundDate.Offset(1, 0) = rDate.Offset(1, 0)
        End If
    Next rDate
    Application.ScreenUpdating = True
End Sub

 

[xaelic]

Hi, thank you

I will try that but I actually figured out the issue moments before your post.

Occurred to me excel runs SUMIF cell by cell, you if you don't let the macro do that it wont work.

Below works beautifully. When I integrated this in a larger more convoluted macro I just needed to activate the sheet before executing this, and it also worked seamlessly.

Const DateCopy = "Datecopy"
Const ValuesCopy = "ValuesCopy"


Sub SumIFPaste()
Dim i As Integer
Dim t As Integer


t = 3


Do


i = 3


t = t + 1


If t > 6 Then
    Exit Do
End If


Do


Cells(t, i).Value = WorksheetFunction.SumIf(Range(DateCopy), Cells(3, i).Value, Range(ValuesCopy))


i = i + 1


If i > 13 Then
    Exit Do
End If


Loop


Loop


End Sub

 

[mumps]

Glad it worked out. )