| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Simple question
I am looking to automate a cell comparison.
The code I have is as follows: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And Cells("b" & xrow).Value < Cells("b" & xrow).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I want to search through 10000 cells in column B and if the value is not 0, and not equal to the previous cell, then enter "33". The figures are arbitrary, but this is the task I need completed. Thanks for the help! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Simple question
I apologize, the code should be shown as:
Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And range("b" & xrow).Value < range("b" & xrow - 1).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I feel that the range("b" & xrow - 1).value is causing the problem. How should I write it? "Shane" wrote: I am looking to automate a cell comparison. The code I have is as follows: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And Cells("b" & xrow).Value < Cells("b" & xrow).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I want to search through 10000 cells in column B and if the value is not 0, and not equal to the previous cell, then enter "33". The figures are arbitrary, but this is the task I need completed. Thanks for the help! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Simple question
Shot in the dark. Try parenthesis around your xrow - 1.
If Range("b" & xrow).Value < 0 And range("b" & xrow).Value < range("b" & (xrow - 1)).Value Then HTH, Paul "Shane" wrote in message ... I apologize, the code should be shown as: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And range("b" & xrow).Value < range("b" & xrow - 1).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I feel that the range("b" & xrow - 1).value is causing the problem. How should I write it? "Shane" wrote: I am looking to automate a cell comparison. The code I have is as follows: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And Cells("b" & xrow).Value < Cells("b" & xrow).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I want to search through 10000 cells in column B and if the value is not 0, and not equal to the previous cell, then enter "33". The figures are arbitrary, but this is the task I need completed. Thanks for the help! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Simple question
By looping xrow from 1 to 10000, the first iteration will error since
there is no Range("B0"). You might be able to use For xrow = 2 to 10000 FYI, you don't gain anything by defining xrow as an Integer rather than a Long (VBA uses longs internally), and if you ever use more than 32767 rows, you'll get an error. In article , "Shane" wrote: I apologize, the code should be shown as: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And range("b" & xrow).Value < range("b" & xrow - 1).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I feel that the range("b" & xrow - 1).value is causing the problem. How should I write it? "Shane" wrote: I am looking to automate a cell comparison. The code I have is as follows: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And Cells("b" & xrow).Value < Cells("b" & xrow).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I want to search through 10000 cells in column B and if the value is not 0, and not equal to the previous cell, then enter "33". The figures are arbitrary, but this is the task I need completed. Thanks for the help! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Simple question
I am received the error "1004" Method 'Range' of object '_Global' failed.
I tried both suggestions, neither changed this error. "JE McGimpsey" wrote: By looping xrow from 1 to 10000, the first iteration will error since there is no Range("B0"). You might be able to use For xrow = 2 to 10000 FYI, you don't gain anything by defining xrow as an Integer rather than a Long (VBA uses longs internally), and if you ever use more than 32767 rows, you'll get an error. In article , "Shane" wrote: I apologize, the code should be shown as: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And range("b" & xrow).Value < range("b" & xrow - 1).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I feel that the range("b" & xrow - 1).value is causing the problem. How should I write it? "Shane" wrote: I am looking to automate a cell comparison. The code I have is as follows: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And Cells("b" & xrow).Value < Cells("b" & xrow).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I want to search through 10000 cells in column B and if the value is not 0, and not equal to the previous cell, then enter "33". The figures are arbitrary, but this is the task I need completed. Thanks for the help! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Simple question
this works for me, you may have to adjust rows as jim stated
Public Sub assign2() Dim ws As Worksheets Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer Dim Sh As Worksheet node = 1 Set Sh = Worksheets("sheet3") For xrow = 2 To 10000 If Sh.Range("b" & xrow).Value < 0 And Sh.Range("b" & xrow).Value _ < Sh.Range("b" & xrow - 1).Value Then Sh.Range("a" & xrow).Value = 33 End If Next End Sub -- Gary "Shane" wrote in message ... I am received the error "1004" Method 'Range' of object '_Global' failed. I tried both suggestions, neither changed this error. "JE McGimpsey" wrote: By looping xrow from 1 to 10000, the first iteration will error since there is no Range("B0"). You might be able to use For xrow = 2 to 10000 FYI, you don't gain anything by defining xrow as an Integer rather than a Long (VBA uses longs internally), and if you ever use more than 32767 rows, you'll get an error. In article , "Shane" wrote: I apologize, the code should be shown as: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And range("b" & xrow).Value < range("b" & xrow - 1).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I feel that the range("b" & xrow - 1).value is causing the problem. How should I write it? "Shane" wrote: I am looking to automate a cell comparison. The code I have is as follows: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And Cells("b" & xrow).Value < Cells("b" & xrow).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I want to search through 10000 cells in column B and if the value is not 0, and not equal to the previous cell, then enter "33". The figures are arbitrary, but this is the task I need completed. Thanks for the help! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Simple question
meant jem, not jim, sorry
-- Gary "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... this works for me, you may have to adjust rows as jim stated Public Sub assign2() Dim ws As Worksheets Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer Dim Sh As Worksheet node = 1 Set Sh = Worksheets("sheet3") For xrow = 2 To 10000 If Sh.Range("b" & xrow).Value < 0 And Sh.Range("b" & xrow).Value _ < Sh.Range("b" & xrow - 1).Value Then Sh.Range("a" & xrow).Value = 33 End If Next End Sub -- Gary "Shane" wrote in message ... I am received the error "1004" Method 'Range' of object '_Global' failed. I tried both suggestions, neither changed this error. "JE McGimpsey" wrote: By looping xrow from 1 to 10000, the first iteration will error since there is no Range("B0"). You might be able to use For xrow = 2 to 10000 FYI, you don't gain anything by defining xrow as an Integer rather than a Long (VBA uses longs internally), and if you ever use more than 32767 rows, you'll get an error. In article , "Shane" wrote: I apologize, the code should be shown as: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And range("b" & xrow).Value < range("b" & xrow - 1).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I feel that the range("b" & xrow - 1).value is causing the problem. How should I write it? "Shane" wrote: I am looking to automate a cell comparison. The code I have is as follows: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And Cells("b" & xrow).Value < Cells("b" & xrow).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I want to search through 10000 cells in column B and if the value is not 0, and not equal to the previous cell, then enter "33". The figures are arbitrary, but this is the task I need completed. Thanks for the help! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Simple question
I am received the error "1004" Method 'Range' of object '_Global' failed.
I find that this is almost always caused by a missing workbook or worksheet reference: "Range(A1)" vs "Worksheets("Sheet1").Range(A1)". Range, Cells, Columns, Rows, etc. are all properties of multiple objects. When you don't specify a "parent" object for one of those, Excel can *sometimes* guess what you mean. When it can't, you get a 1004 error (and whether a piece of code does or does not cause an error can even seem to change from day to day). Avoid making assumptions to avoid 1004's. (This has been tested) Dim wks as Worksheet Dim node As Integer Dim xrow As Integer Set wks = ThisWorkbook.Worksheets("Sheet3") node = 1 With wks For xrow = 2 To 10000 If .Range("b" & xrow) < 0 Then If .Range("b" & xrow) < .Range("b" & xrow - 1) Then .Range("a" & xrow) = 33 End If End If Next xrow End With Notes: 1) Note the addition of a dot/period before each occurance of Range within the "With...End With" stucture. This associates Range with wks and should eliminate your 1004's 2) If performance is an issue, you might consider changing ".Range("b" & xrow)" to ".Cells(xrow,2).", etc. Concatenation (using &) does add a bit of processing time. Probably not material here. 3) I assume node is doing something in code you didn't share with us, 'cause it certainly ain't doing anything here <g HTH, -- George Nicholson Remove 'Junk' from return address. "Shane" wrote in message ... I am received the error "1004" Method 'Range' of object '_Global' failed. I tried both suggestions, neither changed this error. "JE McGimpsey" wrote: By looping xrow from 1 to 10000, the first iteration will error since there is no Range("B0"). You might be able to use For xrow = 2 to 10000 FYI, you don't gain anything by defining xrow as an Integer rather than a Long (VBA uses longs internally), and if you ever use more than 32767 rows, you'll get an error. In article , "Shane" wrote: I apologize, the code should be shown as: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And range("b" & xrow).Value < range("b" & xrow - 1).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I feel that the range("b" & xrow - 1).value is causing the problem. How should I write it? "Shane" wrote: I am looking to automate a cell comparison. The code I have is as follows: Public Sub assign() Worksheets("Sheet3").Activate Dim node As Integer Dim xrow As Integer node = 1 For xrow = 1 To 10000 If Range("b" & xrow).Value < 0 And Cells("b" & xrow).Value < Cells("b" & xrow).Value Then Range("a" & xrow).Value = 33 End If Next End Sub I want to search through 10000 cells in column B and if the value is not 0, and not equal to the previous cell, then enter "33". The figures are arbitrary, but this is the task I need completed. Thanks for the help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
| Simple Question | Excel Discussion (Misc queries) | |||
| Simple Question | Excel Programming | |||
| Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
| simple question, hopefully a simple answer! | Excel Programming | |||
Linear Mode
