For Each question

[jcvoth]

Greetings all!

I am having a problem with a loop and I hope someone can help.

Essentially, my goal is this. When a command button is clicked, the user is prompted to enter a 3 digit code. When that code is entered, the macro will search through the list of data and put a 1 in a cell for the row that contains the code and do nothing to the rows that contain a different code. I am close, but for some reason, the value does not seem to equal the information I entered in the inputbox. My theory is that I need to format the result from the inputbox to equal the format in the row data, but I cannot figure out how to do it.

Below is the code I have so far. (I have commented out the pieces that are not relevant to this portion of the testing)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> discprice()
mytimer = Timer
master = "E:\Sales Accounting\D F I\DFI Master File.xls"
<SPAN style="color:#007F00">'Workbooks.Open Filename:=(master)</SPAN>
<SPAN style="color:#007F00">'ActiveWindow.ActivateNext</SPAN>
lastrow = Range("A65536").End(xlUp).Row
cctr = InputBox("What Cost Center should be updated?")
<SPAN style="color:#007F00">'Set lookuprange = Workbooks("DFI Master File.xls").Worksheets("Price Lists").Range("a2:e9000")</SPAN>
<SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> lastrow

<SPAN style="color:#00007F">If</SPAN> Range("v" & i).Value <> cctr <SPAN style="color:#00007F">Then</SPAN> Range("H" & i).Value = 0

<SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#007F00">'Workbooks("DFI Master File.xls").Close SaveChanges:=False</SPAN>
MsgBox ("It took " & Timer - mytimer & " seconds to update the discount price for " & cctr & "." & vbCrLf & vbCrLf & "You have " & lastrow - 1 & " records in this file.")
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Any assistance is greatly appreciated.

-Jarrod

 

[Joe4]

Try declaring your variables at the beginning of your code (which is something you should be doing anyway!).

i.e.

Dim cctr as String

or

Dim cctr as Integer

 

[jcvoth]

Brilliant!

Although I am a little embarassed it was something so simple as a variable declaration...



Thank you!
-Jarrod

 

[Joe4]

As you get more into programming, you may want to "force" yourself to declare all variables by turning on the "Option Explicit" command. It may seem like a nuisance in the beginning, but most experienced programmers will tell you that it saves a lot of trouble when trying to debug code (especially if you have a lot of code)!

For a good write-up on the benefits of this, check out Tom Urtis's response in this thread: http://www.mrexcel.com/board2/viewtopic.php?t=51854&highlight=option+explicit

Good luck and happy Excel-ling!