If statement - Multiple Sheets

[STEVEB]

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

 

[Dave Peterson]

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

 

[Myles]

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.

 

[Dave Peterson]

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

 

[Myles]

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

 

[STEVEB]

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

 

[Dave Peterson]

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.

 

[Dave Peterson]

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]

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

 

[Dave Peterson]

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]

Dave,

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