How to insert formula into a column of cells within a list of data

**potato** · 12-30-2011, 05:03 AM

Hello!

Need help on macro. Currently, I am writing a macro to populate results into a newly inserted column C (date and time) after I captured info from column A (dates) and column B (time) for a list of data that could easily go beyond thousands row of records.

So far, I managed to find a way to insert of a new column and get the result that I want for one row. However, I do not know how to apply the formula for every single cell until the very last row of data in the list.

This is what I have so far:

Please Login or Register  to view this content.

What I'm trying to do: I insert a new column and a title at the first cell of the column. Then, using a Do Until loop I generate results for the whole list of data by combining info from column A (date) and column B (time) into the newly created column C (date and time) and change the format of the cell. A check was also done using the IF ELSE loop to leave the cell empty if there is no data in column A.

I tried running the above codes but it gives me error at line 5/6. I could have used the wrong syntax to check for empty cells but I do not know what should I use to declare instead. I had also tried running the above codes by commenting codes to test whether the formula works on a single cell and it did. However, due to my limited knowledge on macro/vba, I was unable to continue my coding to get the results that I want. Please advice. ):

I had also posted on another forum previously (www.excelblog.co.uk/forums/t/3221.aspx) but I have not received any replies yet so I decided to post another thread on this forum. I might have used the wrong term but please forgive me as I am very new to excel (started a few days ago). Thank you in advance (:

**antoka05** · 12-30-2011, 05:51 AM

You could use this code:

Please Login or Register  to view this content.

Regards,
Antonio

**potato** · 01-01-2012, 11:29 PM

Sorry for the late reply. What will happen when there are blank cells in either column A or B? Do you mind explaining the need to transform the formula to values? I am currently reading up on macro but am still quite confused even though I work using vb in the past. Really Thanks for your help (:

**WasWodge** · 01-02-2012, 02:37 AM

What will happen when there are blank cells in either column A or B

It will work fine as long as column A has the largest number of used cells (See attachment). I haven't checked the formula just the autofill as you will see.

Do you mind explaining the need to transform the formula to values?

It depends what you need to do with the column afterwards. I would change it to values for instance if I needed to do a sort on the column afterwards or obviously if I wanted to fix the result.
It is up to you whether you want to change it to values or not again depending on what you need to do afterwards. It is not necessary for the formula fill.

HTH