macro to move data from one cell to the adjacent,then to the third , then to the fourth

To run the program has made use of Range A1 initialized with a zero.
I couldn't see that day 4 required any action -
so with a zero in A1 for day 1 run - a 1 for day 2 run etc.
Try this:

Sub Sumesh(): Dim d As Long, c As Long, r As Long
r = Range("B" & Rows.count).End(xlUp).Row
d = Range("A1") + 1: Range("A1") = d
If d = 4 Then Exit Sub
If d = 5 Then
For c = 3 To 23 Step 10
Cells(2, c).Resize(r - 1, 4).ClearContents
Range("A1") = 0: Next c
Else
For c = 3 To 23 Step 10
Cells(2, c + 1).Resize(r - 1, 3).Value = _
Cells(2, c).Resize(r - 1, 3).Value
Cells(2, c).Resize(r - 1, 1).Value = "": Next c
End If
End Sub

I guess you need the program to install the formulae - I'll see what I can do.

Try this:

Sub Sumesh(): Dim d As Long, c As Long, r As Long
Dim F1 As String: F1 = "=IF(RC[-3]>RC[-4],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-6]-RC[-7])"
Dim F3 As String: F3 = "=AVERAGE(RC[-4]:RC[-1])"
Dim F4 As String: F4 = "=IF(RC[-4]>RC[-5],""Y"",""N"")"
Dim F5 As String: F5 = "=SUM(RC[-7]-RC[-8])"
r = Range("B" & Rows.count).End(xlUp).Row - 1
d = Range("A1") + 1: Range("A1") = d
If d = 4 Then
Cells(2, 7).Resize(r, 3).Formula = F1: Cells(2, 10).Resize(r, 3).Formula = F2
Cells(2, 17).Resize(r, 3).Formula = F1: Cells(2, 20).Resize(r, 3).Formula = F2
                Cells(2, 27).Resize(r, 1).Formula = F3
Cells(2, 28).Resize(r, 3).Formula = F4: Cells(2, 31).Resize(r, 3).Formula = F5
Exit Sub: End If
If d = 5 Then
For c = 3 To 23 Step 10
Cells(2, c).Resize(r, 11).ClearContents
Range("A1") = 0: Next c
Else
For c = 3 To 23 Step 10
Cells(2, c + 1).Resize(r, 3).Value = _
Cells(2, c).Resize(r, 3).Value
Cells(2, c).Resize(r, 1).Value = "": Next c
End If
End Sub

Hi Sumesh,

Remember that a1 must contain the day number of the last run - this version alters slightly the clearing:

Sub Sumesh(): Dim d As Long, c As Long, r As Long
Dim F1 As String: F1 = "=IF(RC[-3]>RC[-4],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-6]-RC[-7])"
Dim F3 As String: F3 = "=AVERAGE(RC[-4]:RC[-1])"
Dim F4 As String: F4 = "=IF(RC[-4]>RC[-5],""Y"",""N"")"
Dim F5 As String: F5 = "=SUM(RC[-7]-RC[-8])"
r = Range("B" & Rows.count).End(xlUp).Row - 1
d = Range("A1") + 1: Range("A1") = d
If d = 4 Then
Cells(2, 7).Resize(r, 3).Formula = F1: Cells(2, 10).Resize(r, 3).Formula = F2
Cells(2, 17).Resize(r, 3).Formula = F1: Cells(2, 20).Resize(r, 3).Formula = F2
                Cells(2, 27).Resize(r, 1).Formula = F3
Cells(2, 28).Resize(r, 3).Formula = F4: Cells(2, 31).Resize(r, 3).Formula = F5
Exit Sub: End If
If d > 4 Then
For c = 3 To 23 Step 10
Cells(2, 3).Resize(r, 31).ClearContents
Range("A1") = 0: Next c
Else
For c = 3 To 23 Step 10
Cells(2, c + 1).Resize(r, 3).Value = _
Cells(2, c).Resize(r, 3).Value
Cells(2, c).Resize(r, 1).Value = "": Next c
End If
End Sub

thanks for the help.
"Remember that a1 must contain the day number of the last run - this version alters slightly the clearing":

i am not able to follow the above instuction.
can you pls apply the macro to my sheet and show me how does it work?

since you have used more than one macro , is it possible to rename it with sumesh1(). sumesh2() ?

Sub bb()
Dim r(1 To 3) As Range
Set r(1) = Range("G2:I11,Q2:S11"): Set r(2) = Range("J2:L11,T2:V11,AE2:AG11"): Set r(3) = Range("AB2:AD11")
[a1].Value = [a1].Value + 1
If [a1].Value = 4 Then
    r(1).Formula = "=IF(D2>C2,""Y"",""N"")": r(2).Formula = "=SUM(D2-C2)": r(3).Formula = "=IF(X2>W2,""Y"",""N"")"
