Help With VBA Code (Find and add code)

[Ammarbokhari]

Hi,
I am at below zero level when it comes to VBA so what you see is a recorded macro, which I failed to complete.
What this macro is supposed to do, is to search within given row (say Row7) for given value and when match (find) occurs, value at offset of 1 column is to be taken and added to the value which will come at next match (Find).
The initial modifications required in the below code are:
It should search within one row at a time, and when end of row is reached, it should stop.
When match occurs, pick the value next to matched cell.
Match (find) again, and the value next to matched cell added to the previous value, and repeat the process to the end of row or end of selection (which can be manually given (i.e to column AZ in row7).

Sub Macro3()
'
' Macro3 Macro
'

'
Cells.Find(What:="QTJ-001", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
End Sub

Thank you all in advance.

 

[pbornemeier]

Here is my code at what I think you are asking for:

Option Explicit 
 
Sub RowFind()
    Dim lLastColumn As Long
    Dim lFirstColumn As Long
    Dim oFound As Object
    Dim sFirstAddress As String
    Dim vSum As Variant
 
    If Selection.Rows.Count > 1 Or Selection.Areas.Count > 1 Then
        MsgBox "Select a single cell to examine an entire row or a contiguous range of cells in a single row to examine only that area."
        GoTo End_Sub
    End If
 
    If Selection.Cells.Count > 1 Then
        'If more than one cell in a row is selected, examine that range
        lFirstColumn = Selection.Column
        lLastColumn = Selection.Column + Selection.Columns.Count - 1
    Else
        'Examine entire selected row
        lFirstColumn = 1
        lLastColumn = Cells(Selection.Row, Columns.Count).End(xlToLeft).Column
    End If
    With Range(Cells(Selection.Row, lFirstColumn), Cells(Selection.Row, lLastColumn))
        Set oFound = .Find(What:="QTJ-001", After:=Cells(Selection.Row, lLastColumn), _
            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=True, SearchFormat:=False)
        If Not oFound Is Nothing Then
            sFirstAddress = oFound.Address
            Do
                'One and only one of the following two lines should be uncommented
                vSum = vSum + oFound.Offset(0, 1).Value 'Addition
                'vSum = vSum & oFound.Offset(0, 1).Value 'Concatenation
 
                Set oFound = .FindNext(oFound)
            Loop While Not oFound Is Nothing And oFound.Address <> sFirstAddress
        End If
    End With
 
    MsgBox vSum
 
End_Sub:
 
    Set oFound = Nothing
 
End Sub

If that code works for you, great, otherwise please comment on the following:

I do not understand what is to be done with the value that is offset by 1 column from the match:
"value at offset of 1 column is to be taken and added to the value which will come at next match (Find)."

1) What if there is no next match
2) By added do you mean the mathematical operation or a concatenation?
3) Are the cells to the right of each found cell in the row being modified or is the sum (or concatenation) of these cells being used for something after the row (or range) is finished?

Please use Excel Jeanie (see link in my sig) to show part of an example row.

 

[Ammarbokhari]

Hi pbornemeier,
Thank you for your time.
I am attaching screenshots of what I'm trying to do.
http://www.4shared.com/photo/kbtbxIDB/Daily_Man_hours.html
http://www.4shared.com/photo/yYrjnI_y/Job_Wise_Man_Hrs.html
It's basically salary sheet, in which we do entry on daily basis+ jobwise basis, and then it goes to jobwise manpower cost and also total monthly salary per person.
What I am trying to do is:
The module I am asking your help will be used in Job-wise man hour cost sheet.
And it will select the search criteria (Job-name) from the column B of this Job-wise man hour sheet.
Search is to be carried out in rows (one row at a time) as each worker has different per hour rate, and offset of 1,2,3,4, and 5 will be required as rate for B (basic hours), R/H (R. Hours), F (Friday Hours), Idle hours and OT (Overtime hour) may vary, (but they will be some specific multiple of per hour rate in Daily Man hour sheet of per Hour rate)
...
I have a similar code already; but it's giving me some error in value.
You can have a look at it, and if it requires any modifications, please let me know. (It is giving job cost on the higher side, difference is not huge but I'm trying to close it to zero)

Function MultiSumif(Looky As Range, Job As Range, Offset As Integer, OTRate As Double)

Application.Volatile

Dim lCount As Long, rFoundCell As Range, BasicHrs, OTHrs, Rate, TotalCost

Set rFoundCell = Looky.Columns(1).Cells(1, 1)

On Error Resume Next

For lCount = 1 To WorksheetFunction.CountIf(Looky, Job.Value)

    Set rFoundCell = Looky.Find(Job.Value, rFoundCell, xlValues, xlPart, xlByRows, xlNext, False)
    FoundAdd = rFoundCell.Address
    Hrs = rFoundCell.Offset(0, Offset)
    Rate = rFoundCell.Offset(0, 4 - rFoundCell.Column)
    
    TotalCost = TotalCost + Hrs * OTRate * Rate
    
Next lCount

MultiSumif = TotalCost

End Function
[\code]


and answer to your questions:
1. If there is no match up-to column AZ, same value is to be given. (e.g, 8 hrs)
2. Addition is mathematical addition (as I'm trying to add total hours spent by one person on a particular site, which in turn will be multiplied to his per hour rate to get total manpower cost of that job)
3. The values will be carried to another sheet where they will be displayed as, Basic hours, Friday hours, OT Hours and then Basic hour cost, Friday Cost, OT Cost ...
Hope I explained my example and sorry for not explaining it properly enough on the first attempt :-)
Thank you once again.