PDA

View Full Version : [SOLVED] AutoFill Question



ddh
11-27-2004, 06:27 AM
Thank you for your help.

The code that I have below is at the Active Cell Inserting a new Row for 17 columns and then it will AutoFill down column A down for 2 cells. I have been trying to include Column B to also Autofill down 2 cells but I always get a error.


Sub asample()
' asample Macro
' Macro recorded 11/23/2004 by
'Dim Rg As Range
Dim SRg As String
Dim LRg As String
Cells(ActiveCell.Row + 1, 1).Resize(1, 17).Insert _
Shift:=xlDown
Set Rg = Selection
SRg = Rg.Item(1).Address
LRg = Rg.Item(Rg.Count).Offset(2, 0).Address
Selection.AutoFill Destination:=Range(SRg & ":" & LRg)
Set Rg = Nothing
End Sub

mdmackillop
11-27-2004, 07:37 AM
Hi DDH,
Try the following
MD


Cells(ActiveCell.Row + 1, 1).Resize(1, 17).Insert _
Shift:=xlDown
Set rg = Selection
SRg = Range(rg.Item(1), rg.Item(i).Offset(1, 16)).Address
Range(SRg).Select
LRg = Range(rg.Item(1).Offset(1, 0), rg.Item(i).Offset(2, 16)).Address
Selection.AutoFill Destination:=Range(SRg & ":" & LRg)

ddh
11-27-2004, 08:30 AM
Thank you very much, I changed the column numbers and it works just I wanted it to.

I try so very hard to write my own code but it is like trying to learn how to speak some foreign langauage. I have a lot of trouble with the ActiveCell part of coding.Thank you for your help.


Sub asample()'
' asample Macro
' Macro recorded 11/23/2004
''Dim Rg As Range
Dim SRg As String
Dim LRg As String
Cells(ActiveCell.Row + 1, 1).Resize(1, 17).Insert _Shift:=xlDownSet Rg = SelectionSRg = Range(Rg.Item(1), Rg.Item(i).Offset(1, 1)).AddressRange(SRg).SelectLRg = Range(Rg.Item(1).Offset(2, 1), Rg.Item(i).Offset(2, 1)).AddressSelection.AutoFill Destination:=Range(SRg & ":" & LRg)
Set Rg = Nothing
End Sub

mdmackillop
11-27-2004, 09:11 AM
Hi DDH,

Never used Resize before, so just playing around with it. A tidier solution to the code would be



Sub DoFill()
Dim Rg As Range, SRg As Range, Lrg As Range
Dim MyCols As Integer, MyRows As Integer
MyCols = 5
MyRows = 5
ActiveCell.Resize(MyRows, MyCols).Select
Selection.Insert Shift:=xlDown
Set Rg = ActiveCell.Offset(-1, 0).Resize(1, MyCols)
Set SRg = Selection
Set Lrg = Union(Rg, SRg)
Rg.Select
Selection.AutoFill Destination:=Lrg
Set Rg = Nothing
ActiveCell.Select
End Sub

ddh
11-27-2004, 10:32 AM
Thank you very much, I will try the new code.

I have a 5 different macros, to insert 1 or 3 or 5 or 10 or 20 lines.
I modified the code you gave me and now it works in all 5 macros.
Below is how I used the code you helped me with.

I am a 62 year old accountant and not many businesses are willing to hire someone the same age as the person in their office that is getting ready to retire. I have a lot of difficultly presenting my resume to businesses for a potential interview and employment.

I started developing a fax and e-mail list so I would have a way for my resume to be viewed. I still don?t have a job, so it has not worked as I expected. Now the list is something to do that helps keep my mind active.

This Fax/E-mail database is on an Excel spreadsheet and added to daily. The database contains six different sections and currently contains 31,935 lines and with a file size of 34.77 MB.

For a business to qualify for entry to the database it must have data in one of the four categories.

