Automatic formula entry, file save and cell blanking
 

Hello All,

I have a few questions that I hope you guys will be able to help me with. I
am really new to this and have absolutely no programming knowledge.

1. I am putting together an excel sheet for data collection, which can
contain few thousand entries one in each row ranging from columns 1 to 18.
Some of these cells have formulas previously entered like vlookup, len etc.
When data is entered in columns 1 and 2, the rest of the cells are
automatically filled based on these formulas. The rows from 2 to 51 have
these formulas entered in sequence. Now this is my problem: since there might
be few thousand entries, it becomes tedious to enter the formula in each and
every row, not knowing how many rows to enter. Is there any VB code that will
automatically enter the formula in the next row if it detects an entry in the
first two columns? I have tried to select and drag the cells but the formula
doest come our properly and the numbers all get mixed up.

2. After all the data is entered in the above file, the user will close it.
I would like it to automatically save (save as, so that the original file is
intact) it in a given folder in C: with a name already specified in a cell
say A1 along with the present date.

3. Since the same template would be used in different sites, again a couple
of thousands. It would be a nice option if the user could click on the save
icon, which instead of over writing, uses the save as option to save it as a
new file with name specified in a cell as mentioned above. After it saves,
the first two columns where the data has been entered should be blanked. This
will minimise user involvement and speed up the data collection process.

I have seen various codes on this site, but none were like I wanted. I also
tried putting them together without any success. Certainly hope someone out
there would be kind enough to help me out, keeping in mind that I have no
programming knowledge.

Thanks in advance.

Automatic formula entry, file save and cell blanking
 

Hi Gary,

Thanks for the reply. I have already tried that approach, however, as I
mentioned, although if is in a sequence, the formula does not get transferred
properly. The numbers all get messed up and I have to correct them manually.
Hence I was wondering if there was a VB code that would automatically check
the formula in one row, and put them in the next as per the sequence given.

"Gary''s Student" wrote:

This can help you with the first question. Say we have data in column A from
A1 thru A1000 and we have a formula in B1 only.

We want to copy down from B2 to where the data ends in column A.

1. click on B1
2. put the mouse in the center of B1 and the cursor will become a hollow plus
3. pull the mouse down to the cell edge and the cursor will become a thin
plus with arrowheads
4. pull the mouse over to the corner and the cursor will become a solid plus
5. double-right-click and the formula will be copied down column B as far as
there is data in column A
--
Gary''s Student - gsnu200814


"Vipin Palazhi" wrote:

Hello All,

I have a few questions that I hope you guys will be able to help me with. I
am really new to this and have absolutely no programming knowledge.

1. I am putting together an excel sheet for data collection, which can
contain few thousand entries one in each row ranging from columns 1 to 18.
Some of these cells have formulas previously entered like vlookup, len etc.
When data is entered in columns 1 and 2, the rest of the cells are
automatically filled based on these formulas. The rows from 2 to 51 have
these formulas entered in sequence. Now this is my problem: since there might
be few thousand entries, it becomes tedious to enter the formula in each and
every row, not knowing how many rows to enter. Is there any VB code that will
automatically enter the formula in the next row if it detects an entry in the
first two columns? I have tried to select and drag the cells but the formula
doest come our properly and the numbers all get mixed up.

2. After all the data is entered in the above file, the user will close it.
I would like it to automatically save (save as, so that the original file is
intact) it in a given folder in C: with a name already specified in a cell
say A1 along with the present date.

3. Since the same template would be used in different sites, again a couple
of thousands. It would be a nice option if the user could click on the save
icon, which instead of over writing, uses the save as option to save it as a
new file with name specified in a cell as mentioned above. After it saves,
the first two columns where the data has been entered should be blanked. This
will minimise user involvement and speed up the data collection process.

I have seen various codes on this site, but none were like I wanted. I also
tried putting them together without any success. Certainly hope someone out
there would be kind enough to help me out, keeping in mind that I have no
programming knowledge.

Thanks in advance.