Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Programming / November 2007

Tip: Looking for answers? Try searching our database.

Searching cell values...

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Outatym - 12 Nov 2007 21:31 GMT
I am needing to search column G on the "Master" sheet for certain states
starting at row 3.  If if finds the correct state, say AR, then I need it to
copy that entire row and paste it into another worksheet.  Here is the code I
am working with:

***********************************************************
Sheets("Master").Activate
 
   For i = 3 To Cells(Rows.Count, 7).End(xlUp).Row
       If Cells(i, 7).Text = "AR" Then
           Selection.EntireRow.Copy
           Worksheets("Ed").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If

       If Cells(i, 7).Text = "TN" Then
           Selection.EntireRow.Copy
           Worksheets("Beverly").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If

       If Cells(i, 7).Text = "IL" Then
           Selection.EntireRow.Copy
           Worksheets("Kevin").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "GA" Then
           Selection.EntireRow.Copy
           Worksheets("Chris E").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "SC" Then
           Selection.EntireRow.Copy
           Worksheets("Chris E").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "IN" Then
           Selection.EntireRow.Copy
           Worksheets("David F").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "OH" Then
           Selection.EntireRow.Copy
           Worksheets("David F").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "PA" Then
           Selection.EntireRow.Copy
           Worksheets("David F").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "MI" Then
           Selection.EntireRow.Copy
           Worksheets("Louise").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "MO" Then
           Selection.EntireRow.Copy
           Worksheets("Louise").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "VA" Then
           Selection.EntireRow.Copy
           Worksheets("Louise").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "AL" Then
           Selection.EntireRow.Copy
           Worksheets("Bunny").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "MS" Then
           Selection.EntireRow.Copy
           Worksheets("Bunny").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
       If Cells(i, 7).Text = "FL" Then
           Selection.EntireRow.Copy
           Worksheets("Bunny").Activate
           Rows("3").Select
           Selection.Insert Shift:=xlDown
           Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False
       End If
       
Next

***********************************************************

But for some reason it will only find CA and paste mutiple copies of that
row.  I've apparently gotten something screwed up here....
Reply to this Message
JW - 12 Nov 2007 21:57 GMT
Gove something like this a shot.  There isn't a need to make all of
those activations and selctions.
To add more states to this, simply add a new Case statement before the
Case Else statement and follow the same conventions as the others.
Sub tester()
   Dim shName As String
   With Sheets("Master")
       For i = 3 To .Cells(.Rows.Count, 7).End(xlUp).Row
           With .Cells(i, 7)
               Select Case .Text
                   Case "AR"
                       shName = "Ed"
                   Case "TN"
                       shName = "Beverly"
                   Case "IL"
                       shName = "Kevin"
                   Case "GA"
                       shName = "Chris E"
                   Case "SC"
                       shName = "Chris E"
                   Case "IN"
                       shName = "David F"
                   Case "OH"
                       shName = "David F"
                   Case "PA"
                       shName = "David F"
                   Case "MI"
                       shName = "Louise"
                   Case "MO"
                       shName = "Louise"
                   Case "VA"
                       shName = "Louise"
                   Case "AL"
                       shName = "Bunny"
                   Case "MS"
                       shName = "Bunny"
                   Case "FL"
                       shName = "Bunny"
                   Case Else
                       shName = "NotFound"
               End Select
               If shName <> "NotFound" Then
                   Worksheets(shName).Rows(3) _
                       .Insert Shift:=xlDown
                   .EntireRow.Copy _
                       Worksheets(shName).Cells(3, 1)
               End If
           End With
       Next
   End With
End Sub