ElseIf [a1].Value > 4 Then
     r(1).ClearContents: r(2).ClearContents: r(3).ClearContents
     [a1].Value = 0
End If
End Sub

Hi Sumesh,

If you start with day1 the macro will take care of the day assignment in A1. You can name the macros any thing acceptable for a macro name.

Here's another version for your larger file:

Sub SumeshIV(): Dim d As Long, C As Long, r As Long
Dim F1 As String: F1 = "=IF(RC[-8]>RC[-9],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-19]-RC[-20])"
Dim F3 As String: F3 = "=AVERAGE(RC[-10]:RC[-1])"

r = Range("B" & Rows.count).End(xlUp).Row - 1
d = Range("A1") + 1: Range("A1") = d
If d = 9 Then
                    For C = 14 To 72 Step 29
            Cells(2, C).Resize(r, 9).Formula = F1
            Cells(2, C + 9).Resize(r, 9).Formula = F2
            Cells(2, C - 1).Resize(r, 1).Formula = F3
                    Next C
Exit Sub: End If
            If d > 9 Then
Cells(2, 3).Resize(r, 87).ClearContents
Range("A1") = 0
            Else
        For C = 3 To 61 Step 29
    Cells(2, C + 1).Resize(r, 9).Value = _
    Cells(2, C).Resize(r, 9).Value
    Cells(2, C).Resize(r, 1).Value = "": Next C
            End If
End Sub

Try this:

Sub SumeshV(): Dim d As Long, C As Long, R As Long
Dim F1 As String: F1 = "=IF(RC[-8]>RC[-9],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-19]-RC[-20])"
Dim F3 As String: F3 = "=AVERAGE(RC[-10]:RC[-1])"

R = Range("B" & Rows.count).End(xlUp).Row - 1
d = Range("A1") + 1: Range("A1") = d

            If d > 9 Then
Cells(2, 3).Resize(R, 87).ClearContents
Range("A1") = 0
            Else
        For C = 3 To 61 Step 29
    Cells(2, C + 1).Resize(R, 9).Value = _
    Cells(2, C).Resize(R, 9).Value
    Cells(2, C).Resize(R, 1).Value = "": Next C
            
            If d > 1 Then
                    For C = 14 To 72 Step 29
            Cells(2, C).Resize(R, d).Formula = F1
            Cells(2, C + 9).Resize(R, d).Formula = F2
            Cells(2, C - 1).Resize(R, 1).Formula = F3
                    Next C
            End If: End If
End Sub

Maybe:

Sub SumeshVI(): Dim d As Long, C As Long, R As Long
Dim F1 As String: F1 = "=IF(RC[-8]>RC[-9],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-19]-RC[-20])"
Dim F3 As String: F3 = "=AVERAGE(RC[-10]:RC[-1])"

R = Range("B" & Rows.count).End(xlUp).Row - 1
d = Range("A1") + 1: Range("A1") = d

            If d > 9 Then
Cells(2, 3).Resize(R, 87).ClearContents
Range("A1") = 0
            Else
        For C = 3 To 61 Step 29
    Cells(2, C + 1).Resize(R, 9).Value = _
    Cells(2, C).Resize(R, 9).Value
    Cells(2, C).Resize(R, 1).Value = "": Next C
            
            If d > 1 Then
                    For C = 14 To 72 Step 29
            Cells(2, C + 1).Resize(R, d - 1).Formula = F1
            Cells(2, C + 10).Resize(R, d - 1).Formula = F2
            Cells(2, C - 1).Resize(R, 1).Formula = F3
                    Next C
            End If: End If

Sub SumeshVI(): Dim d As Long, C As Long, R As Long
Dim F1 As String: F1 = "=IF(RC[-8]>RC[-9],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-19]-RC[-20])"
Dim F3 As String: F3 = "=AVERAGE(RC[-10]:RC[-1])"

R = Range("B" & Rows.count).End(xlUp).Row - 1
d = Range("A1") + 1: Range("A1") = d

            If d > 9 Then
Cells(2, 3).Resize(R, 87).ClearContents
Range("A1") = 0
            Else
        For C = 3 To 61 Step 29
    Cells(2, C + 1).Resize(R, 9).Value = _
    Cells(2, C).Resize(R, 9).Value
    Cells(2, C).Resize(R, 1).Value = "": Next C
            
            If d > 1 Then
                    For C = 14 To 72 Step 29
            Cells(2, C + 1).Resize(R, d - 1).Formula = F1
            Cells(2, C + 10).Resize(R, d - 1).Formula = F2
            Cells(2, C - 1).Resize(R, 1).Formula = F3
                    Next C
            End If: End If
End Sub

I changed formula 1:

