Solved

VBA Autofill Down

Posted on 2015-02-10
16
48 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 46

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
 
LVL 46

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 46

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 32

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 32

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 32

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now