LngCnt Implementation

[stacys01]

Hello,


I am very new to macros and need assistance with one I am currently working on. I have a script that copies cells in one column and pastes them in the next column. Also there are some paste special- values in there. What I need to occur is that each time I run the macro I need it to shift over one column.


I believe the easiest way to accomplish this is to use a 'LngCnt' and use 'Name Manager' to create a new name to store the 'LngCnt' Value. Here is how I used it in the past- I would start the code with this:

LngCnt = Replace(ActiveWorkbook.Names("valuestore").Value, "=", "")

then implement 'LngCnt' after each Range Selection:

[COLOR=#333333]startRange.FormulaR1C1 = "=SUMIF('test'!R5C1:R159C1,RC1,'test'!R5C" & 31 + LngCnt & ":R159C" & 31 + LngCnt & ")"
[/COLOR]

This would allow the formula to begin pulling from the 31st column on the 'test' tab. This was followed by

LngCnt = LngCnt + 1

to move the column over by 1 each time the macro is run.


What I am not able to figure out is how to properly adjust my current macro to include the 'LngCnt' function. I would need each Selection to incorporate the 'LngCnt'. Here is where I currently am:

 ActiveWindow.SmallScroll Down:=-51
    Range("L1:N1").Select
    Selection.Cut
    Range("M1").Select
    ActiveSheet.Paste
    Range("K1").Select
    Selection.Copy
    Range("L1").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=27
    Range("N47").Select
    ActiveWindow.SmallScroll Down:=12
    Range("N47:N60").Select
    ActiveWindow.SmallScroll Down:=0
    Range("N47").Select
    ActiveWindow.SmallScroll Down:=21
    Range("N47:N81").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=-21
    Range("L47").Select
    ActiveWindow.SmallScroll Down:=15
    Range("L47:L81").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveWindow.SmallScroll Down:=-18
    Range("M47").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=0
    Range("K47").Select
    ActiveWindow.SmallScroll Down:=21
    Range("K47:K81").Select
    ActiveWindow.SmallScroll Down:=-15
    Application.CutCopyMode = False
    Selection.Copy
    Range("L47").Select
    ActiveSheet.Paste
    Range("K47").Select
    ActiveWindow.SmallScroll Down:=15
    Range("K47:K81").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Another part I need assistance on is to adjust the beginning of the formula so that when run it starts at L1:N1 and then next time I run it starts at M1:O1, etc.

If 'LngCnt' is not the easiest way to accomplish this then I am open to suggestions. This is just something I have used in the past and it worked fine.

I do not need the whole formula done for me, but if someone could show me how to adjust just the first 2 cell selections (L1:N1 & M1) I am sure I can apply it to the rest.

Thanks so much in advance!

 

[stacys01]

What exactly is determining the last column with data? I see the 14 you set, which makes since on the first run, but after saving and closing the workbook, and then running the macro again how does it know that O is now the rightmost column with data?

 

[Snakehips]

What exactly is determining the last column with data? I see the 14 you set, which makes since on the first run, but after saving and closing the workbook, and then running the macro again how does it know that O is now the rightmost column with data?

If you go to your sheet and select a cell that is far right of your data and then you hit the End key and the Left Arrow key it will select the first cell to the left ,in that row, that has data. i.e. the last column from the left, with data.
Similarly, if you go some rows below the data in a column and key End then UpArrow it will find the last cell in the column that holds a value or a formula.

Programatically….
Cells(Row Number, Column Number) is a way to express a single cell as a range.
Columns.Count = Max number of columns in your spreadsheet, irrespective of excel version.
1 = Row Number, in this case row 1.

So Cells(1, Columns.Count) is the farthest right cell in row 1.
.End(xlToLeft) is equivalent to having keyed End Left Arrow, finding the first cell to the left that has content and .Column returns the Column number for that column.

So Cells(1, Columns.Count).End(xlToLeft).Column returns 14 for column N in the first instance and then 15 for 2nd run when O has data etc each time.

The code sets variable 'co' to 14 -14 =0 for the first run and the .Offset(0, co) part of the code says offset the starting ranges e.g. L1:N1 by 0 rows and co columns.
So effectively, no offset (0) the first time, offset 1 to the right second time etc etc.


The similar code for finding the last used Row in say column A is Cells(Rows.Count, 1).End(xlUp).Row orCells(Rows.Count, "A").End(xlUp).Row
Which is starting in the very last row and looking up to find the last row with content

I hope that makes sense?

 

[stacys01]

Thanks for the detailed explanation and the breakout of each part of code. It went a long way in helping me understand.

Thanks again for all your help!