macro code selecting moving range

K

KDG

I am attempting to apply a macro to a file I download and edit each month.
Each month there are more records (rows) in this file. This being so, a
portion of the macro is attempting to move around some of the data to format
the data consistantly. However, when I have done this it is selecting the
same range each time. I would like it to know that when I go to the bottom of
the column and then down one row and over 3 rows that this is where I wish it
to select my new range to move, no matter where the end.down leads me to.
Here is the portion of the code that I'm attempting to change.
---
Range("K2").Select
Selection.End(xlDown).Select
Range("H2343:J2343").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("I2343:K9184")
Range("K9184").Select
Selection.End(xlUp).Select
Range("I6902:J6902").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("J6902:K9184")

As you see, the RANGE("...").Select areas are the portions I'm needing to be
intelligent enough to know that it's not always that cell that I will be
selecting. The cell will always be one row below the row that I end up on
when I do the Selection.End(xlDown).Select and then either two or three cells
to the left. then they will be moved over one cell. Would this be something
like the "RC[-3]" code? I am doing this through the macro recorder and am
rather a novice into the actual coding of macros.

I hope that this makes sense. Any help would be greatly appreciated!!!
 
K

KDG

OK! Thanks! I think I have this thing almost figured out. Now... the last
thing I need for this thing to do is to delete anything that appears below
the last record (by going to the last cell in column A and selecting all the
rows below that and deleting them). Here's what the code is erroring out on...

ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Rows("1:4705").EntireRow.Select
Selection.ClearContents

I'm assuming that it has something to do with the third line. Any hints???

Thanks you SOOO much for your help and your very speedy reply! It has made
my day!
Simon Lloyd said:
Assuming you are copying from H2 to the last row in column K
Code:
--------------------

Range("H2:K" & Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row).Copy Destination:=Range("I2343")

--------------------


KDG;434485 said:
I am attempting to apply a macro to a file I download and edit each
month.
Each month there are more records (rows) in this file. This being so, a
portion of the macro is attempting to move around some of the data to
format
the data consistantly. However, when I have done this it is selecting
the
same range each time. I would like it to know that when I go to the
bottom of
the column and then down one row and over 3 rows that this is where I
wish it
to select my new range to move, no matter where the end.down leads me
to.
Here is the portion of the code that I'm attempting to change.
--- Code:
--------------------Range("K2").Select
Selection.End(xlDown).Select
Range("H2343:J2343").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("I2343:K9184")
Range("K9184").Select
Selection.End(xlUp).Select
Range("I6902:J6902").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("J6902:K9184")
--------------------As you see, the RANGE("...").Select areas are the portions I'm
needing to be
intelligent enough to know that it's not always that cell that I will
be
selecting. The cell will always be one row below the row that I end
up on
when I do the Selection.End(xlDown).Select and then either two or
three cells
to the left. then they will be moved over one cell. Would this be
something
like the "RC[-3]" code? I am doing this through the macro recorder
and am
rather a novice into the actual coding of macros.

I hope that this makes sense. Any help would be greatly
appreciated!!!
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
Click to expand...
 
K

KDG

I have solved my last question... but now I have another. I had the entire
macro up and running on the file that I was working with. But as I stated, I
run a new download each month that will contain more records each time. I
always open the file into Excel and save it as "LCLI.xls" To test it, I ran
a new download to see if it would work. I get an error 9: subscript out of
range and the debugger looks like this... the second line is highlighted.
What am i not telling it to do????

Cells.Select
ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("LCLI.xls").Sort
.SetRange Columns("A:K")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

KDG said:
OK! Thanks! I think I have this thing almost figured out. Now... the last
thing I need for this thing to do is to delete anything that appears below
the last record (by going to the last cell in column A and selecting all the
rows below that and deleting them). Here's what the code is erroring out on...

ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Rows("1:4705").EntireRow.Select
Selection.ClearContents

I'm assuming that it has something to do with the third line. Any hints???

Thanks you SOOO much for your help and your very speedy reply! It has made
my day!
Simon Lloyd said:
Assuming you are copying from H2 to the last row in column K
Code:
--------------------

Range("H2:K" & Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Row).Copy Destination:=Range("I2343")

--------------------


KDG;434485 said:
I am attempting to apply a macro to a file I download and edit each
month.
Each month there are more records (rows) in this file. This being so, a
portion of the macro is attempting to move around some of the data to
format
the data consistantly. However, when I have done this it is selecting
the
same range each time. I would like it to know that when I go to the
bottom of
the column and then down one row and over 3 rows that this is where I
wish it
to select my new range to move, no matter where the end.down leads me
to.
Here is the portion of the code that I'm attempting to change.
---
Code:
--------------------

Range("K2").Select
Selection.End(xlDown).Select
Range("H2343:J2343").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("I2343:K9184")
Range("K9184").Select
Selection.End(xlUp).Select
Range("I6902:J6902").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("J6902:K9184")
--------------------

