For Each question

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: For Each question

  1. #1
    Board Regular
    Join Date
    Feb 2004
    Posts
    198
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Feb 2004
    Posts
    198
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For Each question

    Brilliant!

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



    Thank you!
    -Jarrod

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default 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/viewto...ption+explicit

    Good luck and happy Excel-ling!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com