Solved

VBA Autofill Down

Posted on 2015-02-10
16
47 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 45

Expert Comment

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

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

Expert Comment

by:Rob Henson
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:jmac001
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 100 total points
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

762 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

12 Experts available now in Live!

Get 1:1 Help Now