Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rows to columns in a table or pivot table | Excel Discussion (Misc queries) | |||
how to interchange rows to columns & columns to rows in a table | Excel Discussion (Misc queries) | |||
Loop through columns and then rows | Excel Programming | |||
Addition of rows in table with loop | Excel Programming | |||
How do I delete rows and columns in With With End Loop? | Excel Programming |