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 / September 2007

Tip: Looking for answers? Try searching our database.

Copy and Past Macro

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
J.W. Aldridge - 21 Sep 2007 19:53 GMT
I have a formula that looks for matching criteria (in cell I1 vs.
Column A) and copies the values to the right of it and pastes them in
E (starting with E1).

What I need to do is the same except the last part (starting with E1).
I need to copy, but a lateral move (like shown below).

If the matching value is found on row 2, then copy and paste the
target cells value directly over on the same row.

A      B       C      D       E       F       G       H       I
9/3   ABC                                                        9/4
9/4   DEF                    DEF
9/3   GHI
9/4   JKL                     JKL

The formula that I am working with is as follows:

Sub macro3()
   Dim ws As Worksheet
   Dim iA As Integer
   Dim iB As Integer
   Dim c As Range
   Dim rng As Range

   Set ws = Worksheets("Sheet1")
   Set rng = ws.Range("A1:A65536")
   For Each c In rng
       If c = Range("I1") Then
           iA = iA + 1
           ws.Cells(iA, 5) = c.Offset(0, 1)
       End If
   Next c
End Sub
Bob Phillips - 21 Sep 2007 20:04 GMT
Sub macro3()
   Dim c As Range
   Dim rng As Range
   Dim ws As Worksheet

   Set ws = Worksheets("Sheet1")
   Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
   For Each c In rng
       If c.Value = Range("I1").Value Then
           c.Offset(0, 4).Value = c.Offset(0, 1).Value
       End If
   Next c
End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> I have a formula that looks for matching criteria (in cell I1 vs.
> Column A) and copies the values to the right of it and pastes them in
[quoted text clipped - 30 lines]
>    Next c
> End Sub
J.W. Aldridge - 21 Sep 2007 21:43 GMT
When I get my first million....... I'm buying you a Krystal Burger
(with Cheese!)

THANX!!!!!!!!!
Don Guillett - 21 Sep 2007 22:18 GMT
Big spender

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>
> When I get my first million....... I'm buying you a Krystal Burger
> (with Cheese!)
>
> THANX!!!!!!!!!
 
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.