Solved

Excel: Copy Column to another macro

Posted on 2012-03-14
8
154 Views
Last Modified: 2012-03-20
I have a long macro I'm running for a user here at work. I'm wanting to insert in the middle of it a macro, a part that copies the entire column of D over to Q.

Essentially I just want D to = Q.

Problem is with a simple formula such as =D2 leaves zero's if there is nothing there in D that goes on forever.

So I see a macro as a better solution, also because the user can just hit run on the macro and change it all over all at once besides running the macro, then making sure the formulas work.


Just so you an see my already made macro I'll attach it. It's current functions have nothing to do with this one. If I can just insert it in there, that would be great rather than worry about two macros.
MACRO.txt
0
Comment
Question by:Pancake_Effect
  • 4
  • 3
8 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37721322
If it's as simple as that.
Range("D:D").Copy Range("Q:Q")

Open in new window

By the way, you could seriously shorten the Select Case in the code by grouping values.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37721370
Worked great! Thank you so much.

-I know there probably a better way, I've been Frankenstein'ing it with pieces I've been gathering pretty much entirely by the help by the expert exchange community. (Thank god for expert exchange) I have no idea how any of this works, I'm trying to learn however, specially after building this, I've been looking at each piece and trying to learn it.

I'm actually just a network guy, trying to learn this for a user, since I'm the only IT guy up here, it lands on me haha.
0
 
LVL 33

Assisted Solution

by:Norie
Norie earned 500 total points
ID: 37721435
Do you mean a better way to do the copying or the Select Case thing?

If it's the copying the only thing I can think of would be to restrict the no of rows so you only copy the data.

If it's the Select case thing I was thinking of something like this where you group all the old values that 'share' the same new value together.
        Select Case c.Value
            Case "B", "BAP", "BBA", "BBR", "BC", "BCP", "BI", "BNH", "BNP", "BS", _
                 "C", "CB", "CBR", "CC", "CCP", "CCX", "CIC", "CNP", _
                 "DA", "DB", "DBA", "DBD", "DBE", "DBF", "DBG", "DBI", "DBJ", "DBK", _
                 "DBL", "DBM", "DBN", "DBQ", "DBV", "DBW", "DBX"    '...add rest of old values

                c.Value = 3         'New value

            Case "BC1", "E9", "E91", "EB9"             'Old values...
                c.Value = 2     'New value



            Case "HSP"              'Old value
                c.Value = 7         'New value


            Case "M", "M1", "MB", "MB1"                'Old value
                c.Value = 1     'New value

            Case "P", "PBR                'Old value"
                c.Value = 6         'New value

            Case "W", "WB", "WBR"                'Old value
                c.Value = 5     'New value


            Case "X", "XB", "XBB"    'Old value
                c.Value = 4         'New value


            Case Else               'Do nothing
        End Select

Open in new window

I kind of got sore fingers so this isn't finished, hopefully it shows what I mean.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37721568
Yeh I was talking about the Select case thing, (the copy worked great)

I see what your doing, I'll give it a try when I get a moment, thanks for the tip!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 33

Expert Comment

by:Norie
ID: 37721587
Do you have a list of all these old values and their new values?

If you did, or could create/get one, then you could get rid of the Select Case altogether.

What you would use in it's place would be a VLOOKUP which you can use in the code via Application.WorksheetFunction.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37721613
Yeh I was given copy of 6 pages of medical codes along with the numbers associated with each one. What I did was, as you can see,  was I just typed in each one for each column.

Essentially they have a program that spits out the raw data onto excel, but we recently merged with another company and they require it to be in a completely different format. (There's no way to change the initial raw format.) Hence why I have to do all these changes via macros.
0
 
LVL 3

Expert Comment

by:DaFranker
ID: 37721724
As for that giant Select Case block, "better ways" could include matched-index arrays with one having the original values and the other matching target values for each, a collection of structs doing pretty much the same thing (though you could save on memory there by having objects in the collection contain more than one value for the same target), or reading directly from a worksheet or other data source. You'd then loop over stuff or choose by index or whatever suits the types you're using.

Whether those are actually "better", though, depends entirely on the goal and the context. Looping through a collection could be much slower, especially if you end up nesting loop within loop and not having any way to exit once you've found a match. However, they also allow much more flexibility in the matching parameters, up to and possibly including (depending on how you go about it) regular expressions.
0
 
LVL 4

Author Closing Comment

by:Pancake_Effect
ID: 37744450
Thanks for the help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now