As you see, the RANGE("...").Select areas are the portions I'm
needing to be
intelligent enough to know that it's not always that cell that I will
be
selecting. The cell will always be one row below the row that I end
up on
when I do the Selection.End(xlDown).Select and then either two or
three cells
to the left. then they will be moved over one cell. Would this be
something
like the "RC[-3]" code? I am doing this through the macro recorder
and am
rather a novice into the actual coding of macros.

I hope that this makes sense. Any help would be greatly
appreciated!!!
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
Click to expand...
Click to expand...
 
K

KDG

ok... that helped figure out what was going on there... thanks bunches.
Now... two more things. Seems you solve one thing and something else pops up!

1) In order for it to see the correct sheet name I'll have to manually
change the sheet name to the name in the code. Right? or have the code
recognize the current sheet name no matter what it is? Could this be coded
in? I'm assuming so since I know that nothing is impossible, just beyond my
experience.

2) Now... when I get to the part of the code that is selecting a range
(which is different each time) and moving it to the right one space I get
error because it's saying that the selected range and the destination size
do not match. I understand what it's saying, but I don't know how to tell it
to accept what I'm wanting it to do.

here's the code that is the problem...
ActiveCell.Offset(1, -3).Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=ActiveCell.Offset(0, 1).Range("A1:C6842")
<--this line

and the error code is...
Run Time error 1004
The information cannot be pasted because the cut area and the paste area are
not the same size and shape. Try one of these: click a single cell and then
past or select a rectangle that's the same size and shape, and then paste.

?????????????? Thanks so much for your help. I'm learning TONS!!!!


Simon Lloyd said:
The error 9 is usually because excel cannot find the sheet you are
asking it to look for try this and see what sheet names it gives you, it
will give you the activeworkbook name and the activesheet name to prove
what it is you are working with.
Code:
--------------------

msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.name
Cells.Select
ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFields.Clear
msgbox "Active workbook is " & ActiveWorkbook.Name & vblf & "Active sheet is " & Activesheet.name
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("LCLI.xls").Sort
.SetRange Columns("A:K")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
--------------------
KDG;435068 said:
I have solved my last question... but now I have another. I had the
entire
macro up and running on the file that I was working with. But as I
stated, I
run a new download each month that will contain more records each time.
I
always open the file into Excel and save it as "LCLI.xls" To test it, I
ran
a new download to see if it would work. I get an error 9: subscript out
of
range and the debugger looks like this... the second line is
highlighted.
What am i not telling it to do????
Code:
--------------------Cells.Select
ActiveWorkbook.Worksheets("LCLI.XLS").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"K2:K48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"J2:J48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"I2:I48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("LCLI.xls").Sort.SortFields.Add Key:=Range( _
"H2:H48576"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("LCLI.xls").Sort
.SetRange Columns("A:K")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
--------------------
KDG said:
OK! Thanks! I think I have this thing almost figured out. Now... the last
thing I need for this thing to do is to delete anything that appears below
the last record (by going to the last cell in column A and selecting all the
rows below that and deleting them). Here's what the code is erroring out on...

ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Rows("1:4705").EntireRow.Select
Selection.ClearContents

I'm assuming that it has something to do with the third line. Any hints???

Thanks you SOOO much for your help and your very speedy reply! It has made
my day!
:


Assuming you are copying from H2 to the last row in column K
Code:
Click to expand...
0).Row).Copy Destination:=Range("I2343")
--------------------


KDG;434485 Wrote:
I am attempting to apply a macro to a file I download and edit each
month.
Each month there are more records (rows) in this file. This being so, a
portion of the macro is attempting to move around some of the data to
format
the data consistantly. However, when I have done this it is selecting
the
same range each time. I would like it to know that when I go to the
bottom of
the column and then down one row and over 3 rows that this is where I
wish it
to select my new range to move, no matter where the end.down leads me
to.
Here is the portion of the code that I'm attempting to change.
---

Code:
--------------------

Range("K2").Select
Selection.End(xlDown).Select
Range("H2343:J2343").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("I2343:K9184")
Range("K9184").Select
Selection.End(xlUp).Select
Range("I6902:J6902").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("J6902:K9184")

--------------------

As you see, the RANGE("...").Select areas are the portions I'm
needing to be
intelligent enough to know that it's not always that cell that I will
be
selecting. The cell will always be one row below the row that I end
up on
when I do the Selection.End(xlDown).Select and then either two or
three cells
to the left. then they will be moved over one cell. Would this be
something
like the "RC[-3]" code? I am doing this through the macro recorder
and am
rather a novice into the actual coding of macros.

I hope that this makes sense. Any help would be greatly
appreciated!!!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' ('The Code Cage - Microsoft Office Help -
Click to expand...
Microsoft Office Discussion' (http://www.thecodecage.com))Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'macro code selecting moving range - The Code
Click to expand...
Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=120622)
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

File Name changes with dates and Deleting Rows of Zeros 0
Macro to fill to next field with data 4
variable number of rows. 2
Excel Macro to use cells as a autofilter 0
Applying Variables to SORT 4
Paste failing after cut 7
macro question - selecting the right column 3
"Run-time error '1004' 5

Top