Select/process Variable Range with Macro

**longfisher** · 12-15-2006, 04:44 PM

This is my first post to this forum. And, I'm pretty new to Excel macros. I have no experience whatsoever with VBA (and I'm not sure I need to).

Here's my problem.

I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.

Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.

Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.

I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.

Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.

Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.

Thanks for any help you can provide.

Longfisher

**mudraker** · 12-15-2006, 05:11 PM

When you record a macro as you have found out it records the exact range selected

You will need to post your code so that we can modify the code to allow for a variable number of rows

You will need to explain what part of the code needs modifing
What column you are using the Shift/Control End and Shift/control arrow commands on.

**longfisher** · 12-15-2006, 06:12 PM

Here's the existing code:

Record3 (a)
=SELECT("R2C1:R231C23")
=SORT(1,"R2C3",2)
=SELECT("R2C3")
=SELECT.END(4)
=SELECT("R2C2:R16C2","R16C2")
=COPY()
=SELECT("R2C4")
=PASTE()
=SELECT("R2C3:R16C3")
=COPY()
=SELECT("R2C2")
=PASTE()
=SELECT("R2C3:R16C3")
=CLEAR(1)
=RETURN()

Here's the explanation, and, I'm sorry for how long this got to be.

The basic idea is that Column C3 should not contain any data and if it does the record contains a transposition error; data belonging in C4 ended up in C2 and in the process displaced data from C2 into C3. That's what needs to be fixed.

To correct the dataset the data needs to be sorted to bring the errant records to the top of the worksheet immediately under the title row and then the correction processed. So, first there's a sort on Column C3 in decending order.

Please remember, each dataset will be of a different number of rows (pertinent to the selection which precedes the sort) and each dataset will contain a different number of errant records which require correction.

Record3 (a)
=SELECT("R2C1:R231C23")

This selects the data itself leaving out the column headers. It was done manually with the Shift/Ctrl - End keyboard command.

=SORT(1,"R2C3",2)

This sorts the data in decending order on column C3.

=SELECT("R2C3")
=SELECT.END(4)

I think these two commands select the errant data in column c3. I used this simply to measure the number of data records (rows) which contain errant data. Then I arrow left to Column C2 and select the data from that column in preparation for a copy/past to Column C4.

=SELECT("R2C2:R16C2","R16C2")
=COPY()
=SELECT("R2C4")
=PASTE()

That was the copy/paste of the data in Column C2 to equivalent rows in C4. Then I arrow over left and select the data in Column C3 which needs to go into Column C2.

=SELECT("R2C3:R16C3")
=COPY()
=SELECT("R2C2")
=PASTE()

That was the copy past into Column C2.

Then I simply clean up a bit by clearing the data from Column C3.

=SELECT("R2C3:R16C3")
=CLEAR(1)
=RETURN()

I hope that's not to opaque to understand.

LongFisher

**MSP77079** · 12-15-2006, 09:33 PM

Hey Fisher,

What version of Excel are you using? What you posted looks a lot like Excel4 macro stuff. Which will still work with Excel2003, but there are not many people around who remember that stuff.

If you are using Excel versions since '97, you should be using the Macro Recorder to create VBA. In which case, the Ctrl+End and Ctrl+Arrow stuff does record true to life.

**longfisher** · 12-15-2006, 09:50 PM

Is it time to upgrade?

LongFisher

**MSP77079** · 12-15-2006, 11:02 PM

Ha! Best laugh I've had all day.

My friend, if I still had my Excel 97 disks (and, they were disks!), I would gladly give them to you. But I have not seen them this century.

My memory of Excel 4 macros is just too rusty to be of any help. I'm sorry.

**longfisher** · 12-16-2006, 01:07 PM

Thanks for laughing at me.

LongFisher

**longfisher** · 12-20-2006, 07:55 PM

OK, so I was using an old versions of Excel. Now I have upgraded to Excel 5.0 which uses VB. Can anyone help me now?

Here's the code from the macro recorder. I hope it's sufficient.

'
' Macro11 Macro
' Macro recorded 12/20/2006 by Longfisher
'
'
Sub Macro11()
ActiveCell.Offset(-10, -4).Range("A1").Select
Selection.Sort Key1:=ActiveCell.Offset(0, 2).Range("A1"), Order1:= _
xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(-105, -1).Range("A1:A106").Select
ActiveCell.Offset(0, -1).Range("A1").Activate
Selection.Copy
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -1).Range("A1:A106").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1:A106").Select
Application.CutCopyMode = False
Selection.Clear
ActiveCell.Offset(0, -2).Range("A1").Select
End Sub

Thanks for any help anyone is willing to provide.

God bless you,

Longfisher

**MSP77079** · 12-20-2006, 09:22 PM

As penance for laughing (not at you, though, my friend), I promise to work on this until we get it done exactly the way you want it.

I just copied the code to my Excel. I'll play with it and get back soon.

**MSP77079** · 12-20-2006, 09:45 PM

You should be able to "step" through this one line of code at a time by pressing F8. (Actually, I cannot remember back to 1995. So, maybe you can and maybe you can't. Try it.)

Let me know what you think.

Please Login or Register  to view this content.

Select/process Variable Range with Macro

LinkBack

Thread Tools

Rate This Thread

Display

Select/process Variable Range with Macro

Code

Yep, It's Excel 4.0

I'm just poor and new to this country.

Upgraded to VB capable version

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions