Simple Excel Macro Needed: Copy/Paste Data to a Specific Location Based on Multiple Criteria

Question

CONTEXT:

I have an empty database set up in Excel which I plan to use to keep track of my monthly expenses for budgeting purposes. What I would like to be able to do is open my spreadsheet, enter in the data from my receipt (date of purchase, type of purchase 'code', and the amount of purchase) and click my "Post Data" macro button to run this macro. I know how to set all of that up, I am just having trouble with writing the macro itself.

THIS IS WHAT I AM LOOKING FOR:

A macro that:

Finds the correct row which has the date in cell C2 in column E (i.e. row 8 in my example)
Finds the correct column based on the expense code in cell C2 within the range F2:M2 (i.e. column G in my example)
Pastes the value I have input in cell C4 into the intersection of the aforementioned row and column (i.e. Cell G8 in my example)

WHAT HAS NOT WORKED:

I am able to get the desired result by generating the IF statement (in each of the data cells):

=IFERROR(IF(AND(E5=$C$2,F$2=$C$3),$C$4,""),"")

I was able to record this macro which copies and pastes this statement to all of my data fields (I have an entire year on my spreadsheet). After that, the macro copies and pastes values over the statements. The only problem is that this macro overwrites data previously entered, which is why I felt I needed a more complex macro.

Please point me in the right direction or where I can find out how to do this.

Screenshot (what I am trying to do):

Macro:

Sub CopyPasteData()
'
' CopyPasteData Macro
'

'
    Range("F5").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(IF(AND(RC5=R2C3,R2C=R3C3),R4C3,""""),"""")"
    Range("F5").Select
    Selection.Copy
    Range("F5:M32").Select
    ActiveWindow.SmallScroll Down:=24
    Range("F5:M32,F36:M66").Select
    Range("F36").Activate
    ActiveWindow.SmallScroll Down:=27
    Range("F5:M32,F36:M66,F70:M99").Select
    Range("F70").Activate
    ActiveWindow.SmallScroll Down:=21
    Range("F5:M32,F36:M66,F70:M99,F103:M133").Select
    Range("F103").Activate
    ActiveWindow.SmallScroll Down:=21
    Range("F5:M32,F36:M66,F70:M99,F103:M133,F137:M166").Select
    Range("F137").Activate
    ActiveWindow.SmallScroll Down:=24
    Range("F5:M32,F36:M66,F70:M99,F103:M133,F137:M166,F170:M200").Select
    Range("F170").Activate
    ActiveWindow.SmallScroll Down:=21
    Range("F5:M32,F36:M66,F70:M99,F103:M133,F137:M166,F170:M200,F204:M234").Select
    Range("F204").Activate
    ActiveWindow.SmallScroll Down:=24
    Range( _
        "F5:M32,F36:M66,F70:M99,F103:M133,F137:M166,F170:M200,F204:M234,F238:M267"). _
        Select
    Range("F238").Activate
    ActiveWindow.SmallScroll Down:=21
    Range( _
        "F5:M32,F36:M66,F70:M99,F103:M133,F137:M166,F170:M200,F204:M234,F238:M267,F271:M301" _
        ).Select
    Range("F271").Activate
    ActiveWindow.SmallScroll Down:=21
    Range( _
        "F5:M32,F36:M66,F70:M99,F103:M133,F137:M166,F170:M200,F204:M234,F238:M267,F271:M301,F305:M334" _
        ).Select
    Range("F305").Activate
    ActiveWindow.SmallScroll Down:=24
    Range( _
        "F5:M32,F36:M66,F70:M99,F103:M133,F137:M166,F170:M200,F204:M234,F238:M267,F271:M301,F305:M334,F338:M368" _
        ).Select
    Range("F338").Activate
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveWindow.ScrollRow = 344
    'deleted many lines...
    ActiveWindow.ScrollRow = 5
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-12
    Application.CutCopyMode = False
    Range("F5:M32").Select
    ActiveWindow.SmallScroll Down:=-9
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=18
    Range("F36:M66").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-15
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=27
    Range("F70:M99").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-12
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=27
    Range("F103:M133").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-9
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=27
    Range("F137:M166").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-9
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=27
    Range("F170:M200").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-12
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=24
    Range("F204:M234").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-15
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=36
    Range("F238:M267").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-6
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=27
    Range("F271:M301").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-12
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=33
    Range("F305:M334").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-12
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=27
    Range("F338:M368").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-12
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollRow = 332
    'deleted many lines
    ActiveWindow.ScrollRow = 7
    ActiveWindow.ScrollRow = 5
    Range("A1").Select
End Sub

Thank you for the information, as you can see I am brand new to StackOverflow. I will include what I have tried (what has not worked) so far momentarily. I appreciate your patience. — Viktor, Feb 05 '15 at 05:34
Hi Tim, thanks for the question. The way I enter my receipts, I will group them together if they happen to be on the same day, eliminating this problem. (i.e. bought several groceries totaling $100 on 2/6/2015; I will simply enter $100 for the date and file my receipts as backup). — Viktor, Feb 05 '15 at 06:05
Looks like there is little tolerance for being new on this site... I have accommodated every recommended change/edit to my very first post and have received nothing but stern warnings and downvotes. — Viktor, Feb 05 '15 at 06:15

score 0 · Accepted Answer · answered Feb 05 '15 at 06:55

0

You need to use Find() to locate the correct column and row:

Sub Test()

    Dim sht As Worksheet
    Dim fD As Range, fT As Range, dt

    Set sht = ActiveSheet
    dt = CDate(sht.Range("C2").Value)
    Set fD = sht.Range("E5:E1000").Find(dt)
    Set fT = sht.Range("F2:M2").Find(sht.Range("C3").Value, lookat:=xlWhole)

    If Not fD Is Nothing And Not fT Is Nothing Then
        With sht.Cells(fD.Row, fT.Column)
        .Value = .Value + sht.Range("C4").Value
        End With
    End If

End Sub

answered Feb 05 '15 at 06:55

Tim Williams

154,628
8
97
125

Thank you very much Tim, this is exactly what I was looking for. I had tried using the `Ctrl+F` functionality in previous attempt to record a macro that would work but ultimately I could not get that to work either. I plan on using `F8` on this to go line by line on what you have written to help me learn the components of the VBA language you have introduced to me here. Again, my sincerest gratitude goes out to you. This may have been a simple task for you (judging by the speed at which you completed it), but it means a lot. Thanks for making my first SO experience a great one! – Viktor Feb 05 '15 at 16:04
It can be hard getting started here - takes a little while to learn how to ask... – Tim Williams Feb 05 '15 at 17:07

Simple Excel Macro Needed: Copy/Paste Data to a Specific Location Based on Multiple Criteria

1 Answers1