Reputation: 19
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
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