Loop through table of columns and rows
 

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

Loop through table of columns and rows
 

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


" wrote:

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

Loop through table of columns and rows
 

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

" wrote:

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

Loop through table of columns and rows
 

On Jan 20, 4:58*pm, JLGWhiz wrote:
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



" wrote:
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- Hide quoted text -

- 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

Loop through table of columns and rows
 

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


"JLGWhiz" wrote:

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


" wrote:

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

Loop through table of columns and rows
 

On Jan 20, 5:08*pm, FSt1 wrote:
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



" wrote:
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- Hide quoted text -

- 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

Loop through table of columns and rows
 

On Jan 20, 5:12*pm, JLGWhiz wrote:
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



"JLGWhiz" wrote:
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

" wrote:

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- Hide quoted text -

- 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

Loop through table of columns and rows
 

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

regards
FSt1

" wrote:

On Jan 20, 5:08 pm, FSt1 wrote:
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



" wrote:
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- Hide quoted text -

- 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

Loop through table of columns and rows
 

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

" wrote:

On Jan 20, 5:08 pm, FSt1 wrote:
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



" wrote:
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- Hide quoted text -

- 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

Loop through table of columns and rows
 

On Jan 20, 5:57*pm, JLGWhiz wrote:
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



" wrote:
On Jan 20, 5:08 pm, FSt1 wrote:
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

" wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- 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