[RESOLVED] Set minimum value Column A (VBA)

[Radjesh Klauke]

Hi,

I have a lot of values in column A. A lot of them are below a value of 10, they all should have a minimum of 10. Anyone with a quick VBA solution?

I tried:

Sub test()
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In ActiveSheet.UsedRange.Columns("A")
        If Trim(c.Value) > 0 And c.Value < 10 Then
            c.Value = 10
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

It should skip empty cells if possible.

Thanks in advance.

[westconn1]

I tried:

so what happened?
wrong result?
error?
nothing?

[Radjesh Klauke]

Bit stupid not the tell you that. Sorry.

"Types Mismatched"

Sub test()
    Dim c As Range
    Application.ScreenUpdating = False
    For Each c In ActiveSheet.UsedRange.Columns("A")
        If Not IsEmpty(c.Value) Then
            If c.Value < 10 Then
                c.Value = 10
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

[Radjesh Klauke]

Was doing dishes and figured it out. It should be ...Range("A:A") instead of columns("A").
The code is show however. Any suggestions?

[4x2y]

Try this

Sub test()
    For j = 1 To 10 ' replace 10 with the number of last row
        If Trim$(Cells(j, 1)) <> vbNullString Then ' it is not empty
            If Val(Cells(j, 1)) < 10 Then
                Cells(j, 1) = 10
            End If
        End If
    Next
End Sub

I don't know how to enumerate up to last row in a column, so i hard coded as 10, you should change it.

[Radjesh Klauke]

That one is great m8. Simply add:
For j = 1 to activesheet.usedrange.rows.count

Thanks.