Loop through table of columns and rows

[wfgfreedom]

Iam trying to create a loop that searches through a table of columns
and rows to zero and negative values. My columns portion of the code
is causing an error. How should I change the code to have it work
properly.

Sub Search()
LastColumn = "R"
NewColumnCount = "F"
For ColumnCount = NewColumnCount To LastColumn
LastRow = 54
NewRowCount = 38
For RowCount = NewRowCount To LastRow
If Range(ColumnCount & RowCount) < 0 Then
Range(ColumnCount & RowCount) = 0
NewRowCount = NewRowCount + 2
End If
Next RowCount
NewColumnCount = NewColumnCount + 2
Next ColumnCount
End Sub

Frank

 

[JLGWhiz]

Your code modified:

For ColumnCount = NewColumnCount To LastColumn
LastRw = 54
NewRowCount = 38
For RowCount = NewRowCount To LastRw
If Cells(RowCount, ColumnCount) < 0 Then
Cells(RowCount, ColumnCount) = 0
NewRowCount = NewRowCount + 2
End If
Next RowCount
NewColumnCount = NewColumnCount + 2
Next ColumnCount
End Sub


'This could be written as:

Sub SearchAlt()
For Each c In Range("F38:R54")
If c.Value < 0 Then
c.Value = 0
End If
Next
End Sub

 

[FSt1]

hi
your code is full of syntax errors. What are you trying to do?
I'm guessing search range F35 to R54 for negative numbers?

regards
FSt1

 

[wfgfreedom]

Your code modified:

For ColumnCount = NewColumnCount To LastColumn
LastRw = 54
NewRowCount = 38
For RowCount = NewRowCount To LastRw
   If Cells(RowCount, ColumnCount) < 0 Then
     Cells(RowCount, ColumnCount) = 0
      NewRowCount = NewRowCount + 2
   End If
Next RowCount
NewColumnCount = NewColumnCount + 2
Next ColumnCount
End Sub

'This could be written as:

Sub SearchAlt()
    For Each c In Range("F38:R54")
      If c.Value < 0 Then
        c.Value = 0
      End If
    Next
End Sub







- Show quoted text -

I tried your suggested code and realized that it loops through every
cell in the table. I need to loop through every second row of every
second column. Is there an easy way to do this?

Thanks,
Frank

 

[JLGWhiz]

Part of your code got clipped on the paste, here is the complete modified
version:

Sub Search()
LastColumn = 18
NewColumnCount = 6
For ColumnCount = NewColumnCount To LastColumn
LastRw = 54
NewRowCount = 38
For RowCount = NewRowCount To LastRw
If Cells(RowCount, ColumnCount) < 0 Then
Cells(RowCount, ColumnCount) = 0
NewRowCount = NewRowCount + 2
End If
Next RowCount
NewColumnCount = NewColumnCount + 2
Next ColumnCount
End Sub

 

[wfgfreedom]

hi
your code is full of syntax errors. What are you trying to do?
I'm guessing search range F35 to R54 for negative numbers?

regards
FSt1







- Show quoted text -

I realize my code has errors, since I am a novice, and need help. I am
trying to search through a table from F38 through R54 and zero any
value that is below zero. I need to search only through every send
row, of every second column through this table.

Frank

 

[wfgfreedom]

Part of your code got clipped on the paste, here is the complete modified
version:

Sub Search()
LastColumn = 18
NewColumnCount = 6
For ColumnCount = NewColumnCount To LastColumn
LastRw = 54
NewRowCount = 38
For RowCount = NewRowCount To LastRw
   If Cells(RowCount, ColumnCount) < 0 Then
     Cells(RowCount, ColumnCount) = 0
      NewRowCount = NewRowCount + 2
   End If
Next RowCount
NewColumnCount = NewColumnCount + 2
Next ColumnCount
End Sub









- Show quoted text -

Thanks for the updated code. For some reason, I still looped through
every row, so I removed the code to add 2, and included step 2 in the
For statement. That finally worked.

Thanks for your help.

Frank

 

[FSt1]

hi
JLGWhiz has viable solutions. that is basicly what i was going to suggest.

regards
FSt1

 

[JLGWhiz]

This code does what you described that you
wanted.

If you use the starting point of 6 for j and
38 for i then changes occur in columns f, h,
j, l, n, p and r for rows 38, 40, 42, 44, 46,
48, 50, 52 and 54. So you can adjust the rows
to change by adusting the starting rows and columns.

Sub makeZero()
For i = 38 To 54 Step 2
For j = 6 To 18 Step 2
If Cells(i, j) < 0 Then
Cells(i, j) = 0
End If
Next j
Next i
End Sub

 

[wfgfreedom]

This code does what you described that you
wanted.

If you use the starting point of 6 for j and
38 for i then changes occur in columns f, h,
j, l, n, p and r for rows 38, 40, 42, 44, 46,
48, 50, 52 and 54. So you can adjust the rows
to change by adusting the starting rows and columns.

Sub makeZero()
For i = 38 To 54 Step 2
  For j = 6 To 18 Step 2
    If Cells(i, j) < 0 Then
      Cells(i, j) = 0
    End If
  Next j
Next i
End Sub







- Show quoted text -

Thanks for your help. I got this thing working fine. There is always
many ways of doing what you want, some are less complicated than
others. The simple solution is always preferred.

Cheers,
Frank