Sub SumeshVI(): Dim d As Long, C As Long, r As Long
Dim F1 As String: F1 = "=IF(RC[-10]>RC[-11],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-19]-RC[-20])"
Dim F3 As String: F3 = "=AVERAGE(RC[-10]:RC[-1])"

r = Range("B" & Rows.count).End(xlUp).Row - 1
d = Range("A1") + 1: Range("A1") = d

            If d > 9 Then
Cells(2, 3).Resize(r, 87).ClearContents
Range("A1") = 0
            Else
        For C = 3 To 61 Step 29
    Cells(2, C + 1).Resize(r, 9).Value = _
    Cells(2, C).Resize(r, 9).Value
    Cells(2, C).Resize(r, 1).Value = "": Next C
            
            If d > 1 Then
                    For C = 14 To 72 Step 29
            Cells(2, C + 1).Resize(r, d - 1).Formula = F1
            Cells(2, C + 10).Resize(r, d - 1).Formula = F2
            Cells(2, C - 1).Resize(r, 1).Formula = F3
                    Next C
            End If: End If
End Sub

thanks for the quick response.


i need some more help in this matter.
pls see that i have sheets named MY STOCK SAMPLE (DAY1)......MY STOCK SAMPLE (DAY2) ..ETC
READ MY STOCK SAMPLE (DAY1) as MSSDAY(1)

I have column header like DAY1.....DAY2...ETC

after i run the code at sheet mssday(9),.NOW I will be having only the baseprice column (col C) base vol , and base del per columns empty.

the resultant content should be copied to sheet named MSSDAY(10), here i will fill the details in the baseprice column.,
then i run the code NOT to get the data transferred to the adjecent column, but to excecute the formula only.

Now i will be having all the columns filled with data and the formulae excecuted.Now i have ten days data. ( nine days plus the base col)

Now i want the contents of this sheet MSSDAY(10)be transferred to sheet named MSS RESET, Here i will run the code and all the data will be erased.
at present when i run the code at sheet mssday(9) the data is erased.
pls see sheets named MSSDAY(8)MSSDAY(9) and MSSDAY(10) of the newly attached file for clarification. thanks.

My code is designed to do everything on just one sheet - have you been creating a sheet for each day?

I thought that the multi-sheet samples were just to provide an example of each days results.

So, I've been pondering exactly what is needed. This is my quandary.

Originally Posted by xladept

My code is designed to do everything on just one sheet - have you been creating a sheet for each day?

certainly not. i want that the whole process be done in just two sheets. i will explain. pls see the attached files.

I thought that the multi-sheet samples were just to provide an example of each days results.

you were right.

So, I've been pondering exactly what is needed. This is my quandary.

allow me,

on reaching the ninth day, i will be having the base columns empty as well as the first column of the comparison ( col N, col W and col AQ).
After entering the data in base columns,when i run the code i am supposed to get the columns N,W AND AQ filled with the results.

but at present when i run the code all data is erased.
That is why i demanded that when i run the code on sheet named day9 the result in col N.W, AND AQ be filled and all the entries be copied to the next worksheet named day10.


now only i will be having the full data of ten days with all the calculations of formulae.
there i will run the code just to get all the data erased.

plse see the attached file for clarification. pls run the code on the first sheet and find the result. first sheet is supposed to be the Day9 sheet where i have entered the base values.now we have to run the code to get the empty columns of comparison to be filled with results.
the second sheet is supposed to be the DAy10 sheet where i should get all the data filled. and when i run the code all data should be erased.

See if this does it:

Sub SumeshVI(): Dim d As Long, C As Long, r As Long
Dim F1 As String: F1 = "=IF(RC[-10]>RC[-11],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-19]-RC[-20])"
Dim F3 As String: F3 = "=AVERAGE(RC[-10]:RC[-1])"

r = Range("B" & Rows.count).End(xlUp).Row - 1
d = Range("A1") + 1: Range("A1") = d

            If d > 9 Then
Cells.Copy Sheets("MY STOCKSAMPLE-RESET").Cells(1, 1)
Cells(2, 3).Resize(r, 87).ClearContents
Range("A1") = 0
            Else
        For C = 3 To 61 Step 29
    Cells(2, C + 1).Resize(r, 9).Value = _
    Cells(2, C).Resize(r, 9).Value
    Cells(2, C).Resize(r, 1).Value = "": Next C
            
            If d > 1 Then
                    For C = 14 To 72 Step 29
            Cells(2, C + 1).Resize(r, d - 1).Formula = F1
            Cells(2, C + 10).Resize(r, d - 1).Formula = F2
            Cells(2, C - 1).Resize(r, 1).Formula = F3
                    Next C
            End If: End If
End Sub

is there any prerequisite to run the code? i tested this code on a single sheet named day9.



I run the code and get this error

