Complete error variable not defined

Quick Sum

New to VBA. Excel 2003
I’m getting a compile error: Variable not defined, in the following Macro.
(General) = Open Explicit.
How do I set the variable for Counter

Sub ItemsToPrice()
For Counter = 1 To 300
Set curCell = Worksheets(«Sheet4»).Cells(Counter, 18)
If Abs(curCell.Value) = 2 Then curCell.Select
Range(«C» & ActiveCell.Row & «:M» & ActiveCell.Row).Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
Range(«R» & ActiveCell.Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(«H» & ActiveCell.Row).Select
Selection.ClearContents
Next Counter
End Sub

Sub ItemsToPrice()
dim Counter as integer
For Counter = 1 To 300
‘etc

Tim

«BrianW» <BrianW@discussions.microsoft.com> wrote in message
news:5CE95B1A-530B-46DD-8830-1BFF2E323CA6@microsoft.com…

> New to VBA. Excel 2003
> I’m getting a compile error: Variable not defined, in the following Macro.
> (General) = Open Explicit.
> How do I set the variable for Counter
>
> Sub ItemsToPrice()
> For Counter = 1 To 300
> Set curCell = Worksheets(«Sheet4»).Cells(Counter, 18)
> If Abs(curCell.Value) = 2 Then curCell.Select
> Range(«C» & ActiveCell.Row & «:M» & ActiveCell.Row).Select
> With Selection.Interior
> .ColorIndex = 37
> .Pattern = xlSolid
> End With
> Selection.Font.ColorIndex = 3
> Selection.Font.Bold = True
> Range(«R» & ActiveCell.Row).Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range(«H» & ActiveCell.Row).Select
> Selection.ClearContents
> Next Counter
> End Sub

You meant to put:
Option Explicit
at the top of your module.

This tells excel that you want to be force to declare your variables:

Dim Counter As Long
dim CurCell as range

But you can do lots of stuff without selecting the ranges. But it’s kind of
difficult to see what you’re doing.

You check that to see if the curcell.value = 2 and select that cell. But the
next line, you select C:M of that row. So selecting that single cell doesn’t
really do anything.

Since I don’t quite understand what you want, this might give you a starting
point—if you want to check a value and do something, maybe you can add it to
this shell:

Option Explicit
Sub ItemsToPrice()
Dim Counter As Long
Dim CurCell As Range

With Worksheets(«Sheet4»)
For Counter = 1 To 300
With .Range(«C» & Counter & «:M» & Counter)
.Interior.ColorIndex = 37
.Interior.Pattern = xlSolid
.Font.ColorIndex = 3
.Font.Bold = True
End With
With .Range(«R» & Counter)
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
.Range(«H» & Counter).ClearContents
Next Counter
End With
End Sub

As written, this just format C1:M300 row by row, converts R1:R300 to values and
cleans up H3:H300.

You can do that without the loop:
Option Explicit
Sub ItemsToPrice()
Dim Counter As Long
Dim CurCell As Range

With Worksheets(«Sheet4»)
With .Range(«C1:M300»)
.Interior.ColorIndex = 37
.Interior.Pattern = xlSolid
.Font.ColorIndex = 3
.Font.Bold = True
End With
With .Range(«R1:r300»)
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
.Range(«H1:H300»).ClearContents
End With
End Sub

BrianW wrote:

>
> New to VBA. Excel 2003
> I’m getting a compile error: Variable not defined, in the following Macro.
> (General) = Open Explicit.
> How do I set the variable for Counter
>
> Sub ItemsToPrice()
> For Counter = 1 To 300
> Set curCell = Worksheets(«Sheet4»).Cells(Counter, 18)
> If Abs(curCell.Value) = 2 Then curCell.Select
> Range(«C» & ActiveCell.Row & «:M» & ActiveCell.Row).Select
> With Selection.Interior
> .ColorIndex = 37
> .Pattern = xlSolid
> End With
> Selection.Font.ColorIndex = 3
> Selection.Font.Bold = True
> Range(«R» & ActiveCell.Row).Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range(«H» & ActiveCell.Row).Select
> Selection.ClearContents
> Next Counter
> End Sub

—

Dave Peterson

Hi Dave and Tim. Thanks for the replies
What I have is a pricing workbook that loads a csv file and price building
products that I measure for clients. If a product has been nominated by the
designer that isnt in the database my vlookup formula returns exlNAerror
value which is converted into a 2 in column R by way of formula. I then
filter all the 2’s, formate the cells in that row pertaining to the
corrosponding 2 from columns C-M. I then F2 F9 the cell with the formula in
column R so that when I clear the error value in column H my client can find
the rows that require special pricing. I hope this helps?

Dave when I run your 1st macro I’m getting a script out of range error.
Tim when I run yours it works but my macro takes a dreadfully long time to
complete. Since I have 2Gb of ram 200 SATA HD ND7800 grahic card and 3200
dual processor it has to be my code. You guys may see a better way now that
you are fulyy in the picture.
Is there a way to limit the search to say the last row rather than
nominating 300. I do have a 3 in column B which indicates the end of data.
Your input is greatly valued

«Dave Peterson» wrote:

> You meant to put:
> Option Explicit
> at the top of your module.
>
> This tells excel that you want to be force to declare your variables:
>
> Dim Counter As Long
> dim CurCell as range
>
> But you can do lots of stuff without selecting the ranges. But it’s kind of
> difficult to see what you’re doing.
>
> You check that to see if the curcell.value = 2 and select that cell. But the
> next line, you select C:M of that row. So selecting that single cell doesn’t
> really do anything.
>
> Since I don’t quite understand what you want, this might give you a starting
> point—if you want to check a value and do something, maybe you can add it to
> this shell:
>
> Option Explicit
> Sub ItemsToPrice()
> Dim Counter As Long
> Dim CurCell As Range
>
> With Worksheets(«Sheet4»)
> For Counter = 1 To 300
> With .Range(«C» & Counter & «:M» & Counter)
> .Interior.ColorIndex = 37
> .Interior.Pattern = xlSolid
> .Font.ColorIndex = 3
> .Font.Bold = True
> End With
> With .Range(«R» & Counter)
> .Copy
> .PasteSpecial Paste:=xlPasteValues, _
> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> End With
> .Range(«H» & Counter).ClearContents
> Next Counter
> End With
> End Sub
>
> As written, this just format C1:M300 row by row, converts R1:R300 to values and
> cleans up H3:H300.
>
> You can do that without the loop:
> Option Explicit
> Sub ItemsToPrice()
> Dim Counter As Long
> Dim CurCell As Range
>
> With Worksheets(«Sheet4»)
> With .Range(«C1:M300»)
> .Interior.ColorIndex = 37
> .Interior.Pattern = xlSolid
> .Font.ColorIndex = 3
> .Font.Bold = True
> End With
> With .Range(«R1:r300»)
> .Copy
> .PasteSpecial Paste:=xlPasteValues, _
> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
> End With
> .Range(«H1:H300»).ClearContents
> End With
> End Sub
>
>
>
> BrianW wrote:
> >
> > New to VBA. Excel 2003
> > I’m getting a compile error: Variable not defined, in the following Macro.
> > (General) = Open Explicit.
> > How do I set the variable for Counter
> >
> > Sub ItemsToPrice()
> > For Counter = 1 To 300
> > Set curCell = Worksheets(«Sheet4»).Cells(Counter, 18)
> > If Abs(curCell.Value) = 2 Then curCell.Select
> > Range(«C» & ActiveCell.Row & «:M» & ActiveCell.Row).Select
> > With Selection.Interior
> > .ColorIndex = 37
> > .Pattern = xlSolid
> > End With
> > Selection.Font.ColorIndex = 3
> > Selection.Font.Bold = True
> > Range(«R» & ActiveCell.Row).Select
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > Range(«H» & ActiveCell.Row).Select
> > Selection.ClearContents
> > Next Counter
> > End Sub
>
> —
>
> Dave Peterson
>

Hi Guy’s
Dave I just ran your second macro and it works fine up to the paste in
column R. Run time error 1004 click on a single cell and paste.
Cheers

Here’s my best guess based on your description

Tim

****************************************
Sub ItemsToPrice()
Dim Counter As Integer
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets(«Sheet4»)

For Counter = 1 To 300
With sht.Rows(Counter)

If Abs(.Cells(18).Value) = 2 Then

With Range(.Cells(3), .Cells(13))
.Interior.ColorIndex = 37
.Interior.Pattern = xlSolid
.Font.ColorIndex = 3
.Font.Bold = True
End With

.Cells(18).Value = .Cells(18).Value
.Cells(8).ClearContents
End If

End With
Next Counter
End Sub
***************************************

«BrianW» <BrianW@discussions.microsoft.com> wrote in message
news:3A3A506E-25F8-484D-AED4-52F25E71574F@microsoft.com…

> Hi Dave and Tim. Thanks for the replies
> What I have is a pricing workbook that loads a csv file and price building
> products that I measure for clients. If a product has been nominated by
> the
> designer that isnt in the database my vlookup formula returns exlNAerror
> value which is converted into a 2 in column R by way of formula. I then
> filter all the 2’s, formate the cells in that row pertaining to the
> corrosponding 2 from columns C-M. I then F2 F9 the cell with the formula
> in
> column R so that when I clear the error value in column H my client can
> find
> the rows that require special pricing. I hope this helps?
>
> Dave when I run your 1st macro I’m getting a script out of range error.
> Tim when I run yours it works but my macro takes a dreadfully long time to
> complete. Since I have 2Gb of ram 200 SATA HD ND7800 grahic card and 3200
> dual processor it has to be my code. You guys may see a better way now
> that
> you are fulyy in the picture.
> Is there a way to limit the search to say the last row rather than
> nominating 300. I do have a 3 in column B which indicates the end of data.
> Your input is greatly valued
>
> «Dave Peterson» wrote:
>
>> You meant to put:
>> Option Explicit
>> at the top of your module.
>>
>> This tells excel that you want to be force to declare your variables:
>>
>> Dim Counter As Long
>> dim CurCell as range
>>
>> But you can do lots of stuff without selecting the ranges. But it’s kind
>> of
>> difficult to see what you’re doing.
>>
>> You check that to see if the curcell.value = 2 and select that cell. But
>> the
>> next line, you select C:M of that row. So selecting that single cell
>> doesn’t
>> really do anything.
>>
>> Since I don’t quite understand what you want, this might give you a
>> starting
>> point—if you want to check a value and do something, maybe you can add
>> it to
>> this shell:
>>
>> Option Explicit
>> Sub ItemsToPrice()
>> Dim Counter As Long
>> Dim CurCell As Range
>>
>> With Worksheets(«Sheet4»)
>> For Counter = 1 To 300
>> With .Range(«C» & Counter & «:M» & Counter)
>> .Interior.ColorIndex = 37
>> .Interior.Pattern = xlSolid
>> .Font.ColorIndex = 3
>> .Font.Bold = True
>> End With
>> With .Range(«R» & Counter)
>> .Copy
>> .PasteSpecial Paste:=xlPasteValues, _
>> Operation:=xlNone, SkipBlanks:=False,
>> Transpose:=False
>> End With
>> .Range(«H» & Counter).ClearContents
>> Next Counter
>> End With
>> End Sub
>>
>> As written, this just format C1:M300 row by row, converts R1:R300 to
>> values and
>> cleans up H3:H300.
>>
>> You can do that without the loop:
>> Option Explicit
>> Sub ItemsToPrice()
>> Dim Counter As Long
>> Dim CurCell As Range
>>
>> With Worksheets(«Sheet4»)
>> With .Range(«C1:M300»)
>> .Interior.ColorIndex = 37
>> .Interior.Pattern = xlSolid
>> .Font.ColorIndex = 3
>> .Font.Bold = True
>> End With
>> With .Range(«R1:r300»)
>> .Copy
>> .PasteSpecial Paste:=xlPasteValues, _
>> Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>> End With
>> .Range(«H1:H300»).ClearContents
>> End With
>> End Sub
>>
>>
>>
>> BrianW wrote:
>> >
>> > New to VBA. Excel 2003
>> > I’m getting a compile error: Variable not defined, in the following
>> > Macro.
>> > (General) = Open Explicit.
>> > How do I set the variable for Counter
>> >
>> > Sub ItemsToPrice()
>> > For Counter = 1 To 300
>> > Set curCell = Worksheets(«Sheet4»).Cells(Counter, 18)
>> > If Abs(curCell.Value) = 2 Then curCell.Select
>> > Range(«C» & ActiveCell.Row & «:M» & ActiveCell.Row).Select
>> > With Selection.Interior
>> > .ColorIndex = 37
>> > .Pattern = xlSolid
>> > End With
>> > Selection.Font.ColorIndex = 3
>> > Selection.Font.Bold = True
>> > Range(«R» & ActiveCell.Row).Select
>> > Selection.Copy
>> > Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
>> > SkipBlanks _
>> > :=False, Transpose:=False
>> > Range(«H» & ActiveCell.Row).Select
>> > Selection.ClearContents
>> > Next Counter
>> > End Sub
>>
>> —
>>
>> Dave Peterson
>>

I don’t see why the copy|paste didn’t work.

If the subscript out of range error occurred on this line:
With Worksheets(«Sheet4»)
then you don’t really have a worksheet named Sheet4. Change it to what you
want.

If you can pick out a column that always has data, you can find the last row in
the column via:

dim LastRow as Long
with worksheets(«sheet4»)
lastrow = .cells(.rows.count,»A»).end(xlup).row
for irow = 1 to lastrow

…..

Fix the worksheet name here, too.

BrianW wrote:

>
> Hi Guy’s
> Dave I just ran your second macro and it works fine up to the paste in
> column R. Run time error 1004 click on a single cell and paste.
> Cheers

—

Dave Peterson

Hi Guy’s
Yip I’ve got it working.
Really appreciated your help
Once again thanks for your help.

«Dave Peterson» wrote:

> I don’t see why the copy|paste didn’t work.
>
> If the subscript out of range error occurred on this line:
> With Worksheets(«Sheet4»)
> then you don’t really have a worksheet named Sheet4. Change it to what you
> want.
>
> If you can pick out a column that always has data, you can find the last row in
> the column via:
>
> dim LastRow as Long
> with worksheets(«sheet4»)
> lastrow = .cells(.rows.count,»A»).end(xlup).row
> for irow = 1 to lastrow
>
> …..
>
> Fix the worksheet name here, too.
>
>
>
> BrianW wrote:
> >
> > Hi Guy’s
> > Dave I just ran your second macro and it works fine up to the paste in
> > column R. Run time error 1004 click on a single cell and paste.
> > Cheers
>
> —
>
> Dave Peterson
>