×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Copy row data

Copy row data

Copy row data

(OP)
I have a huge excel spreadsheet

Number description code date
1234 Test1 A 12/31/1998
1234 Test2 B 04/13/2020
7890 Test3 C 05/06/1999
4567 Test4 D 01/03/1999
4567 Test5 E 09/03/2020
4567 Test6 F 06/07/2020


What I want is that if the two numbers are same then description, code and date should be copied over to the first number

So it be
1234 Test1 A 12/31/1998 Test2 B 04/13/2020

RE: Copy row data

Hi,

Check Jul 22, when I answered a very similar request.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy row data

(OP)
Skip I tried what you had suggested earlier. The issue is for example 4567 gets repeated 3 times so I have to keep dragging the formula across which is a manual process. Is there a way to sutomate this in such a away that it looks for the number of repeats takes the data and copies it acrosd

RE: Copy row data

If the "description, code and date should be copied over to the first number", so you expect your data to look like this?

Number description code date
1234   Test1        A   12/31/1998   Test2 B 04/13/2020
1234   Test2        B   04/13/2020
7890   Test3        C   05/06/1999
4567   Test4        D   01/03/1999   Test5 E 09/03/2020 Test6 F 06/07/2020
4567   Test5        E   09/03/2020
4567   Test6        F   06/07/2020
 


---- Andy

There is a great need for a sarcasm font.

RE: Copy row data

(OP)
Yes that is correct Andy

RE: Copy row data

Here is a little code that does that:

CODE

Option Explicit

Sub CopyStuff()
Dim intR As Integer
Dim intRToCopyTo As Integer
Dim intNoVal As Integer

intR = 2

Do While Range("A" & intR).Value <> ""
    If Range("A" & intR).Value = Range("A" & intR - 1).Value Then
        If intNoVal <> Range("A" & intR).Value Then
            intRToCopyTo = intR - 1
        End If
        
        intNoVal = Range("A" & intR).Value
                
        Range("B" & intR & ":D" & intR).Copy
        Range("A" & intRToCopyTo).End(xlToRight).Offset(0, 1).Select
        ActiveSheet.Paste
    End If
    
    intR = intR + 1
Loop

End Sub 

I do realize that .Select could/should be changed to something more 'VBA proper', so feel free to comment/improve (Skip? smile )


---- Andy

There is a great need for a sarcasm font.

RE: Copy row data

@Andy,

1) On the topic of proper VBA, yes, I probably would have approached this problem differently, not only with respect to avoiding Select and Activate

2) Fundamentally, this kind of summary is ridiculous. It belongs in the spreadsheet solution category rather than VBA. I can vaguely remember doing such stuff in the 1990s using formulas but I can't remember why, except to think that I discovered much better ways to summarize data or analyze data to get the information I needed without such a ridiculous accross-the-page summarization. Anyhow, my IE buddy and I figured out how do this kind of goofy summarization for whatever reason, long before I learned to use VBA with success. A guy who is a (Programmer) ought to be able to figure out how to do this on a spreadsheet without help, IMNSHO.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy row data

@Skip
2) I agree. I just took it as: "Here is a problem, and I need a VBA solution", and since I had a few minutes, there you go. As far as 'why do you need your data this way?' - I quit asking this question. smile


---- Andy

There is a great need for a sarcasm font.

RE: Copy row data

Well part of my purpose as a contributor to Excel-related questions is to also promote good, sound best practices, which this is not. I didn't want to take the time to interrogate the OP about purpose, so I reluctantly gave them a solution several months ago. Hey, drag the formula over (n*3)-1 columns. What's the big deal for a (Programmer)?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy row data

(OP)
I am getting a mismatch error

IntNoVal = Range (“A” & intR).Value

RE: Copy row data

What do you keep in column A?
Investigate the values of your variables:

CODE

...
Debug.Print "intR is " & intR
Debug.Print "In Column A row " & intR & " I have " & Range (“A” & intR).Value
intNoVal = Range (“A” & intR).Value
... 

What do you get - before the error - in Immediate Window from Debug statements?


---- Andy

There is a great need for a sarcasm font.

RE: Copy row data

(OP)
Getting run time error 13 type mismatch

RE: Copy row data

C'mon gmoorthy (Programmer). You oughta be able to figger this out.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy row data

(OP)
Int no Val = 0

Range a and intR = 45678

This is the first instance in the spreadsheet where numbers are repeated

RE: Copy row data

(OP)
I have zero experience in VB

RE: Copy row data

Quote:

intR = 45678

Dim intR As Long

I'd declare ALL your numeric variables referring to Excel Row as Long as a matter of practice as Integer has a max value of 32767.

Now your Number field/column is another thing. Is Number really a number? Are you gonna do arithmetic on any value in the Number field, or is Number more like a Zip code, Part Number or Invoice Number? These are really Identifiers that yer not gonna add,msubtract, multiply or divide and as such, ought to be TEXT values.

FAQ68-6659: When is a NUMBER not a NUMBER?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy row data

(OP)
I am doing any calculation just have to copy the data

RE: Copy row data

(OP)
Tried changing to text still the same issue

RE: Copy row data

How do you have intR declared?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Copy row data

If it is not a 'top secret', you may attach your file here so we can see your problem.

I did set up intR as Integer and it has a limit of 32767
So, it can never reach the value of intR = 45678, and if you pass its limit, the error would be 'an overflow', not a mismatch error.

---- Andy

There is a great need for a sarcasm font.

RE: Copy row data

(OP)
I changed the int no Val to string and it worked.

If I have more columns what I need to do copy them ?

Thanks a lot Andy and Skip

RE: Copy row data

(OP)
Figured that out :D in the code determines the columns.

RE: Copy row data

I see you wanted to cram something other than a number to intNoVal variable (from column A) which would only accept a number. So, if you tried to assign a text value to it, you got error 13 type mismatch sad

And yes, ":D in the code determines the [last] column" co be copied.


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close