Help save me from excel hell!

September 2, 2009 4:52 PM

Another crazy excel question- I can't promise this won't be my last one.

I am finally to the point where I am recording macros and have found that the shorter the macro, the better.

The issue I am having is 1 of 2 things. Either there are a bunch of zeroes being added at the bottom of the chunk of data I am working with, or my formula isn't transferring through a full column.

Here is my workflow:

I have 2 worksheets that I am combing together into 1 before the start of my macro. There are roughly 30 to 35 columns in the combined worksheet, we'll call it "working." There are thousands of rows of data for every workbook I work with, all with a different number of rows, but always the same columns and number of columns (this may be where the issue is).

My first step is to copy one column to the front of the workbook by using shift+ctrl+down and then "insert copied cells" into column A, shift to the right.

My next step is to insert a new, blank column next to column A and add the formula =text(a2,"0000000") and fill handle down so the formula only goes down to the bottom of the data set.

I then cut and paste special "values" into column A so that the numbers in column A are all 7 digits instead of 2 to 7 digits long.

Obviously, this works on the worksheet I have recorded it on, but used in any other workbook returns either 1 of 2 responses, either there are a bunch of 0's below the data set in column A or the cut and paste special- values doesn't make it over to column A.

I know this is pretty long and may be a little convoluted, but if you have any suggestions on either how to fix this, or someone who can do this professionally (yes, I'm just about willing to pay someone to help me with this as it is driving me nuts right now), I would be greatly appreciative!

posted by TheBones to computers & internet (8 comments total)

Excel has an option to step through recorded macros, one function at a time, and see the results. I suggest that you troubleshoot your macro in this fashion, and the problem will likely become quickly apparent.

Alternatively, if you are sick of dealing with it, PM me and we can work something out. I occasionally do some Excel freelancing on the side.
posted by suburbanrobot at 5:39 PM on September 2

One more point- I am not good at all at vba, I can barely read it, and am much worse at writing it.
posted by TheBones at 5:42 PM on September 2

You shouldn't really need to rewrite any VBA. Just step through the function and figure out where things are going awry. Once you know where the problem is occurring, usually it isn't too difficult to figure out why*.

* Since we are talking about Excel, this isn't a tried and true rule. Every now and then, you will run into something completely illogical an inordinate amount of time figuring out a workaround.
posted by suburbanrobot at 5:46 PM on September 2

I wrote the following function for you. It takes the first column of a worksheet, changes it to values, and then applies a format to the numbers.

Sub format_column(target As Worksheet, format As String)
    target.UsedRange.Columns(1).Value = target.UsedRange.Columns(1).Value
    target.UsedRange.Columns(1).NumberFormat = format
End Sub

You can invoke it like:

call format_column Sheet1, "0000000"

Note that you have to give the function the sheet's codename, which you can see on the lefthand side of the VBA window.

If you want, you can even have it do all the columns in a worksheets

Sub format_all(target As Worksheet, format As String)
    target.UsedRange.Value = target.UsedRange.Value
    target.UsedRange.NumberFormat = format
End Sub

Hope this helps!
posted by Mons Veneris at 5:49 PM on September 2

Try copying the values from Column B (calculation) into column C and then delete columns A (raw data) and B.
posted by ifandonlyif at 6:16 PM on September 2

Your first problem "there are a bunch of 0's below the data set in column A" sounds like it will occur if you recorded the macro on a longer data set, then used it on a shorter one.
When you use the fill handle to drag a formula down, the macro records the specific length that was filled down that one time, not that it matched the adjacent column.

So if you record the macro on a longer data set, then use it on a shorter one, you'll get 000000s added until it is the same length as the original used to record the macro.

To fix, without editing the VBA, try re-recording with this workflow:

After you have inserted the new column,
1) select cell A1 (the top of your pasted data)
2) press ctrl+down
3) press right arrow
4) type your formula
5) press ctrl+shift+up
6) select Edit...Fill...Up from the edit menu. (presuming Excel 2003).
posted by rube goldberg at 8:12 PM on September 2

I would also guess that your second problem "the cut and paste special- values doesn't make it over to column A" is probably occurring because you are selecting a different column to move over then when you recorded the macro.

If you record the macro by selecting column F, copying, then inserting it in front of A, the recorded VBA will be written to insert the copied data 5 columns to the right. If you then use the macro on column M, it will be inserted 5 columns over, thus not making it all the way to A.

Try this workflow:

1) Before recording the macro: start with the top cell of the column you want to copy selected.
2) Start recording, using relative references.
3) Click on the column heading to select the whole column.
4) Copy (or cut) the data
5) press ctrl+left --- this should select cell A1
6) Edit...Insert Copied Cells
posted by rube goldberg at 8:24 PM on September 2

Rube goldberg- thanks for the suggestion, I tried and still got the same results unfortunately
posted by TheBones at 8:35 AM on September 3