macro calculate absolute difference between two columns

[beelzebubble]

Hi,

I'm using excel 2003. Using the macro recorder, I was able to produce vba code to calculate the absolute difference between two values for 15 rows of data. That is, 15 data points in column A and 15 data points in column B. The absolute difference values are places in column C.

Sub test()
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=ABS(RC[-2]-RC[-1])"
    Range("C1").Select
    Selection.AutoFill Destination:=Range("C1:C15")
    Range("C1:C15").Select
End Sub

I am trying to modify the above code to make it more flexible, but without success. That is, I would like the code to work on data sets that may have different numbers of rows (i.e., without having to manually change the number of rows). Say, one data set might have 37 rows and another might have 337. Secondly, I would like the code to place the absolute difference scores in the next available column in the excel sheet, rather than specifying the column.

Thanks for your help.

 

[Tom Urtis]

Sub Test1()
Application.ScreenUpdating = False
Dim LastRow As Long, NextColumn As Integer
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
NextColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
Range(Cells(1, NextColumn), Cells(LastRow, NextColumn)).FormulaR1C1 = "=ABS(RC1-RC2)"
Application.ScreenUpdating = True
End Sub

 

[Legacy 68668]

Sub test()
    With Range("A1", Range("a" & Rows.Count).End(xlUp))
        .Offset(, Cells(1, Columns.Count).End(xlToLeft).Column).Formula = _
        "=abs(a1-b1)"
    End With
End Sub

?

 

[beelzebubble]

Thank you, Tom Urtis and Seiya, both solutions worked.