# Search, Match, and SumTotal

#### TTom

##### Well-known Member
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

' 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)
End If
End With

MsgBox (mySum)

End Sub

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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

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

Code:
``````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
Do
mysum = mysum + c.Offset(0, 1)
Set c = .FindNext(c)
End If
End With

MsgBox mysum

End Sub``````

Francoise (Sunnyland),

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

Replies
21
Views
663
Replies
2
Views
393
Replies
1
Views
261
Replies
2
Views
320
Replies
18
Views
1K

1,203,690
Messages
6,056,753
Members
444,889
Latest member
ibbara

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back