autofill a range in excel vba 2

[beginner999]

what iam doing here is

my excel has several rows.
A 1 2 3 4
B 4 5 6 7
c 2 4 6 4
D 4 5 8 7
each time i cut first row and paste it next to second row
B 4 5 6 7 A 1 2 3 4
c 2 4 6 4
D 4 5 8 7
Then autofill the remaining rows
B 4 5 6 7 A 1 2 3 4
c 2 4 6 4 A 1 2 3 4
D 4 5 8 7 A 1 2 3 4

Then copy the intial left rows like this

B 4 5 6 7 A 1 2 3 4
c 2 4 6 4 A 1 2 3 4
D 4 5 8 7 A 1 2 3 4

B 4 5 6 7
c 2 4 6 4
D 4 5 8 7

Then cut the next row and repeat the above steps

B 4 5 6 7 A 1 2 3 4
c 2 4 6 4 A 1 2 3 4
D 4 5 8 7 A 1 2 3 4

c 2 4 6 4 B 4 5 6 7
D 4 5 8 7

and repeat the above steps

B 4 5 6 7 A 1 2 3 4
c 2 4 6 4 A 1 2 3 4
D 4 5 8 7 A 1 2 3 4

c 2 4 6 4 B 4 5 6 7
D 4 5 8 7 B 4 5 6 7

For the above problem i started like this and this is just beginning and I got struck at the auto fill. I appreciate your help. The autofill is giving me the error. The error is " Application-defined error or object-defined error

Sub Test()

Dim n As Integer
Dim i As Integer
n = ActiveCell.CurrentRegion.Rows.Count
For i = 1 To n
UsedRange.Rows(i).Cut
Range("o2").Select
ActiveSheet.Paste
ActiveSheet.Select
Selection.AutoFill Destination:=Range("o3:ab" & n), Type:=xlFillDefault

Next i

 

[ck1999]

Two observations

1) Selection.AutoFill Destination:=Range("o3:ab" & n), Type:=xlFillDefault

probrably should start at o2 not o3

2) Also
Selection.AutoFill Destination:=Range("o3:ab" & n), Type:=xlFillDefault

I think you have to either autofill down or across but not both.

ck1999

 

[AnotherHiggins]

To verify: "B 4 5 6 7 A 1 2 3 4" will go into a single cell, right? What cell? The one that originally contained "A 1 2 3 4"?

Te problem is that you aren't autofilling. Rather, you are appending a string to what's already in the cell.

Play around with Autofill in Excel (not with VBA) to see how it works. It can't do what you're after here.

Where would the "c 2 4 6 4 B 4 5 6 7" series go? Below the row containing the "B 4 5 6 7 A 1 2 3 4" series?

Please provide an example of what the final result would look like for your example data including. It's hard to help you get where you're going if we don't know where you're headed.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[beginner999]

hi ck199
after changing to 02 also it didn't work.
Thanks

 

[beginner999]

hi anotherhiggins
each number goes to each cell. It's not a string. here in the example I divided each cell with a space.
My final result looks like this

B 4 5 6 7 A 1 2 3 4
c 2 4 6 4 A 1 2 3 4
D 4 5 8 7 A 1 2 3 4

c 2 4 6 4 B 4 5 6 7
D 4 5 8 7 B 4 5 6 7


Thanks

 

[ck1999]

to autofill down use

Selection.AutoFill Destination:=Range("o2" & n), Type:=xlFillDefault

This will copy whatever is in cell "o2" into cells o3 down to o row N

Like I said you can not autofill both down and to the right

you could try

Selection.AutoFill Destination:=Range("o2" & n), Type:=xlFillDefault

followed with
Range("o2" & n).Select
Selection.AutoFill Destination:=Range("o2:ab" & n), Type:=xlFillDefault

but it is a two step process

And this is just to be able to autofill correctly. Read what John wrote about if autofill will do what you want it to do.

ck1999

 

[beginner999]

Thanks much ck1999. It's working now. but when autofilling it's incrementing the value by 1. what can i do incrementing. my code is like this.

