Does anyone have any suggestions for the following:

Beginning with the third sheet in the workbook I would like:

If any Cell in Column A = "Variance" (for all sheets in workbook
Then:

Copy the Cell in Colum "C" (in the same row as the text "Variance" i
Column A) to :
Column A beginning with Row 10 in the Sheet named Variance in th
workbook.

Any help would be greatly appreciated

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=50657

Maybe...

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim DestCell As Range
Dim VarWks As Worksheet
Dim iCtr As Long
Dim FoundCell As Range
Dim WhatToFind As String
Dim FirstAddress As String

WhatToFind = "variance"

Set VarWks = Worksheets("Variance")
Set DestCell = VarWks.Range("a10")

For iCtr = 3 To Worksheets.Count
Set wks = Worksheets(iCtr)
With wks
If .Name < VarWks.Name Then
FirstAddress = ""
With .Range("a:a")
Set FoundCell = .Find(what:=WhatToFind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
DestCell.Value = FoundCell.Offset(0, 2).Value
Set DestCell = DestCell.Offset(1, 0)
Set FoundCell = .FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If
End With
End If
End With
Next iCtr

End Sub

STEVEB wrote:

Does anyone have any suggestions for the following:

Beginning with the third sheet in the workbook I would like:

If any Cell in Column A = "Variance" (for all sheets in workbook)
Then:

Copy the Cell in Colum "C" (in the same row as the text "Variance" in
Column A) to :
Column A beginning with Row 10 in the Sheet named Variance in the
workbook.

Any help would be greatly appreciated!

--
STEVEB
------------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...fo&userid=1872
View this thread: http://www.excelforum.com/showthread...hreadid=506572

--

Dave Peterson

Point of interest:

Which is faster in searching through a Range? The *Find Method* as in
Dave's code vs. *For each c in Rng... Next *construct.

Myles.


--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=506572

I would guess that the .find is much faster--unless the number of matches
approaches the number of cells.

Myles wrote:

Point of interest:

Which is faster in searching through a Range? The *Find Method* as in
Dave's code vs. *For each c in Rng... Next *construct.

Myles.

--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=506572

--

Dave Peterson

Dave,

Can I then infer that whereas the code has to touch every cell in
Range with the For each...Next code, regardless, it only does so
conditionally when a criterion is met with the Find Method. Sound
intuitive.

Myles

--
Myle
-----------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...fo&userid=2874
View this thread: http://www.excelforum.com/showthread.php?threadid=50657

Dave,

Thanks so much for the help!! Everything worked great & it saved me s
much time!, I really appreciate it!

I was wondering if it was possible to expand the code to include th
following:

If it finds "variance" and posts the # in column C in column A in th
Sheet Variance (The current code already does this) could it also pos
in the Varaince sheet:

Whatever # is in cell A2 to column B (The same row that the # jus
posted with the currect code) and Whatever # is in Cell A3 to colum
C.

For Example:

Sheet 3 finds two "variance" in column A and posts the # in Column C t
Row 10 and Row 11 on the Sheet Variance. I would like the code to pos
the value of Cell A2 Sheet 3 to column B Row 10 & 11 and the Value o
Cell A3 in column B Row 10 & 11. (Complete this for all shhets i
Workbook)

Thanks again for your help, I really appreciate it

--
STEVE
-----------------------------------------------------------------------
STEVEB's Profile: http://www.excelforum.com/member.php...nfo&userid=187
View this thread: http://www.excelforum.com/showthread.php?threadid=50657

Yep.

You could fill up column A with ASDF's and put one QWER in there (near the
bottom). Then test the .find vs the for/each.



Myles wrote:

Dave,

Can I then infer that whereas the code has to touch every cell in a
Range with the For each...Next code, regardless, it only does so
conditionally when a criterion is met with the Find Method. Sounds
intuitive.

Myles.

--
Myles
------------------------------------------------------------------------
Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
View this thread: http://www.excelforum.com/showthread...hreadid=506572

--

Dave Peterson