Finding text, comparing values, and adding to existing

[tourless]

Hi Folks,

I have two sheets, 'Details' (contains invoice line items) and 'Summary' (contains invoice totals only. Both sheets contain values starting at row 6 and have intermittent spaces between sets of values. Details is separated by invoice numbers in column C and Summary is separated by customer numbers in column A. I would like to be able to look at the details sheet, column E and each time the words '(Textile Fee)' appears, take the value from column C of that row and find it on the summary sheet (also in column C), when found take the value of column E of the summary sheet and subtract it from the value in column J of the details sheet but one row below where we found the phrase '(Textile Fee)'. Then take the result of that calculation and add it to the value found in the details sheet, column I of the row where we found the phrase "(Textile Fee)'.

That's the logic, and here's the problem... By truncating column I of the my details sheet to only two digits past decimal, each invoice will total a few pennies off in most instances to the correct total listed on the summary sheet. To make up that difference I have the flexibility to add those pennies back to the textile fee line item on the details sheet. Right now that is still a manual process.

Not sure if I should open this as a new post but since it builds upon my original question I'm going to leave it here for now and leave it to the moderators to decide.

 

[tourless]

Here's what I've worked up so far but I don't think I should have to loop through every s value before returning to the d values. Other than that it works pretty well.

Sub AdjustFees()
    
    Dim d As Long
    Dim s As Long
    Dim lastRowD As Long
    Dim lastRowS As Long
    Dim invNumber As Long
    Dim dTotal As Currency
    Dim sTotal As Currency
    Dim tDiff As Currency
    Dim tFee As Currency
    'Dim ws As Worksheet
    
    wsTemplateD.Activate
    With ActiveSheet
        lastRowD = .Cells(.Rows.Count, "E").End(xlUp).Row
    End With
    
    For d = 6 To lastRowD
        If Range("E" & d).Value = "(Textile Fee)" Then
            invNumber = Range("C" & d).Value
            dTotal = Range("J" & d + 1).Value
            tFee = Range("I" & d).Value
            
            wsTemplateS.Activate
            
            With ActiveSheet
                lastRowS = .Cells(.Rows.Count, "E").End(xlUp).Row
            End With
            
            For s = 6 To lastRowS
                If Range("C" & s).Value = invNumber Then
                sTotal = Range("E" & s).Value
                MsgBox invNumber & " " & dTotal & " " & sTotal
                tDiff = sTotal - dTotal
                MsgBox tDiff
                wsTemplateD.Range("I" & d).Value = tFee + tDiff
                Else
                End If
            Next s
        Else
        End If
    Next d

End Sub

 

[DanteAmor]

In a copy of your file try the following macro, it is a little faster. The macro stores the data in an array, performs the operation and then deposits the result on the sheet.

I did not understand the problem "By truncating column I", but if in column "I" you leave the real value, then that value takes the macro and processes it.

After putting the result in the "cell of column I", you must put the "exit for" sentence so that it does not continue comparing with all the data in the Summary sheet.

Try and comment.

Sub AdjustFees()
  Dim a As Variant, b As Variant, c As Variant, i As Long, j As Long
  
  a = wsTemplateD.Range("A6:J" & wsTemplateD.Range("C" & Rows.Count).End(xlUp).Row + 1).Value
  b = wsTemplateS.Range("A6:J" & wsTemplateS.Range("C" & Rows.Count).End(xlUp).Row).Value
  ReDim c(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a, 1)
    c(i, 1) = a(i, 9)
    If a(i, 5) = "(Textile Fee)" Then
      For j = 1 To UBound(b, 1)
        If b(j, 3) = a(i, 3) Then
          c(i, 1) = c(i, 1) + b(j, 5) - a(i + 1, 10)
          Exit For
        End If
      Next
    End If
  Next
  wsTemplateD.Range("I6").Resize(UBound(c)).Value = c
End Sub

 

[tourless]

Thanks Dante, that's definitely faster and seems to do the trick. I still have some validation to do but I'm optimistic.

 

[DanteAmor]

Of course let me know any doubt.