Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not defined comple error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not defined comple error
Sub ItemsToPrice()
dim Counter as integer For Counter = 1 To 300 'etc Tim "BrianW" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not defined comple error
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not defined comple error
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not defined comple error
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not defined comple error
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable not defined comple error
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding files in a folder. Variable not defined error. | Excel Discussion (Misc queries) | |||
Why error message - not enough system resources to display comple. | Excel Discussion (Misc queries) | |||
Variable not defined compile error | Excel Programming | |||
Compile error, variable not defined | Excel Programming | |||
Comple Error Cannot Find Project or Library | Excel Programming |