Variable ranges

[BaldyMcFatFat]

I have an issue where I need a range to have a variable figure in one of the positions. This variable is set when running the macro by an input box. I am trying to learn all this VBA stuff with no actual programming background and I have been able to do most of it with the help of websites such as this and a couple books; however, I have been unsuccessful (so far) to find an answer to this issue. the codding is written below:

ActiveCell.Offset(-Y, 1).Range("A1:A14").Select
Selection.Cut
ActiveCell.Offset(0, 1).Range("A1:A14").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1:C14").Select
Application.CutCopyMode = False

This is just a small section of the macro I am working with.
The -Y in the first line is one of the areas I have been able to work with the input box variable, but the one hold up I am finding, is how to get the range selected to also be determined by the input box value. All I need is the "A14" and "C14" to be able to change based on the value placed in the Input Box at the beginning of the macro.
I am working on Excel 03 and Windows XP if that maes any difference. Any help would be greatly appreciated. And please excuse any mistakes or stupid errors I may have made in my logic here. As always, let me know if there are any questions to help me clarify my ranting.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[SkipVought]

Check out the InputBox Method in HELP. One argument, 8, specifies "A cell reference, as a Range object".

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BaldyMcFatFat]

Will I have to make the input box for the range seperate from other items at that point? I ask this cause the existing input box provides the variable for other functions within the code. I mainly want to know if I need to insert another input box into my macro.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[SkipVought]

Please post the code that illustrates how you use the InputBox this way.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BaldyMcFatFat]

I will go ahead and insert the entire code up to the point of where I need help. And once again, I do apologize for my faults in this area. I am basically looking for it to select a range that goes down a certain number of rows from wherever it happens to be at that point in the macro (if that clarifies anything).

Sub TestAllTBQSetUpAgent()
Dim X As Variant
X = CInt(InputBox("How many agents or groups are there?"))
Dim Y As Variant
Y = CInt(InputBox("How many Items did you select from the form?"))
For Count = 1 To X
ActiveSheet.Shapes("Picture 1:150").Select
Selection.Delete
Next Count
Range("C8").Select
For Count = 1 To X
ActiveCell.Offset((Y * 2 + 9), 0).Rows("1:25").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, 2).Range("A1").Select
Next Count
Range("C8").Select
For Count = 1 To X
ActiveCell.Offset(1, 0).Rows("1:6").EntireRow.Select
Selection.Delete Shift:=xlUp
For Item = 1 To (Y - 1)
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
Next Item
ActiveCell.Offset(1, 0).Rows("1:3").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(-Y, 1).Range("A1:A14").Select
Selection.Cut
ActiveCell.Offset(0, 1).Range("A1:A14").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1:C14").Select
Application.CutCopyMode = False


Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[SkipVought]

This is extremely hard to follow, as I have NO IDEA where the ActiveCell is. Using ActiveCell is not a good idea!

I have not idea what the For Count = 1 to X does in the first two loops. You never change the Shape Name. Are there shapes left on the sheet when the first loop finishes?

And the second loop is just a puzzling.

Your input box is not specifying a RANGE. It is specifying LIMITS or counts.

Perhaps you ought to explain the purpose of this exersize, rather than trying to decipher your logic.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BaldyMcFatFat]

The basic purpose of this whole thing is to sort through data based on one column's data and move things around within the report. I was basically seeking to find out if it was possible to have a statement like ("A1:A14") have a variable in it.

For Count = 1 to X is just telling the macro to perform the loop X number of times with X being a value defined by the input box.

I was only using ActiveCell because I was manipulating what was already in the macro when I created it. I used the record macro feature and then went in and changed it around. ActiveCell was the item given when referring to a starting point in a Relative Reference. I do this because I am trying to have it move a certain number of cells down or up or left or right based on where ever it may be at that time.

In regards to the input boxes not specifying a range, I have it so the person running the macro only needs to input a number based on however many times the function needs to be performed.

Once again, I deeply apologize for all of this being confusing. I am probably going about the entire coding wrong because I have had no teaching in it and all I am going off of is the little stuff I have been able to poke through and experiment on my own. And the little I am able to understand out of the book I have.

Baldy McFatfat

"If at first you don't succeed, skydiving is not for you.

 

[SkipVought]

Using Cut, Delete, Insert in a loop may give you unintended results. There are often better ways of achieving similar results.

"I was basically seeking to find out if it was possible to have a statement like ("A1:A14") have a variable in it."

dim lStartRow as long, lEndRow as long
lStartRow = 1
lEndRow = 14
Range(Cells(lStartRow, "A"), Cells(lEndRow, 1)).Select

is equivalent to

Range("A1:A14").Select

I still don't get the picture. Please post a sample of DATA from your sheet, like a before and after. Please state where the ActiveCell is at the beginning.




Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]