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