| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each
Hello,
In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each
By enclosing "0" with quotes, you're making a string comparison. Try:
If cell.Value < 0 Then In article , "Arwes" wrote: Hello, In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each
Try changing
If cell < "0" Then to If cell < 0 Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Arwes" wrote in message i.nl... Hello, In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each
M. Pearson,
I changed it but no result Arwes "Chip Pearson" schreef in bericht ... Try changing If cell < "0" Then to If cell < 0 Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Arwes" wrote in message i.nl... Hello, In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each
Perhaps you should provided details about what you mean by "does
not work". "Arwes" wrote in message i.nl... M. Pearson, I changed it but no result Arwes "Chip Pearson" schreef in bericht ... Try changing If cell < "0" Then to If cell < 0 Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Arwes" wrote in message i.nl... Hello, In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each
If your depending on the counter your problem is it never gets incremented:
Try Private Sub CommandButton2_Click() Dim myRange As Range Dim cell as Range Dim intCounter As Integer Dim intTeller as Integer intCounter = 0 intTeller = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells if isnumeric(cell) and not isempty(cell) then If cell < 0 Then Debug.Print cell.Value intCounter = intCounter + 1 End If end if intTeller = intTeller + 1 Next cell MsgBox intCounter & " of " & intTeller & _ " cells contained numbers that were not zero" End Sub -- Regards, Tom Ogilvy "Arwes" wrote in message i.nl... M. Pearson, I changed it but no result Arwes "Chip Pearson" schreef in bericht ... Try changing If cell < "0" Then to If cell < 0 Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Arwes" wrote in message i.nl... Hello, In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each
Thanks for that M. Ogilvy, that code works.
Allow me to ask another question about this: If Range("Y5:Y584") are all different Part# and I want to get a list written with all the Part#, starting in Range("AA5").Select and how many times they are <0. Can this also be included is your code? Arwes "Tom Ogilvy" schreef in bericht ... If your depending on the counter your problem is it never gets incremented: Try Private Sub CommandButton2_Click() Dim myRange As Range Dim cell as Range Dim intCounter As Integer Dim intTeller as Integer intCounter = 0 intTeller = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells if isnumeric(cell) and not isempty(cell) then If cell < 0 Then Debug.Print cell.Value intCounter = intCounter + 1 End If end if intTeller = intTeller + 1 Next cell MsgBox intCounter & " of " & intTeller & _ " cells contained numbers that were not zero" End Sub -- Regards, Tom Ogilvy "Arwes" wrote in message i.nl... M. Pearson, I changed it but no result Arwes "Chip Pearson" schreef in bericht ... Try changing If cell < "0" Then to If cell < 0 Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Arwes" wrote in message i.nl... Hello, In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each
Private Sub CommandButton2_Click()
Dim myRange As Range Dim cell as Range Dim intCounter As Integer Dim intTeller as Integer' Dim noDupes as New Collection Dim itm as Variant Dim i as Long intCounter = 0 intTeller = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells if isnumeric(cell) and not isempty(cell) then If cell < 0 Then On Error Resume Next noDupes.Add cell.Value, cell.Text On Error goto 0 Debug.Print cell.Value intCounter = intCounter + 1 End If end if intTeller = intTeller + 1 Next cell i = 0 for each itm in nodupes Range("AA5").offset(i,0).Value = itm Range("AA5").offset(i,1).Value = Application. _ Countif(myRange,itm) i = i + 1 Next MsgBox intCounter & " of " & intTeller & _ " cells contained numbers that were not zero" End Sub -- Regards, Tom Ogilvy "Arwes" wrote in message i.nl... Thanks for that M. Ogilvy, that code works. Allow me to ask another question about this: If Range("Y5:Y584") are all different Part# and I want to get a list written with all the Part#, starting in Range("AA5").Select and how many times they are <0. Can this also be included is your code? Arwes "Tom Ogilvy" schreef in bericht ... If your depending on the counter your problem is it never gets incremented: Try Private Sub CommandButton2_Click() Dim myRange As Range Dim cell as Range Dim intCounter As Integer Dim intTeller as Integer intCounter = 0 intTeller = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells if isnumeric(cell) and not isempty(cell) then If cell < 0 Then Debug.Print cell.Value intCounter = intCounter + 1 End If end if intTeller = intTeller + 1 Next cell MsgBox intCounter & " of " & intTeller & _ " cells contained numbers that were not zero" End Sub -- Regards, Tom Ogilvy "Arwes" wrote in message i.nl... M. Pearson, I changed it but no result Arwes "Chip Pearson" schreef in bericht ... Try changing If cell < "0" Then to If cell < 0 Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Arwes" wrote in message i.nl... Hello, In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
For Each
Thanks M. Ogilvy, it's been a great help!
Regards Arwes "Tom Ogilvy" schreef in bericht ... Private Sub CommandButton2_Click() Dim myRange As Range Dim cell as Range Dim intCounter As Integer Dim intTeller as Integer' Dim noDupes as New Collection Dim itm as Variant Dim i as Long intCounter = 0 intTeller = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells if isnumeric(cell) and not isempty(cell) then If cell < 0 Then On Error Resume Next noDupes.Add cell.Value, cell.Text On Error goto 0 Debug.Print cell.Value intCounter = intCounter + 1 End If end if intTeller = intTeller + 1 Next cell i = 0 for each itm in nodupes Range("AA5").offset(i,0).Value = itm Range("AA5").offset(i,1).Value = Application. _ Countif(myRange,itm) i = i + 1 Next MsgBox intCounter & " of " & intTeller & _ " cells contained numbers that were not zero" End Sub -- Regards, Tom Ogilvy "Arwes" wrote in message i.nl... Thanks for that M. Ogilvy, that code works. Allow me to ask another question about this: If Range("Y5:Y584") are all different Part# and I want to get a list written with all the Part#, starting in Range("AA5").Select and how many times they are <0. Can this also be included is your code? Arwes "Tom Ogilvy" schreef in bericht ... If your depending on the counter your problem is it never gets incremented: Try Private Sub CommandButton2_Click() Dim myRange As Range Dim cell as Range Dim intCounter As Integer Dim intTeller as Integer intCounter = 0 intTeller = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells if isnumeric(cell) and not isempty(cell) then If cell < 0 Then Debug.Print cell.Value intCounter = intCounter + 1 End If end if intTeller = intTeller + 1 Next cell MsgBox intCounter & " of " & intTeller & _ " cells contained numbers that were not zero" End Sub -- Regards, Tom Ogilvy "Arwes" wrote in message i.nl... M. Pearson, I changed it but no result Arwes "Chip Pearson" schreef in bericht ... Try changing If cell < "0" Then to If cell < 0 Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Arwes" wrote in message i.nl... Hello, In a comumn are 580 numbers and they change every day. By clicking the button in my worksheet I want the numbers who are different from zero ans use the following code. For one reason or another this won't work. As a control a have included a counter at the end to see if the code goes all the way to the end. Any ideas. Thanks Arwes Private Sub CommandButton2_Click() Dim myRange As Range Dim intCounter As Integer intCounter = 0 Range("AA5").Select Set myRange = Range("Y5:Y584") For Each cell In myRange.Cells If cell < "0" Then Debug.Print cell.Value End If intTeller = intTeller + 1 Next cell MsgBox intCounter End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
