variable not defined error

[Joanne]

WinXP Pro, MSOffice 2003
Debbie from this group gave me this sub routine to cause cell in "D" to
= 65 if cell in "C" is > 0 but it doesn't work. So I added the first 4
lines, hoping that would help (from an example from a different sub
routine I had received help on), but now when I F8, I am getting the
variable not defined error and the word 'lists' in row 5 is highlighted.
Lists is the name of the ws I want this to act on
The range will always be rows 15 thru 65 inclusive, so I know that could
be hardcoded in the sub, but my questions are is it a good idea to do
that, and if so, what changes do I need to make to stop the row count?
Would For i - 15 to 65 be the correct syntax?

Sub LaborRate()
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("lists")
For i = 15 To Sheets(lists).Cells(Rows.Count, "C").End(xlUp).Row
If Sheets(lists).Cells(i, "C").Value > 0 Then
Sheets(lists).Cells(i, "D").Value = 65
End If
Next
End Sub

I can't figure out what it wants. I thought that line 5 was setting the
variable - is that different from declaring it. If so, how do I declare
it so that this will work for me?

I sure appreciate your time and help on this - it seems to me a simple
thing and is kind of making me crazy that I cannot get it to go even
though I got some expert help on it.

Thanks
Joanne

 

[JLGWhiz]

You need quote marks around lists in your code lines.

 

[GTVT06]

WinXP Pro, MSOffice 2003
Debbie from this group gave me this sub routine to cause cell in "D" to
= 65 if cell in "C" is > 0 but it doesn't work.  So I added the first 4
lines, hoping that would help (from an example from a different sub
routine I had received help on), but now when I F8, I am getting the
variable not defined error and the word 'lists' in row 5 is highlighted.
Lists is the name of the ws I want this to act on
The range will always be rows 15 thru 65 inclusive, so I know that could
be hardcoded in the sub, but my questions are is it a good idea to do
that, and if so, what changes do I need to make to stop the row count?
Would    For i - 15 to 65     be the correct syntax?

Sub LaborRate()
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("lists")
For i = 15 To Sheets(lists).Cells(Rows.Count, "C").End(xlUp).Row
    If Sheets(lists).Cells(i, "C").Value > 0 Then
        Sheets(lists).Cells(i, "D").Value = 65
    End If
Next
End Sub

I can't figure out what it wants. I thought that line 5 was setting the
variable - is that different from declaring it. If so, how do I declare
it so that this will work for me?

I sure appreciate your time and help on this - it seems to me a simple
thing and is kind of making me crazy that I cannot get it to go even
though I got some expert help on it.

Thanks
Joanne

Joanne,
Try this code.

Sub LaborRate()
'Select Yes in col B if invoice exist with blank value in col B
Dim i As String
Dim cell As Range
For Each cell In Sheets("lists").Range("C15:C65")
If cell.Value > 0 Then
cell.Offset(0, 1).Select
Selection.Value = 65
End If
Next cell

End Sub

 

[GTVT06]

Joanne,
Try this code.

Sub LaborRate()
'Select Yes in col B if invoice exist with blank value in col B
Dim i As String
Dim cell As Range
    For Each cell In Sheets("lists").Range("C15:C65")
    If cell.Value > 0 Then
    cell.Offset(0, 1).Select
    Selection.Value = 65
    End If
    Next cell

End Sub- Hide quoted text -

- Show quoted text -

disregard the comment in the code above. I already had a similar code
to what you needed so I just adjusted it to your needs and forgot to
remove my comment.

 

[m00n]

You need quote marks around lists in your code lines.

Yes.

This way, it's all a little nicer:

Sub LaborRate()
On Error Resume Next
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer
Set wb = ThisWorkbook
Set ws = wb.Sheets("lists")
ws.Activate
For i = 15 To Sheets("lists").Cells(Rows.Count, "C").End(xlUp).Row
If Sheets("lists").Cells(i, "C").Value > 0 Then
Sheets("lists").Cells(i, "D").Value = 65
End If
Next
Set ws = Nothing
Set wb = Nothing
End Sub

 

[GTVT06]

WinXP Pro, MSOffice 2003
Debbie from this group gave me this sub routine to cause cell in "D" to
= 65 if cell in "C" is > 0 but it doesn't work.  So I added the first 4
lines, hoping that would help (from an example from a different sub
routine I had received help on), but now when I F8, I am getting the
variable not defined error and the word 'lists' in row 5 is highlighted.
Lists is the name of the ws I want this to act on
The range will always be rows 15 thru 65 inclusive, so I know that could
be hardcoded in the sub, but my questions are is it a good idea to do
that, and if so, what changes do I need to make to stop the row count?
Would    For i - 15 to 65     be the correct syntax?

Sub LaborRate()
On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("lists")
For i = 15 To Sheets(lists).Cells(Rows.Count, "C").End(xlUp).Row
    If Sheets(lists).Cells(i, "C").Value > 0 Then
        Sheets(lists).Cells(i, "D").Value = 65
    End If
Next
End Sub

I can't figure out what it wants. I thought that line 5 was setting the
variable - is that different from declaring it. If so, how do I declare
it so that this will work for me?

I sure appreciate your time and help on this - it seems to me a simple
thing and is kind of making me crazy that I cannot get it to go even
though I got some expert help on it.

Thanks
Joanne

Cleaned it for you.

Sub LaborRate()
Dim cell As Range
For Each cell In Sheets("lists").Range("C15:C65")
If cell.Value > 0 Then
cell.Offset(0, 1).Select
Selection.Value = 65
End If
Next cell

End Sub

 

[Joanne]

I put this code in the worksheet, it didn't work.
So I put it in a module on the workbook - still nothing
When I do F8, it goes line for line thru

For Each cell etc
If cell.value etc
Then it skips the offset and selection.value lines of code and stops on
If cell.value > 0 and Next Cell lines

What do you think is wrong here?
Thanks for all of your input to everyone - you guys are really great
Joanne

 

[JLGWhiz]

Joanne, this is what your code should look like:

Sub LaborRate()
Dim ws As Worksheet
Dim i As Long
On Error Resume Next
Set ws = Worksheets("lists")
On Error GoTo 0
For i = 15 To Sheets(lists).Cells(Rows.Count, "C").End(xlUp).Row
If ws.Cells(i, 3).Value > 0 Then
ws.Cells(i, 4).Value = 65
End If
Next
End Sub

You should get no errors with this and it should put 65 in col D
if col C value is greater than 0.

 

[GTVT06]

I put this code in the worksheet, it didn't work.
So I put it in a module on the workbook - still nothing
When I do F8, it goes line for line thru

   For Each cell etc
   If cell.value etc
Then it skips the offset and selection.value lines of code and stops on
If cell.value > 0 and Next Cell lines

What do you think is wrong here?
Thanks for all of your input to everyone - you guys are really great
Joanne






- Show quoted text -

The other recommendations are also nice, but they don't end your range
on row 65 like you want it to.
Maybe you weren't on the lists sheet when you ran it? if not, try this
adjustment:

Sub LaborRate()
Dim cell As Range
Sheets("lists").activate
For Each cell In Sheets("lists").Range("C15:C65")
If cell.Value > 0 Then
cell.Offset(0, 1).Select
Selection.Value = 65
End If
Next cell
End Sub