fizcalpolicy
07-17-2010, 05:43 AM
Hi there,
I am embarrased to say I have been wracking my brain on this simple problem for 2 days without success. I am trying to place all values searched for, and found, into a one dimensional Array, then list the contents of the array in a specified range on the same worksheet. Is this possible, being that the cells found are non-contiguous, or in random locations?
Option Explicit
Sub FindExcess()
Dim Total As Long
Dim c As Range
Dim FirstAddress As String
Dim ToFind As String
ToFind = InputBox("Text to find", "Custom Search", "excess")
With ActiveSheet.Cells
Set c = .Find(ToFind, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Total = Total + c.Offset(, -1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
MsgBox Total
End Sub
I am embarrased to say I have been wracking my brain on this simple problem for 2 days without success. I am trying to place all values searched for, and found, into a one dimensional Array, then list the contents of the array in a specified range on the same worksheet. Is this possible, being that the cells found are non-contiguous, or in random locations?
Option Explicit
Sub FindExcess()
Dim Total As Long
Dim c As Range
Dim FirstAddress As String
Dim ToFind As String
ToFind = InputBox("Text to find", "Custom Search", "excess")
With ActiveSheet.Cells
Set c = .Find(ToFind, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Total = Total + c.Offset(, -1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
MsgBox Total
End Sub