VBA for cell division

[sburge]

I’m trying to come up with a macro that will divide the values in a cell range (of an active cell row), by the value of the active cell.
In other words: A1 = 4 (active cell), A2=8 and A3=16. The result would be A2=2 and A3=4. I would select the first cell in any given row, and then run the macro.

Thanks in advance, Steve

 

[Scott Huish]

I assume you mean column because A2 and A3 are in the same column not the same row.

Sub test()
On Error Resume Next
Dim c As Range, d As Range, e As Range
Set c = ActiveCell
Set d = c.Offset(1).Resize(c.End(xlDown).Row - 1)
For Each e In d
    If IsNumeric(e) And e <> "" Then e = e / c
Next
End Sub

 

[kpasa]

Sub CalculateColValue()
    Dim myColIndex As Integer
    Dim myRowIndex As Integer
    Dim rng, cell As Range
    Dim divisor As Integer
    myColIndex = Application.ActiveCell.Column
    myRowIndex = Application.ActiveSheet.Cells(Rows.Count, myColIndex).End(xlUp).Row
    Set rng = Range(Cells(2, myColIndex), Cells(myRowIndex, myColIndex))
    divisor = ActiveCell.Value
    
    For Each cell In rng
        cell.Value = cell.Value / divisor
    Next cell
End Sub

 

[sburge]

I really screwed that one up. It should read: A1 = 4 (active cell), B1=8 and C1=16. The result would be B1=2 and C1=4.
Sorry...

 

[Scott Huish]

Sub test()
ActiveCell.Copy
ActiveCell.Offset(, 1).Resize(, ActiveCell.End(xlToRight).Column - 1).PasteSpecial , xlPasteSpecialOperationDivide
Application.CutCopyMode = False
End Sub

 

[sburge]

Scott, thanks so much for that.