General discussion

Locked

Excel - Easy VBA?

By kbrown ·
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.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel - Easy VBA?

by tclere In reply to Excel - Easy VBA?

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.

Collapse -

Excel - Easy VBA?

by kbrown In reply to Excel - Easy VBA?

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

Collapse -

Excel - Easy VBA?

by kbrown In reply to Excel - Easy VBA?

This question was closed by the author

Back to Desktop Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums