If statement - Multiple Sheets
 

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

If statement - Multiple Sheets
 

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

If statement - Multiple Sheets
 

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

If statement - Multiple Sheets
 

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

If statement - Multiple Sheets
 

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

If statement - Multiple Sheets
 

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

If statement - Multiple Sheets
 

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

If statement - Multiple Sheets
 

This portion did the work:

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

So...

DestCell.Value = FoundCell.Offset(0, 2).Value
Destcell.offset(0,1).value _
= foundcell.parent.range("A2").value
Destcell.offset(0,2).value _
= foundcell.parent.range("A3").value
Set DestCell = DestCell.Offset(1, 0)
Set FoundCell = .FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If


(I didn't test it...)

STEVEB wrote:

Dave,

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

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

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

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

For Example:

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

Thanks again for your help, I really appreciate it!

--
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

If statement - Multiple Sheets
 

Thanks Dave,

I tested the code & everything went smoothly!!! I really appreciat
your help!

One last question, is it possible to update the first code you gave m
for multiple cells:

For Example

For Variance in column A - Instead of copying the value of the cell i
column C is it possible to copy the values in columns C through G an
past accordingly on the varaince tab.

Thanks again

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

If statement - Multiple Sheets
 

And does it paste C:G in A:E and move that other stuff over a column or two?

DestCell.resize(1,5).Value _
= FoundCell.Offset(0, 2).resize(1,5).Value

Destcell.offset(0,5).value _
= foundcell.parent.range("A2").value
Destcell.offset(0,6).value _
= foundcell.parent.range("A3").value
Set DestCell = DestCell.Offset(1, 0)
Set FoundCell = .FindNext(after:=FoundCell)
If FoundCell.Address = FirstAddress Then
Exit Do
End If


Still untested!


STEVEB wrote:

Thanks Dave,

I tested the code & everything went smoothly!!! I really appreciate
your help!

One last question, is it possible to update the first code you gave me
for multiple cells:

For Example

For Variance in column A - Instead of copying the value of the cell in
column C is it possible to copy the values in columns C through G and
past accordingly on the varaince tab.

Thanks again!

--
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

If statement - Multiple Sheets
 

Dave,

Thanks so much for all your help on this, I was able to get everythin
working! I really appreciate your help!

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