For VBA loops. Macro Excel Program

Question

I have problem with my project using VBA. I have this column that has thousands of rows.

And these columns have values like 0.05, 1.3 etc.. and it also has complicated values with greater than sign < like <0.05, <0.02 which is my real problem. I want to use If Else or Looping for this solution. But I don't know how. I'm a beginner in VBA macro excel.

What I want to happen from these rows is if Macro detect a value that has < it will automatically be divided by 2 so I won't have complicated values to get the maximum and minimum value for these rows.

Edit1: Uploaded Image

sample image

I hope you get my point about this matter. Sorry for my english. Thanks for your help.

First in VBA use If IsNumeric(Range) Then to trap cases with "<" in the cell value, then Range.Value = CDbl(Replace(Range.Value,"<",""))/2, assuming overwriting the value. — PatricK
– PatricK, Commented Apr 28, 2014 at 3:41

Siddharth Rout · Accepted Answer · 2014-04-28 04:37:48Z

1

Here is a faster way using Autofilter. Using Autofilter will ensure that you will not have to loop through every cell.

Sample Screenshot:

enter image description here

Code:

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range, aCell As Range, fltrdRng As Range
    Dim LRow As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> Get the last row of Col H where your data is
        LRow = .Range("H" & .Rows.Count).End(xlUp).Row

        '~~> Set your range
        Set rng = .Range("H1:H" & LRow)

        With rng
            .AutoFilter Field:=1, Criteria1:="=<*"
            Set fltrdRng = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        If Not fltrdRng Is Nothing Then
            For Each aCell In fltrdRng.Cells
                If aCell.Row > LRow Then Exit For

                '~~> 8 is for Col H
                If aCell.Column = 8 Then
                    aCell.Value = Replace(aCell.Value, "<", "")
                    aCell.Value = Val(Trim(aCell.Value)) / 2
                End If
            Next aCell
        End If
        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

answered Apr 28, 2014 at 4:37

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3400194 Over a year ago

Wow that was fast answer, and that really works. Thank you so much @Siddhart Rout. It helps a lot. Also thank you to those who answered my question. I'm loving this site and people around here such a genius persons. God bless.

L42 · Accepted Answer · 2014-04-28 03:47:21Z

1

Try this then:

Sub RemoveComplicatedValues()
Dim rng As Range, cel As Range
Dim x

Set rng = Range("H2", Range("H" & Rows.Count).End(xlUp).Address)

For Each cel In rng
    If InStr(1, cel, "<") <> 0 Then
        x = CSng(Split(cel, "<")(UBound(Split(cel, "<"))))
        cel = x / 2
    End If
Next     
End Sub

You can also fully qualify your Workbook and Sheet if you want.
Hope this helps.

answered Apr 28, 2014 at 3:47

L42

19.8k11 gold badges47 silver badges70 bronze badges

Collectives™ on Stack Overflow

For VBA loops. Macro Excel Program

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related