applying a formula to single column in an array

[shevy]

Hi
I currently have some code which is selecting 5 cells from a row where one of those cells is a specific number, and copying that data to a new sheet. The source file and cells contain some billing information and the output destination file is an invoice. My source file contains information for many customers, so my copy selection code copies data for each individual customer to a new sheet so I get an invoice for each customer. Here is my current code:

Set SrcRng = Workbooks("SOURCE DATA.XLS").Worksheets("Specific Data").UsedRange
Set DstRng = Workbooks("INVOICES.xls").Worksheets("Customer #2").Range("A10")

    KeepColumns = Array("A", "B", "C", "E", "D")
    MatchColumn = "C"
    MatchValue = 2

      For Each Cell In SrcRng.Columns(MatchColumn).Cells
        If Cell.Value = MatchValue Then
           R = Cell.Row
           C = 0
           For Each Col In KeepColumns
             DstRng.Offset(N, C) = SrcRng.Cells(R, Col)
             C = C + 1
           Next Col
           N = N + 1
        End If
      Next Cell

Column E in my source data contains my employees hours. When I bill my customers and invoice them I bill 1.5 hours of travel time separately, so I need to deduct that time from my employees hours on the invoice.
Basically I need to subtract 1.5 from each cell in column E when it pastes to the new inovice workbook.

How can I accomplish this?


Any and all help is much appreciated.
Thanks!!!!

[shg]

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

[shg]

Maybe like this. It includes some assumptions on my part.

The Intersect accommodates the case where the UsedRange does not start in col A.

Sub x()
    Const sCol      As String = "C"
    Const iCust     As Long = 2

    Dim rSrc        As Range
    Dim rDst        As Range
    Dim cell        As Range

    Dim avCol       As Variant
    Dim vCol        As Variant

    Dim iRow        As Long
    Dim iRowOfs     As Long
    Dim iColOfs     As Long

    Set rSrc = Workbooks("SOURCE DATA.XLS").Worksheets("Specific Data").UsedRange
    Set rDst = Workbooks("INVOICES.xls").Worksheets("Customer #2").Range("A10")

    avCol = Array("A", "B", "C", "E", "D")

    For Each cell In Intersect(rSrc, rSrc.Parent.Columns(sCol)).Cells
        If cell.Value = iCust Then
            iRow = cell.Row
            iColOfs = 0
            
            For Each vCol In avCol
                rDst.Offset(iRowOfs, iColOfs).Value = rSrc.Cells(iRow, vCol).Value - IIf(vCol = "E", 1.5, 0)
                iColOfs = iColOfs + 1
            Next vCol
            
            iRowOfs = iRowOfs + 1
        End If
    Next cell
End Sub