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.

Find variable in Col B, Copy Paste Values in Cn:Tn to Row below

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Chris - 28 Nov 2007 17:46 GMT
I'm looking for help with a macro that will allow me to find every row in
column B where the value is "FC" and will copy the range Bn:Tn to the row
below.

For example:

if cell B4 = "FC" then copy C4:T4 and paste values to C5:T5

repeat for all rows where Column Bn = "FC"

Any help would be appreciated.
sebastienm - 28 Nov 2007 18:01 GMT
Signature

Regards,
Sébastien
<http://www.ondemandanalysis.com>

> I'm looking for help with a macro that will allow me to find every row in
> column B where the value is "FC" and will copy the range Bn:Tn to the row
[quoted text clipped - 7 lines]
>
> Any help would be appreciated.
Chris - 28 Nov 2007 18:07 GMT
Did you intend to include a suggestion?

> > I'm looking for help with a macro that will allow me to find every row in
> > column B where the value is "FC" and will copy the range Bn:Tn to the row
[quoted text clipped - 7 lines]
> >
> > Any help would be appreciated.
sebastienm - 28 Nov 2007 18:06 GMT
Hi,
Try the following code.

''' -------------------------------
Sub test()
Dim c As Range
Dim result As Range
Dim firstAddress As String

''' find all FC cells
With ActiveSheet.Range("B:B")   ''' <<<<< Range to search
   Set c = .Find("FC", LookIn:=xlValues, lookat:=xlWhole) ''' <<<<< what to
search for
   
   If Not c Is Nothing Then
       firstAddress = c.Address
       Do
           If result Is Nothing Then
              Set result = c
           Else
              Set result = Application.Union(result, c)
           End If
           
           Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstAddress
   End If
End With

''' copy/paste
If Not result Is Nothing Then ''' if some cells were found
  For Each c In result.Cells
     Set c = Application.Intersect(c.EntireRow _
          , c.Parent.Range("C:T"))
     c.Copy c.Offset(1, 0)
  Next
End If

End Sub
''' -------------------------------

Potential issue: in the copy/paste section, if 2 FC cells follow each other
then copy/pasting the 1st one will overwrite the second FC row data therefore
the second copy/paste will carry over on the 3rd row the data of the 1st FC
row. Now, it is possible that this cannot happen in your situation, say for
example that your sheet is organized in such a way that no 2 FC can follow
each other. If this can happen however, you'll need to rewrite the Copy/Paste
section to start copying/pasting the last row and going up.

Signature

Regards,
Sébastien
<http://www.ondemandanalysis.com>

> I'm looking for help with a macro that will allow me to find every row in
> column B where the value is "FC" and will copy the range Bn:Tn to the row
[quoted text clipped - 7 lines]
>
> Any help would be appreciated.
Chris - 28 Nov 2007 18:16 GMT
Thanks, that works well, the only issue I need to address is the formatting.
I would like to only paste the values.

Thanks again.

> Hi,
> Try the following code.
[quoted text clipped - 55 lines]
> >
> > Any help would be appreciated.
sebastienm - 28 Nov 2007 18:26 GMT
For values only, instead of
  c.Copy c.Offset(1, 0)
try something like:
  c.Copy
  c.Offset(1, 0).PasteSpecial xlPasteValues

Signature

Regards,
Sébastien
<http://www.ondemandanalysis.com>

> Thanks, that works well, the only issue I need to address is the formatting.
> I would like to only paste the values.
[quoted text clipped - 60 lines]
> > >
> > > Any help would be appreciated.
 
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.