Range("o2:ab2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("o2:ab" & n), Type:=xlFillDefault

 

[AnotherHiggins]

Change from
[tab]xlFillDefault
to
[tab]xlFillCopy


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[beginner999]

Could you guys please help me with the logic to achieve this.

 

[beginner999]

Please help me with this finished this step
Then autofill the remaining rows
B 4 5 6 7 A 1 2 3 4
c 2 4 6 4 A 1 2 3 4
D 4 5 8 7 A 1 2 3 4

any idea how can i achieve this step in a loop (copy the intial left rows like below)
B 4 5 6 7 A 1 2 3 4
c 2 4 6 4 A 1 2 3 4
D 4 5 8 7 A 1 2 3 4

B 4 5 6 7
c 2 4 6 4
D 4 5 8 7

 

[AnotherHiggins]

OK - each value goes in it's own cell. Got it. But things are still about as clear as mud.

You only state that it does As and Bs. Does it stop there, or will the process repeat again to give you :
D 4 5 8 7 c 2 4 6 4
?

If there are lots and lots of rows, do you want to repeat the pattern until you're out of rows?

Do you really want an empty row between 'sections'?

Please state exactly what it is you are trying to do. Providing ranges where things are and where you'd like them to end up would help.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[beginner999]

Thanks John, I appreciate your help
there are lots and lots of rows, I want to repeat the pattern until Iam out of rows
empty row between 'sections' may be helpful in copying the range right.
the process is exactly same as I mentioned in my first thread. Let me know if that is not clear. I will explain more.

 

[ck1999]

Can you explain if this is correct
Start With
Cell 1:A 1 2 3 4
Cell 2:B 4 5 6 7
Cell 3:c 2 4 6 4
Cell 4 4 5 8 7

Want
Cell 1:B 4 5 6 7 A 1 2 3 4
Cell 2:c 2 4 6 4 A 1 2 3 4
Cell 3 4 5 8 7 A 1 2 3 4

Then what do you want?

Cell1:
Cell2:
Cell3:
etc.


ck1999

 

[beginner999]

here each line is each row and each alphabet/number is each cell

row 1:A 1 2 3 4
row 2:B 4 5 6 7
row 3:c 2 4 6 4
row 4 4 5 8 7

Want
row 1:B 4 5 6 7 A 1 2 3 4
row 2:c 2 4 6 4 A 1 2 3 4
row 3 4 5 8 7 A 1 2 3 4

Then repeat this until the last row
this is after 2nd iteration

row 1:B 4 5 6 7 A 1 2 3 4
row 2:c 2 4 6 4 A 1 2 3 4
row 3 4 5 8 7 A 1 2 3 4
row 4:c 2 4 6 4 B 4 5 6 7
row 5 4 5 8 7 B 4 5 6 7

this is after 3rd iteration

row 1:B 4 5 6 7 A 1 2 3 4
row 2:c 2 4 6 4 A 1 2 3 4
row 3 4 5 8 7 A 1 2 3 4
row 4:c 2 4 6 4 B 4 5 6 7
row 5 4 5 8 7 B 4 5 6 7
row 6 4 5 8 7 c 2 4 6 4

Thanks

 

[ck1999]

Try this code

Code assume table starts in cell a1

Sub help_beg()
    
    Dim vlastrow
    Dim vend
    Dim counter
    Dim counter2
    vend = Range("a1").End(xlDown).Row
    vlastrow = Range("a1").End(xlDown).Row
    counter = 2
    For counter2 = 1 To vend - 1
        Range("F" & counter, "J" & vlastrow).Value = Range("A" & counter - 1, "e" & counter - 1).Value
        If counter2 <> vend - 1 Then
        Range("A" & vlastrow + 1, "E" & vlastrow + (vlastrow - counter) + 1).Value = Range("A" & counter, "e" & vlastrow).Value
        End If
        Rows(counter - 1).Delete
        counter = vlastrow + 1
        vlastrow = Range("a1").End(xlDown).Row
    Next counter2

End Sub

ck1999

 

[beginner999]

Thanks much ck1999. that's gr8.It did work. Could you please explain me with some coments. I really appreciate your help.

 

[ck1999]

Sub help_beg()
    
    Dim vlastrow
    Dim vend
    Dim counter
    Dim counter2
    vend = Range("a1").End(xlDown).Row ' this determines how many iterations to perform. Since each row is equal to 1 iteration
    vlastrow = Range("a1").End(xlDown).Row ' determine how many rows in table
    counter = 2 ' set 1st row of filling range
    For counter2 = 1 To vend - 1 ' use counter for each iteration subtact 1 for the header row
        Range("F" & counter, "J" & vlastrow).Value = Range("A" & counter - 1, "e" & counter - 1).Value ' Copies info you wanted to copy to columns to the right of your table
        If counter2 <> vend - 1 Then ' do not need to extend the list if it is the last item in the list
        Range("A" & vlastrow + 1, "E" & vlastrow + (vlastrow - counter) + 1).Value = Range("A" & counter, "e" & vlastrow).Value ' Copies the rest of the table down below for the next iteration
        End If 
        Rows(counter - 1).Delete ' delete top row for reiteration
        counter = vlastrow + 1 ' reset counter
        vlastrow = Range("a1").End(xlDown).Row ' reset last row since table has been extedneded
    Next counter2

End Sub

ck1999

 

[beginner999]

Hi CK1999,
Could you please tell me how to approach this final step which is a continuation of previous step. I appreciate your help.
This was the last result
row 1:B 4 5 6 7 A 1 2 3 4
row 2:c 2 4 6 4 A 1 2 3 4
row 3 4 5 8 7 A 1 2 3 4
row 4:c 2 4 6 4 B 4 5 6 7
row 5 4 5 8 7 B 4 5 6 7
row 6 4 5 8 7 c 2 4 6 4

In the next step I need to concatenate a1,"/",f1 in k1 cell and then calculate averages for (b1,g1) ; (c1,h1) ; (d1,i1) ; (e1,j1) for all the rows

The final result going to be like this. Part 1 and part are next to each other.because of the space constraint i did divided into 2 parts
part1:
row 1:B 4 5 6 7 A 1 2 3 4
row 2:c 2 4 6 4 A 1 2 3 4
row 3 4 5 8 7 A 1 2 3 4
row 4:c 2 4 6 4 B 4 5 6 7
row 5 4 5 8 7 B 4 5 6 7
row 6 4 5 8 7 c 2 4 6 4
part2:
B/A 2.5 3.5 4.5 5.5
C/A 1.5 3 4.5 4
D/A 2.5 3.5 5.5 5.5
C/B 3 4.5 6 5.5
D/B 4 5 7 7
D/C 3 4.5 7 5.5
Thanks

 

[ck1999]

Sub help_beg2()
    
    Dim vlastrow
    Dim vend
    Dim counter
    Dim counter2
  
    vlastrow = Range("a1").End(xlDown).Row ' get last row

    For counter2 = 1 To vlastrow ' loop through all rows
    Cells(counter2 + 20, 1) = Cells(counter2, 1) & " / " & Cells(counter2, 1 + 5)  ' the 20 tells what row to start ouput in 
       For counter = 2 To 5 ' to average each column in row
        Cells(counter2 + 20, counter) = (Cells(counter2, counter).Value + Cells(counter2, counter + 5).Value) / 2 ' used add and div verses average function since only 2
       Next counter
    Next counter2

End Sub

Hope this helps
ck1999

 

[ck1999]

If you want it side by side change to

cells(counter2, 12) = Cells(counter2, 1) & " / " & Cells(counter2, 1 + 5)
For counter = 2 To 5 ' to average each column in row
Cells(counter2, counter+12) = (Cells(counter2, counter).Value + Cells(counter2, counter + 5).Value) / 2 ' used add and div verses average function since only 2

ck1999