Elena
Elena

Reputation: 19

vba how to optimize macro for many columns

I want to write a macro to Count rows in columns conditionally, I have about 30 columns, i would like to have one macro for all possible columns. I write a macro for the one columns, but dont know how to optimize them. My macro for the first column (110_1.1) is below. And I Need to write a macro for the next columns "120_1.2", "130_1.3", ... and so on until "810_8.1" and hence Sheets("Element_errors").Cells(i, j) should take values "err120","err130",.."err810", and j=1..30 for every column. And Problem is also that all columns dont go one after another.

 Sub Element110_error()
    Dim zelle As Range
    Dim i As Integer
    Dim posMonitoring As Integer
    Dim j As Integer
    Dim intLastRow As Integer

    With Sheets("ICS Table")
     intLastRow = .UsedRange.Rows.Count
    Set zelle = .Cells.Find("110_1.1", lookat:=xlPart)

    If zelle Is Nothing Then
        For i = 2 To intLastRow
            Sheets("Element_errors").Cells(i, 1).Value = "no data"
        Next i
    Else
        posMonitoring = zelle.Column

        For i = 2 To intLastRow
            If .Cells(i, posMonitoring).Value < 1 Or .Cells(i, posMonitoring).Value > 10 Then
                Sheets("Element_errors").Cells(i, 1) = "err110"
            Else
                Sheets("Element_errors").Cells(i, 1) = "no"
            End If
        Next i


      End If
    End With
End Sub

Upvotes: 0

Views: 43

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

this will iterate each pattern and output on column for each:

Sub Element110_error()
    Dim zelle As Range
    Dim i As Long
    Dim posMonitoring As Integer
    Dim j As Long
    Dim intLastRow As Integer
    k = 0
    With Sheets("ICS Table")
        intLastRow = .UsedRange.Rows.Count
        For j = 11 To 81
            Dim fnd As String
            fnd = j & "0_" & Left(j, 1) & "." & Right(j, 1)
            Set zelle = .Cells.Find(fnd, lookat:=xlPart)
            Sheets("Element_errors").Cells(1, j - 10).Value = fnd
            If zelle Is Nothing Then
                For i = 2 To intLastRow
                    Sheets("Element_errors").Cells(i, j - 10).Value = "no data"
                Next i
            Else
                posMonitoring = zelle.Column
                For i = 2 To intLastRow
                    If .Cells(i, posMonitoring).Value < 1 Or .Cells(i, posMonitoring).Value > 10 Then
                        Sheets("Element_errors").Cells(i, j - 10) = "err" & j & "0"
                    Else
                        Sheets("Element_errors").Cells(i, j - 10) = "no"
                    End If
                Next i


            End If
        Next j
    End With
End Sub

Upvotes: 1

Related Questions