• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

VBA Autofill Down

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
jmac001
Asked:
jmac001
  • 7
  • 3
  • 3
  • +1
2 Solutions
 
Martin LissOlder than dirtCommented:
So when would you like this to happen?
0
 
gowflowCommented:
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
 
jmac001Author Commented:
Martin,

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


Gowflow,

Unable to open, no file extension.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Martin LissOlder than dirtCommented:
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
 
jmac001Author Commented:
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
 
gowflowCommented:
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
 
Martin LissOlder than dirtCommented:
When you say " if column C is populated with text", which sheet are you referring to?
0
 
Rob HensonFinance AnalystCommented:
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
 
jmac001Author Commented:
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
 
jmac001Author Commented:
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
 
jmac001Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
gowflowCommented:
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
 
jmac001Author Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
jmac001Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now