+ Reply to Thread
Results 1 to 11 of 11

If statement - Multiple Sheets

  1. #1
    Forum Contributor
    Join Date
    10-23-2003
    Posts
    141

    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" in Column A) to :
    Column A beginning with Row 10 in the Sheet named Variance in the workbook.

    Any help would be greatly appreciated!

  2. #2
    Dave Peterson
    Guest

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

  3. #3
    Forum Contributor
    Join Date
    11-11-2005
    Posts
    267
    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.

  4. #4
    Dave Peterson
    Guest

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

  5. #5
    Forum Contributor
    Join Date
    11-11-2005
    Posts
    267
    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.

  6. #6
    Forum Contributor
    Join Date
    10-23-2003
    Posts
    141
    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!

  7. #7
    Dave Peterson
    Guest

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

  8. #8
    Dave Peterson
    Guest

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

  9. #9
    Forum Contributor
    Join Date
    10-23-2003
    Posts
    141
    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!

  10. #10
    Dave Peterson
    Guest

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

  11. #11
    Forum Contributor
    Join Date
    10-23-2003
    Posts
    141
    Dave,

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1