(1) POB (2) Fax Number (3) Web Site (4) E-Mail Address.

The list covers an area from Cincinnati to Indianapolis and Muncie to Brookville.

INDIANA Area covered - 19 counties, 324 towns, 6,647 Square Miles, 1,969,938 Population.

OHIO Area covered - 9 counties, 250 towns, 4,011 Square Miles, 2,382,697 Population.



A. MAIN DATABASE: Data is entered daily to the 13 data category columns.

(1) Company Name (2) Number of Employees (3) Address (4) POB (5) Town (6) State (7) ZIP Code

(8) Contact Person (9) Telephone Number (10) Fax Number (11) Web Site (Hyperlink)

(12) Business Type (13) E-Mail Address (Hyperlink).

In the main database along with businesses are Schools, Realtors, Churches, Associations and Societies with each persons or department e-mail address and phone number extension if available and other miscellaneous entries and business chain store numbers and many other forms of information.



B. ALPHABET SUMMARY: This Alphabet list contains individual Letter Totals from the

(1) Company Name Column of the main database.

A content percent and rank column and a bar graph are included.

There are 3 sorting options available.

(1) Ascending (2) Descending (3) Alpha

The alphabet summary section is updated when lines are added to the main database.



C. STATE and TOWN SUMMARY: This State Section contains individual State Totals from the (6) State Column of the main database and the Town Section contains Town Totals from the (5) Town Column of the main database.

A content percent and rank column and a bar graph are included.

The state and town summary sections are updated when lines are added to the main database.



D. TOWN DATABASE: This Town Database contains the following data columns with data from the 2002 Census.

(1) Town Name (2) Town Population (3) % Town Population of County Population

(4) Town Square Miles (5) County Name (6) Map Location (7) County Square Miles

(8) County Population (9) % County Population of State Population (10) Zip code

(11) Area Code (12) County Code (13) Number of Cities in the County.

This section is designed to allow typing the town name in the top left cell and all 13 data categories about the town will be displayed from the database in the top line or you can scroll to the town in the list.

The database can be sorted by each of the above categories except (3), (6), (9), (13).



This section also has a state map allowing you to locate the county on the map where the town is located using the alpha numerical code in the (6) Location column of the database.



E. AREA CODE SUMMARY: This Area Code list contains individual Area Code Totals from the

(9) Telephone Number column of the main database.

A content percent and rank column and a bar graph are included.

There are 2 sorting options available

(1) Area Code (2) Total.

The zip code summary section is updated when lines are added to the main database.



F. COUNTY DATABASE: This County List contains the following data columns with data from the 2002 Census.

(1) County Name (2) County Population (3) % County Population of State Population

(4) County Land Square Miles (5) County Water Square Miles (6) County Total Square Miles

(7) Number of Towns in County (8) % County Towns of Total Counties

(9) Average Town Population (10) Average Population per Square Mile

(11) Average Square Miles per Average Town.

The database can be sorted by each of the above categories except (3) and (8).



Each section also has a lot of other special features not listed above.

This list is just a hobby, like a person that collects a ball of string. I am not selling this list, I am not a business.

I am just trying find a job.

mdmackillop
11-28-2004, 12:54 PM
Glad to help.
Instead of 5 macros, you could change the number of lines to be entered by input box.

MyRows = Inputbox("Enter required number of rows")

ddh
11-28-2004, 06:51 PM
Thank you. That is a very good idea.

I will enter the code in and try and make it work.
Thank you very much for your help.

mdmackillop
11-29-2004, 05:09 PM
Hi DDH,
If you're happy with this solution, you can mark this Solved. Go to Thread Tools at the top of the page.
MD

Zack Barresse
12-08-2004, 02:28 PM
ddh,

Did the solution work for you?

ddh
12-08-2004, 05:41 PM
It worked perfect, thank you very much for all of your help and suggestions.

By adding to the Fax list it gives me something to do and trying to add different things to it.
Thank you again for all of your help.