Solved

VBA Autofill Down

Posted on 2015-02-10
16
54 Views
Last Modified: 2015-02-13
Hi Exports,

I would like to auto fill a couple of fields based on criteria being met.  Found some vba code but not quite what I was looking for.    I would like to auto fill column A with a number (in sequential order) if column C is populated with text.  And then I would like to auto populate column H with the C4:G6 (Address, Contact Person, Contact details: phone and email) each on a separate line within the same cell.   Is this possible?

Attached is the workbook.
Test-Snag-Template-w-Issue-Log-02.10.xls
0
Comment
Question by:jmac001
  • 7
  • 3
  • 3
  • +1
16 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40601031
So when would you like this to happen?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40601367
Is this what you want ?

When you input something in Col C it will update both Col A with a numbering and Col H with the ship address I have put a sample. If you delete the cell in C see what happens.

gowlfow
Test-Snag-Template-w-Issue-Log-02.10-V01
0
 

Author Comment

by:jmac001
ID: 40601448
Martin,

It would be when the issue typed in or populated from the snag list tab.


Gowflow,

Unable to open, no file extension.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

Expert Comment

by:Martin Liss
ID: 40601462
I'll give it a try but you can probably open GowFlow's submission by right-clicking on it and choosing Open With and then Excel.
0
 

Author Comment

by:jmac001
ID: 40601617
Thanks Martin, had a little brain freeze forgot that I had to find the excel.exe since it was not in my list of progams the first time I tried to open.

Gowflow - broke the vba to move the Snag List items with yes in column S over to the next available line in the Issue Log.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40601653
Sorry did not understand your comment. What do you need from me ?
I shortened the filename so that extention appears. It is same file as before.
gowflow
Test-Snag-Template-V01.xls
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40601712
When you say " if column C is populated with text", which sheet are you referring to?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40602899
There is an Option within Excel that Formats and Formulas are copied down automatically, this is in:

Excel Options > Advanced > "Extend data range formats and Formulas"

If your sequential numbering and Address fields are formula driven, this will copy down automatically.

If your list is formatted as a table, this will also occur.

To join up the contact details, you can use:

=C4&CHAR(10)&C5&CHAR(10)&C6&CHAR(10)

You may have to tweak the cell format to get it to show correctly; formats such as Wrap text may affect it.

Thanks
Rob H
0
 

Author Comment

by:jmac001
ID: 40603234
Gowflow - I was able to get the  file open and when I was checking to make sure that all of the functionality was working.  The vba that is on the Snag List sheet is not working along with the new vba that you provided.  The SNAG LIST vba will copy over the snag comment to the next available line in the Issue Log when there is a yes in column S.
0
 

Author Comment

by:jmac001
ID: 40603245
Martin,

I am referring to the Issue logs (column C) on the Issue Log.  When the user type in an issue or if the issue is populated based on the Snag List comments be copied over from the Snag List tab.
0
 

Author Comment

by:jmac001
ID: 40603254
Rob,

I currently have a formula in the field, but didn't want to chance user deleting the formula in the unused rows and didn't necessarily want to go with having to protect the sheet to lock the cells.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40603309
With the setting or table format I am suggesting, you wouldn't have to pre-populate the unused rows, the formulas "should" copy down.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 400 total points
ID: 40603547
ok you are correct, it has been corrected in this version. I did not notice interaction with SNAG LOG now fixed.
gowflow
Test-Snag-Template-V02.xls
0
 

Author Comment

by:jmac001
ID: 40606728
Rob I am intrigued by formatting that you suggested and I could possibly use it in another form, but I am still not clear as to where the formula is placed.   When I go to Excel Options > Advanced > "Extend data range formats and Formulas" it is a checkbox which is currently checked.


Gowflow - Having a problem testing on my desktop.  Will check functionality on my laptop and see let you know.
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
ID: 40606845
With the extend data range formats and formulas checked, any formats and formulas you already have in your data table should be extended down as you enter new data below the existing.

Converting your data to a table rather than a list does this as well. Put cursor in data list and go to insert tab, click table and the data area should be selected. Click OK, the standard formatting of tables is blue and white stripes. When you add data in first blank row below the table the formatting will extend down and formulas that are repeated in rows above will copy down.

The formula I was suggesting was to create the address field , CHAR(10) being the carriage return between each field.

Thanks
Rob H
0
 

Author Closing Comment

by:jmac001
ID: 40608760
Thank you GowFlow I was able to get the example to work correctly on both my desktop and laptop.

Rob went with GowFlow as I was quickly able to get it to run but will take note for future reports on the extend down functionality.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question