CODEWITHSUNDEEP
vbaexcelexcel-formula
Lovemaths
Lovemaths

Reputation: 23

Issue with duplicated values in VBA

I would like to replace this formula with a function in VBA : =IFERROR(INDEX(SZCategoryData!E:E,MATCH(1,('SZCategory tailored'!B$3=SZCategoryData!F:F)*('SZCategory tailored'!A12=SZCategoryData!A:A),0)),"")

I used this function:

Sub BRM_ID1()
    For i = 2 To 224
        For j = 4 To 224  
            If Worksheets("SZCategoryData").Cells(i, 6).Value = "BRM_ID" Then
                Worksheets("SZCategory tailored").Cells(j, 2).Value = Worksheets("SZCategoryData").Cells(i, 5)
            End If
        Next
    Next
End Sub

I have column A ( Task_id ) : 1211,1211,1212,1213,1214 in my sheet SZCategoryData and column B ( BRM_ID associated to each task id ) that I need to copy from SZCategoryData to column C in another sheet SZCategory tailored.

Sometimes my task Id dosen't have an associated brm_id so the probleme with my code is that : it's copy values one after another without checking if it is associated to the right task id. For example my task id 1212 doesn't have a BRM_ID associated in the column B instead of keeping the cell empty it copies the BRM ID of 1213 ( the next one).

Upvotes: 2

Views: 83

Answers (1)

Brandon Barney
Brandon Barney

Reputation: 2392

I am not completely certain I understand your code, but hopefully this will get you a bit closer to a solution. As Mat's Mug correctly noted, you need more descriptive names with your variables. This makes it far easier to understand your code. It wouldn't hurt to turn on Option Explicit either.

Here's the modified code:

Sub BRM_ID1()
    Dim SourceData As Worksheet

    ' Highly recommend not relying on ActiveWorkbook. Only using it as a qualifier since that is the current qualifier (though implicit).
    Set SourceData = ActiveWorkbook.Worksheets("SZCategoryData")

    Dim TailoredData As Worksheet
    Set TailoredData = ActiveWorkbook.Worksheets("SZCategory tailored")

    Dim SourceRow As Long
    ' You're going to run into issues with the hardcoded min and max values here.
    For SourceRow = 2 To 224
        Dim DestinationRow As Long
        ' Here as well.
        For DestinationRow = 4 To 224
            ' Note that I am assuming that you want to match the value in TailoredData.Cells(DestinatioNRow, 6).
            ' You will need to adjust this depending on where your match value is.

            If SourceData.Cells(SourceRow, 6).Value = TailoredData.Cells(DestinationRow, 6).Value Then
                TailoredData.Cells(DestinationRow, 2).Value = SourceData.Cells(SourceRow, 5)
            End If
        Next
    Next
End Sub

If I am understanding your code and problem correctly, you were having issues because your code was simply checking if the value of a cell was equal to "BRM_ID". In reality, you need to be checking if the Task_ID of the TailoredData is equivalent to the Task_ID of the SourceData. I took a stab at correctly aligning this, but I have no clue where your task/brm_id's are stored. Your question said columns A, B, and C, but your indices (5, and 6) don't align to this.

Lastly, I would strongly recommend getting your hands dirty with arrays and dictionaries. Once you get this solution running, it will work, but it won't work for long. The code is fragile. In other words, if one detail changes, the code will cease to work correctly. For example, if the length of your data changes from 224 rows to 224,000 rows you will need to fix the code to reflect this (and expect a serious increase in processing time as well).

This will get you started with learning VBA, but I would strongly recommend working on improving the code further (or, ideally, work on improving your Excel skills and avoiding VBA as much as possible so that you are only solving problems with VBA that you can't reasonably solve with the built-in functionality Excel offers).

Best of luck!

Upvotes: 1

Related Questions