Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

autofill a range in excel vba 2

Status
Not open for further replies.

beginner999

Programmer
Mar 5, 2008
30
US
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


 
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.
 
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
 
Actually I tried that. actually Iam talking about the empty line between concatenation and average columns, not the column between data and concatenation
Thanks
 
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
 
Hi ck1999

That empty row is still there even after changing to 11

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

ck1999
 
Thanks ck1999. Sorry one last thing. How can I take care of the programme with header names
 
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
 
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top