> I am needing to search column G on the "Master" sheet for certain states
> starting at row 3.  If if finds the correct state, say AR, then I need it to
[quoted text clipped - 137 lines]
> But for some reason it will only find CA and paste mutiple copies of that
> row.  I've apparently gotten something screwed up here....
Reply to this Message
JW - 12 Nov 2007 22:00 GMT
Actually, now that I think about it, you can combine several of those
Case statements.  Try this.
Sub tester()
   Dim shName As String
   With Sheets("Master")
       For i = 3 To .Cells(.Rows.Count, 7).End(xlUp).Row
           With .Cells(i, 7)
               Select Case .Text
                   Case "AR"
                       shName = "Ed"
                   Case "TN"
                       shName = "Beverly"
                   Case "IL"
                       shName = "Kevin"
                   Case "GA", "SC"
                       shName = "Chris E"
                   Case "IN", "OH", "PA"
                       shName = "David F"
                   Case "MI", "MO", "VA"
                       shName = "Louise"
                   Case "AL", "MS", "Fl"
                       shName = "Bunny"
                   Case Else
                       shName = "NotFound"
               End Select
               If shName <> "NotFound" Then
                   Worksheets(shName).Rows(3) _
                       .Insert Shift:=xlDown
                   .EntireRow.Copy _
                       Worksheets(shName).Cells(3, 1)
               End If
           End With
       Next
   End With
End Sub

> Gove something like this a shot.  There isn't a need to make all of
> those activations and selctions.
[quoted text clipped - 189 lines]
> > But for some reason it will only find CA and paste mutiple copies of that
> > row.  I've apparently gotten something screwed up here....
Reply to this Message
JW - 12 Nov 2007 22:01 GMT
Typo in post above.  Sorry about that.
Sub tester()
   Dim shName As String
   With Sheets("Master")
       For i = 3 To .Cells(.Rows.Count, 7).End(xlUp).Row
           With .Cells(i, 7)
               Select Case .Text
                   Case "AR"
                       shName = "Ed"
                   Case "TN"
                       shName = "Beverly"
                   Case "IL"
                       shName = "Kevin"
                   Case "GA", "SC"
                       shName = "Chris E"
                   Case "IN", "OH", "PA"
                       shName = "David F"
                   Case "MI", "MO", "VA"
                       shName = "Louise"
                   Case "AL", "MS", "FL"
                       shName = "Bunny"
                   Case Else
                       shName = "NotFound"
               End Select
               If shName <> "NotFound" Then
                   Worksheets(shName).Rows(3) _
                       .Insert Shift:=xlDown
                   .EntireRow.Copy _
                       Worksheets(shName).Cells(3, 1)
               End If
           End With
       Next
   End With
End Sub

> Actually, now that I think about it, you can combine several of those
> Case statements.  Try this.
[quoted text clipped - 225 lines]
> > > But for some reason it will only find CA and paste mutiple copies of that
> > > row.  I've apparently gotten something screwed up here....
Reply to this Message
Outatym - 13 Nov 2007 17:25 GMT
It actually works!  But for some reason it isn't picking up AR and putting it
in the "Ed" worksheet...any ideas why?

> Typo in post above.  Sorry about that.
> Sub tester()
[quoted text clipped - 260 lines]
> > > > But for some reason it will only find CA and paste mutiple copies of that
> > > > row.  I've apparently gotten something screwed up here....
Reply to this Message
JW - 13 Nov 2007 21:02 GMT
Nope.  It should be picking it up fine.  Make sure that you cell
containing AR doesn't contain an extra space or something out of the
ordinary like that.  If you still have problems, feel free to send me
the file to the e-mail in my profile and I'll take a look at it.
> It actually works!  But for some reason it isn't picking up AR and putting it
> in the "Ed" worksheet...any ideas why?
[quoted text clipped - 263 lines]
> > > > > But for some reason it will only find CA and paste mutiple copies of that
> > > > > row.  I've apparently gotten something screwed up here....
Reply to this Message
OssieMac - 12 Nov 2007 22:10 GMT
There are better methods of achieving what you are doing but I'll point out
the problems that I can see with your code.

Selection.EntireRow.Copy
Above line will be the row where the active or selected cell is; not related
to
Cells(i, 7). You need to select cells(i,7) first or the following also works:-

Cells(i, 7).EntireRow.Copy

You need to re-activate Master after activating other sheets and before the
next copy. Do this just before Next i.

Sheets("Master").Activate
Next i

I have not tested your code so get back to me if still having problems.

Regards,

OssieMac

> I am needing to search column G on the "Master" sheet for certain states
> starting at row 3.  If if finds the correct state, say AR, then I need it to
[quoted text clipped - 137 lines]
> But for some reason it will only find CA and paste mutiple copies of that
> row.  I've apparently gotten something screwed up here....
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.