CODEWITHSUNDEEP
vbaexcel
suzie_q
suzie_q

Reputation: 39

VBA: Max of 2 columns gets sent to third column

I need to get the maximum value from two columns and send it to a third column. The columns are all uniform in size but sometimes the sizes will be different, however they will all start in the same cell. For example:

5 8 -

6 2 -

6 5 -

The column with the dashes would need to find the maximum between the other two, and the finished project would look like

5 8 8

6 2 6

6 5 6

I tried recording a macro but it used ActiveCell which isn't good. I want The two columns to be, say, anything starting from C10 that has a value, and everything starting in D10 that has a value, and the max values sent to E10.

Here's what I recorded, trying to just fill the destination cell with the formula:

ActiveCell.FormulaR1C1 = "=MAX(RC[-2],RC[-1])"
Selection.AutoFill Destination:=Range("E10:E300"), Type:=xlFillDefault

Upvotes: 1

Views: 471

Answers (1)

Vityata
Vityata

Reputation: 43585

Here is a good alternative of your code, just make sure to declare the first column that you compare with. The example works with the first 10 cells of Column A:

Option Explicit

Public Sub SelectMax()


    Dim rngRange    As Range
    Dim rngCell     As Range

    Set rngRange = Range("A1:A10")

    For Each rngCell In rngRange

        'Without visible formula in Excel:
        rngCell.Offset(0, 2) = WorksheetFunction.Max(rngCell, rngCell.Offset(0, 1))
        'With visible formula in Excel
        rngCell.Offset(0, 2).FormulaR1C1 = "=MAX(RC[-2],RC[-1])"

    Next rngCell

End Sub

The example makes a visible formula in Excel. If you want to ignore the formula, remove the line after the second comment and the formula will not appear.

Upvotes: 3

Related Questions