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

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.

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

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!

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

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!

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