it says
Runtime error (9)
subscript out of range
debugged to see this

Cells.Copy Sheets("MY STOCKSAMPLE-RESET").Cells(1, 1)



I tested it on a sheet named My stock sample-Reset.when i run the code all data were erased. what the code should do is to copy all the data from day9 sheet and paste it on another sheet named mystocksample-reset. then on that sheet when i run the code the data should be erased.
pl see the attached file

That just means that there is no sheet named "MY STOCKSAMPLE-RESET".

Well - I changed the sheet name to correspond to the latest RESET sheet and it seems to work nicely:

Sub SumeshVI(): Dim D As Long, c As Long, r As Long
Dim F1 As String: F1 = "=IF(RC[-10]>RC[-11],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-19]-RC[-20])"
Dim F3 As String: F3 = "=AVERAGE(RC[-10]:RC[-1])"

r = Range("B" & Rows.count).End(xlUp).Row - 1
D = Range("A1") + 1: Range("A1") = D

            If D > 9 Then
Cells.Copy Sheets("RESET").Cells(1, 1)
Cells(2, 3).Resize(r, 87).ClearContents
Range("A1") = 0
            Else
        For c = 3 To 61 Step 29
    Cells(2, c + 1).Resize(r, 9).Value = _
    Cells(2, c).Resize(r, 9).Value
    Cells(2, c).Resize(r, 1).Value = "": Next c
            
            If D > 1 Then
                    For c = 14 To 72 Step 29
            Cells(2, c + 1).Resize(r, D - 1).Formula = F1
            Cells(2, c + 10).Resize(r, D - 1).Formula = F2
            Cells(2, c - 1).Resize(r, 1).Formula = F3
                    Next c
            End If: End If
End Sub

There is no data in those fields.

You had me stop including those fields several posts ago

Originally Posted by xladept

You had me stop including those fields several posts ago

sorry for that confusion.

every time i am quoting the file. pls be kind enough to look through the file .

let me explain once again.

In my worksheet, there are three sections viz the price of the shares, the volume and the delivery percentage.

in each catagory there are three parts. col C:AE covers the first catagory regarding the price.
col AF:BH covers the second catagory regarding the volume
col B1: CK covers the thrid catagory regarding the delevery percentage.

first cell of each catagory is filled by the user and other columns will be filled by the execution of the code.

(1) column C is always filled by the user.
col N:V and col W:AE is supposed to be filled by the result of the formula applied which will be brought by the code




(2) column AF is always filled by the user.
col AQ:AY and col AZ:BH is supposed to be filled by the result of the formula applied which will be brought by the code



(3) column B1 is always filled by the user.
col BT:CB and col CC:CK is supposed to be filled by the result of the formula applied which will be brought by the code

From your post #14:

but it may be noted that the comparison the code makes is erraneous.
there will be no comparison on day1 sheet.
on day2 sheet the comparison can only be done between E and D. see the column heading .there is data only on these two columns at present (after we run the code data from C move to D. then only the comparison should work.)

It seems pretty clear!

thanks for the reply. Pls don't be irritated. since my initial post many changes have occured in the original demand. there were ten sheets which have come to two. there has been an evolution of my request, some minor changes.

at present we have data on column C, so there is a possibility of comparison with col D. at the time of post No.14, the col C was empty. so comparison was not possible at that time........

anyhow,we have travelled a long way together. we have reached just near the far end of our journey. based upon my last post , please help to resolve the issue.

Try this - let me know:

Sub SumeshVII(): Dim D As Long, C As Long, r As Long
Dim F1 As String: F1 = "=IF(RC[-10]>RC[-11],""Y"",""N"")"
Dim F2 As String: F2 = "=SUM(RC[-19]-RC[-20])"
Dim F3 As String: F3 = "=AVERAGE(RC[-10]:RC[-1])"

r = Range("B" & Rows.count).End(xlUp).Row - 1
D = Range("A1") + 1: Range("A1") = D

            If D > 9 Then
Cells.Copy Sheets("RESET").Cells(1, 1)
Cells(2, 3).Resize(r, 87).ClearContents
Range("A1") = 0
            Else
        For C = 3 To 61 Step 29
    Cells(2, C + 1).Resize(r, 9).Value = _
    Cells(2, C).Resize(r, 9).Value
    Cells(2, C).Resize(r, 1).Value = "": Next C
            
            If D > 1 Then
                    For C = 14 To 72 Step 29
            Cells(2, C).Resize(r, D).Formula = F1
            Cells(2, C + 9).Resize(r, D).Formula = F2
            Cells(2, C - 1).Resize(r, 1).Formula = F3
                    Next C
            End If: End If
End Sub

Hi Sumesh,

The Reset sheet is just a copy of the day 9 sheet - is the day 9 sheet now incorrect?