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!

 

[Snakehips]

Stacys01,

Welcome to MrExcel.

Before I try and offer a possible solution can you please answer the following.

When you first run your code what is the rightmost column that has data?
Is there always data in row 1 of the rightmost column?
Each subsequent run of the code adds 1 further column of data?

 

[stacys01]

Sorry for the late response. Based off the code above, the first time I run it column O is the rightmost column with data. Yes there will always be data in the rightmost column. And yes each subsequent run of the code adds 1 further column of data. So next time I run it Column P will be the rightmost column w/ data.

 

[Snakehips]

Sorry for my delay. Had a solution and and lot it through not saving.

Just one more question before I offer some code.

You say that first time there is data in column O then each subsequent time one more column e.g. P, Q ….
As is, your published bit of code does nothing to add an extra column of data. Is the extra data added by some other means?

 

[stacys01]

My apologies, you are right, I have updated the code:

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

Now as you can see above I am taking the contents (which are formulas) from cells N47:N81 and pasting them to O47:O81. And moving other contents similarly from previous columns over 1 column. The next time I run this I will be moving the contents from O47:O81 to P47:P81 and all the following selections above will move over by 1 as well.

 

[Snakehips]

Sorry for the late response. Based off the code above, the first time I run it column O is the rightmost column with data. Yes there will always be data in the rightmost column. And yes each subsequent run of the code adds 1 further column of data. So next time I run it Column P will be the rightmost column w/ data.

Sorry to be a pain but I meant before. Initially, NO DATA in O but data in O after first run?

 

[stacys01]

No worries, you're not a pain, I appreciate the assistance.

Yes you are right, initially there is no data in O but after the first run there will be (the contents of Column N)

 

[Snakehips]

Ok.....
Firstly I imagine your reference to LngCnt to be to the variable that someone has used in a previous piece of code that stores a value in a named range.
I could of course be wrong but do not see the need for you to be doing that. Nor do you need to use the simpler means of storing such as putting the value directly into an unused cell within your workbook.
As you are using vba you can with iline of code, establish the last column containing data. So we can use that relative to the starting point, ie data in N, to give us a number with which to offset the coluns.

Also your code contains a lot of scrolls and selects that are the inevitable product of a recorded macro and these can always be removed to provide more efficient code. Likewise the copying and pasting can be expressed more efficiently. BTW Recorded macro is often a great place to start coding even for the experienced user.

Here is rationalised code that should do what you want.
Do test on trial data.
If I have misinterpreted anything then I appologise.

Sub Stacy()
'inhibit screen updating
Application.ScreenUpdating = False
'Determine rightmost column which we know is n col 14 to start with
'and thus the column offset that is required relative to column O  (first time = zero)
co = Cells(1, Columns.Count).End(xlToLeft).Column - 14
    Range("L1:N1").Offset(0, co).Cut Range("M1").Offset(0, co)
    Range("K1").Offset(0, co).Copy Range("L1").Offset(0, co)
    
    Range("N47:N81").Offset(0, co).Copy Range("O47").Offset(0, co)
    Range("N47:N81").Offset(0, co).Copy
    Range("N47:N81").Offset(0, co).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Range("L47:L81").Offset(0, co).Copy Range("M47").Offset(0, co)
    Range("K47:K81").Offset(0, co).Copy Range("L47").Offset(0, co)
    
    Range("K47:K81").Offset(0, co).Copy
    Range("K47:K81").Offset(0, co).Copy
    Range("K47:K81").Offset(0, co).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
 Range("L2").Offset(0, co).Select '????
Application.CutCopyMode = False
End Sub

Hope that helps.

 

[stacys01]

Yes that did the trick! Thank you so much.

Yes, 'LngCnt' was a variable that I used in the past to store my counts for other macros. And yes, my knowledge of coding is VERY limited so I always need to start with a recording and manipulate the coding to try and achieve what I truly need.

I really appreciate you taking the time and effort to help me. I will have to study your code more to gain a better understanding of what is actually going on so that I can hopefully use the technique in future macros with similar goals.

Thanks!

 

[Snakehips]

You are welcome.

Good luck with the coding, it will get easier.