Discussion Locked

Excel - Easy VBA?

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

We use some really old software on our AS400. I have recently begun importing some data into Excel from these cumbersome databases on our software. The problem I've run into is a Y2K workaround. I have to use string functions to turn the AS400 date into a useable Excel date. That's not a big problem. The problem is that I want to use that formula on every date in the list - which changes length with each download. However, I do not want to have to copy and paste each time.

So, here's thequestion: Is there any VBA code or some Excel feature I can use to place this formula into each cell in my list without copying the formula to the entire column or dragging the formula to the bottom of the list? Automation is the name of the game here.

3 replies

by tclere

In this example I want column C to be the sum of columns A & B. This routine assumes that there will always be an entry in column A for each record.

Option Explicit

Public Sub InsertMyFormula()
Dim i As Long
i = 1 'first row ofdata (not headers)
Do While Len(Range("A" & i).Text) > 0
Range("C" & i).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
i = i + 1
Loop
End Sub

This program will process the command until it reaches the first empty cell in column A.

To do this in your case, just insert your formula in the FormulaR1C1 declaration. You can also record a macro, entering your formula, to see what it looks like in VB. For example, the formula in the above code shows =SUM(A1:B1) in cell C1 and =SUM(An:Bn) in cell Cn.

Let me know if you have any more questions.

by kbrown

Thanks for all your help! This will be a real time saver for a lot of different sheets.

This discussion is closed to new replies.