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

 

[beginner999]

Thanks much ck1999. How can I avoid the empty column bet my concatination and the average columns. and also how can I run both the subs at the same time.
Thanks for all your help
I really appreciate it.

 

[ck1999]

To avoid the empty column in the middle replace the 12 with 11

you can at the end of procedure 1 call help_beg2

in the cases above

Next counter2
help_beg2 ' ADD THIS LINE
End Sub


By the way this only works with data that is 5 columns wide. Since that is what this entire thread is based off of. I noticed your other thread only had 3 columns? What does you actual data have before either procedure is started?

You should be able to tweak this procedure to completely fit your requirements


ck1999

 

[beginner999]

Actually I tried that. actually Iam talking about the empty line between concatenation and average columns, not the column between data and concatenation
Thanks

 

[ck1999]

Change
Cells(counter2, counter+12) = (Cells(counter2, counter).Value + Cells(counter2, counter + 5).Value) / 2
to
Cells(counter2, counter+11) = (Cells(counter2, counter).Value + Cells(counter2, counter + 5).Value) / 2

ck1999

 

[beginner999]

Hi ck1999

That empty row is still there even after changing to 11

Thanks

 

[ck1999]

Then change it to 10
Cells(counter2, counter + 10) = (Cells(counter2, counter).Value + Cells(counter2, counter + 5).Value) / 2

ck1999

 

[beginner999]

Thanks ck1999. Sorry one last thing. How can I take care of the programme with header names

 

[ck1999]

Try this
[/code]
Sub help_beg2()

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 = 3 ' set 1st row of filling range
For counter2 = 1 To vend - 2 ' 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 - 2 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

vlastrow = Range("a1").End(xlDown).Row ' get last row

For counter2 = 2 To vlastrow ' loop through all rows
Cells(counter2, 11) = Cells(counter2, 1) & " / " & Cells(counter2, 1 + 5)
For counter = 2 To 5 ' to average each column in row
Cells(counter2, counter + 10) = (Cells(counter2, counter).Value + Cells(counter2, counter + 5).Value) / 2
Next counter
Next counter2

End Sub
[/code]

This combines the two procedures into 1. And will skip a header row.

You should try and step through the code using f8 and watch on your spreadsheet what is happening. This will allow you to learn to make modifications to code to get the desired results.

ck1999

 

[beginner999]

Gr8. Actually before I changed a different counter, which didn't work. Also could you tell me how to programme like you. How can I contact you in the future. Hope you always keep an eye on my postings. thx and really appreciate your help.

 

[ck1999]

The best method for learning is trial and error.

You have your input, use the macro recorder and then play with the code to get your desired results.

Also, to learn to code better keep and eye on this sight and read other threads.

Post your questions on tek-tips to get answers from a variety of people. This site offers a great resource since there are so many people who view this website.



ck1999