jeudi 18 janvier 2018

Determine Value of a Cell by two variables

My Issue:

I want to loop through a range, and whenever it finds a duplicate value, it should take the amount from the cell with offset to the right of it, and add that to the value of the first Cells amount. I have a macro, which can determine if it finds a duplicate, but if it has several duplicates, then I want it to redo the task, but just continue to find the duplicates and add up the amount :)

What I have so far:

    Sub LoopThroughRangeAndFindDuplicates ()

    NumberOne = sht.Range("1:1").Find("Bilagsnr.").Address(False, False, xlA1)
    Number = Application.WorksheetFunction.Substitute(FindNo, 1, "")
    rng1 = NumberOne & ":" & Number & LastRow

    No1Off = sht.Range(NumberOne).Offset(1, 0).Address(False, False, xlA1)

For Each c In sht.Range(rng1).Cells
    rngOff1 = No1Off & ":" & Number & LastRow + 1
        With Wf
        n = .CountIfs(Range(rngOff1), c)
            If n > 0 Then
                CDuplicate = sht.Range(rngOff1).Find(c).Address(False, False, xlA1)
                CDuplicateSub = Application.WorksheetFunction.Substitute(CDuplicate, Number, Amount)
                CDuplicateSubValue = sht.Range(CDuplicateSub).Value
                CValue = c.Address(False, False, xlA1)
                CValueSub = Application.WorksheetFunction.Substitute(CValue, Number, Amount)
                CValueSubValue = sht.Range(CValueSub).Value
                sht.Range(CValueSub).Value = CValueSubValue + CDuplicateSubValue
            End If
    End With
    No1Off = sht.Range(No1Off).Offset(1, 0).Address(False, False, xlA1)
Next c

Goal:

As requested in the above, I want the macro to redo the If n > 0 Then if it has more duplicates than x. How should I write my code, to get it to look for several duplicates, and add them up, before continuing, without making a For Each string that will be really long? :)

Aucun commentaire:

Enregistrer un commentaire