VBA Simple question

[Guest]

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!

 

[Guest]

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?

 

[PCLIVE]

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

 

[JE McGimpsey]

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.

 

[Guest]

I am received the error "1004" Method 'Range' of object '_Global' failed.

I tried both suggestions, neither changed this error.

 

[Gary Keramidas]

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 Keramidas]

meant jem, not jim, sorry

--


Gary

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

 

[George Nicholson]

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,