Search, Match, and SumTotal

[TTom]

I'm tweaking the following proceedure (see below - comments point where I need code help)

The proceedure finds the location for each cell that contains my specified search criteria, then proceeds to next until all are found. As each range/location (within my search range) is found, I need some code to grab the value not from the found range/location, but a cell offset from that range/location.

Example: if the search value exist at Range("A20"), then C.Address = $A$20 during test run.
I need to make myValue equal to the value in Range("B20").... or Offset(0,1) of C.Address.

In the end I'll have a sum total of values from offset of each range found within my search range that matches my serch criteria.

***

Sub Find_mySum()

mySearch = inputbox("Enter your search criteria")

With Worksheets(1).Range("myRange")
Set C = .Find(mySearch, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address

' thisValue = ???
' I need code here to use the range from either C.Address or firstAddress
' and make the variable "thisValue" equal to the value Offset(0,1) from the range.

Do
mySum = mySum + thisValue
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With

MsgBox (mySum)

End Sub

 

[TTom]

This is different code, likely sloppy as best solution, but it accomplishes my goal. I should define search range. I'm welcome to further input.
TTom

Sub Find_mySum()
On Error GoTo E

myvalue = InputBox("Enter value to search for.")
Range("A1").Activate 'One Cell above first search location in range

N: 'Next search until done (error found)
Cells.Find(What:=AICP, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate

If ActiveCell.Row = firstRow Then GoTo E

If C < 1 Then
firstRow = ActiveCell.Row
C = C + 1
End If

myValue = ActiveCell.Offset(0, 5).Value
mySum = mySum + myValue
GoTo N

E:
Range("A2").Select
msgbox(mySum)

End Sub

 

[sunnyland]

Hello,
Modifying your first post as follow works fine, didn't look at the second post:

Sub Find_mySum()

mySearch = InputBox("Enter your search criteria")
Dim mysum As Double
With Worksheets(1).Range("myRange")
Set c = .Find(mySearch, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mysum = mysum + c.Offset(0, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

MsgBox mysum

End Sub

 

[TTom]

Francoise (Sunnyland),

Thanks for the code.
Yes, yours works and is cleaner, more consise than my 2nd solution.
Have a great weekend.
TTom