Variable not defined comple error

[BrianW]

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

 

[Tim Williams]

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

Tim

 

[Dave Peterson]

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]

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

 

[BrianW]

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

 

[Tim Williams]

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
***************************************

 

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

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