Excel For Each question from Mr Excel


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

For Each question
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

jcvoth
Board Master


Joined: 23 Feb 2004
Posts: 163

Flag: Blank

Status: Offline

 Reply with quote  

For Each question

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)

Sub discprice()
mytimer = Timer
master = "E:\Sales Accounting\D F I\DFI Master File.xls"
'Workbooks.Open Filename:=(master)
'ActiveWindow.ActivateNext
lastrow = Range("A65536").End(xlUp).Row
cctr = InputBox("What Cost Center should be updated?")
'Set lookuprange = Workbooks("DFI Master File.xls").Worksheets("Price Lists").Range("a2:e9000")
For i = 2 To lastrow

If Range("v" & i).Value <> cctr Then Range("H" & i).Value = 0

Next i
'Workbooks("DFI Master File.xls").Close SaveChanges:=False
MsgBox ("It took " & Timer - mytimer & " seconds to update the discount price for " & cctr & "." & vbCrLf & vbCrLf & "You have " & lastrow - 1 & " records in this file.")
End Sub


Any assistance is greatly appreciated.

-Jarrod

Post Tue Mar 30, 2004 6:09 pm 
 View user's profile Send private message

jmiskey
Board Master
Board Master


Joined: 02 Aug 2002
Posts: 3381

Flag: Usa

Status: Offline

 Reply with quote  

Re: For Each question

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
_________________
Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!

Post Tue Mar 30, 2004 6:11 pm 
 View user's profile Send private message

jcvoth
Board Master


Joined: 23 Feb 2004
Posts: 163

Flag: Blank

Status: Offline

 Reply with quote  

Re: For Each question

Brilliant!

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



Thank you!
-Jarrod

Post Tue Mar 30, 2004 6:14 pm 
 View user's profile Send private message

jmiskey
Board Master
Board Master


Joined: 02 Aug 2002
Posts: 3381

Flag: Usa

Status: Offline

 Reply with quote  

Re: For Each question

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! icon_smile.gif
_________________
Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!

Post Tue Mar 30, 2004 6:20 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.