Solved

VBA Autofill Down

Posted on 2015-02-10
16
52 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

828 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