CODEWITHSUNDEEP
vbaexcel
user3088476
user3088476

Reputation: 155

using a formula in vba to compare cells and enter results in another cell

I going to use a formula in VBA to compare to columns of data and if a match is found then enter some data from one sheet to another. I have used a formula on the first row and this works I get the desired results, I want to automate it as this will be part of other automations on the report. I have got some code which enters a column heading then applies the sumif function to the entire column but I get the same results all the way down, it is the results for the first row.

Code

Sub ImportCosting()

    Dim z As Workbook
    Dim x As Workbook

    Set x = Workbooks.Open("C:\Documents\Reports\MEP.xlsx")

    With Workbooks("MEP")
        Worksheets("DynamicReport").Range("P5").Value = "Budget"
        Worksheets("DynamicReport").Range("Q5").Value = "Forecast"
    End With

    Set z = Workbooks.Open("C:\Documents\Reports\Budget.xlsx")

    With x.Worksheets("DynamicReport")
        lastRow = .Cells(Rows.Count, 5).End(xlUp).Row
        For Each rng In .Range("P6:P" & lastRow)
            rng.Formula = "=SUMIF('[Budget.xlsx]DynamicReport'!$C:$C,$B6,'[Budget.xlsx]DynamicReport'!H:H)"
            rng.Value = rng.Value
        Next rng
        End With
End sub

Hope I have explained the problem correctly. Can anyone tell me where I have gone wrong and how to get it so that the sumif function is applied to each row and not the results for the first row repeated.

Thanks

Upvotes: 0

Views: 410

Answers (2)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

pls try below

Sub ImportCosting()
    Dim z As Workbook
    Dim x As Workbook
    Set x = Workbooks.Open("C:\Documents\Reports\MEP.xlsx")
    With Workbooks("Bank.xlsx")
        Worksheets("DynamicReport").Range("P5").Value = "Budget"
        Worksheets("DynamicReport").Range("Q5").Value = "Forecast"
    End With
    Set z = Workbooks.Open("C:\Documents\Reports\Budget.xlsx")
    With x.Worksheets("DynamicReport")
        lastRow = .Cells(Rows.Count, 5).End(xlUp).Row '"E"
        For Each Rng In .Range("P6:P" & lastRow)
            Rng.Formula = "=SUMIF([Budget.xlsx]DynamicReport!$C:$C,[Budget.xlsx]DynamicReport!$B$" & Rng.Row & ",[Budget.xlsx]DynamicReport!H:H)"
            'Rng.Value = Application.WorksheetFunction.SumIf(z.Worksheets("DynamicReport").Range("C:C"), z.Worksheets("DynamicReport").Range("B" & Rng.row), z.Worksheets("DynamicReport").Range("H:H"))
            Rng.Value = Rng.Value
        Next Rng
        End With
End Sub

Upvotes: 0

Stupid_Intern
Stupid_Intern

Reputation: 3450

I am not sure but I guess you want to change the B6 to the rng.row

I guess you are getting the same result in every cell of rng because you are putting B6 as the criteria for sum so change that to the corresponding row number in the B column so that you get the desired result.

Change this:

rng.Formula = "=SUMIF('[Budget.xlsx]DynamicReport'!$C:$C,$B6,'[Budget.xlsx]DynamicReport'!H:H)"

to this

rng.Formula = "=SUMIF('[Budget.xlsx]DynamicReport'!$C:$C,$B" & rng.Row & ",'[Budget.xlsx]DynamicReport'!H:H)"

Upvotes: 1

Related Questions