August 7th, 2013, 07:45 AM
For Next Loops
In the days of Sinclair and Amstrad I became fairly proficient in Basic. With the advent of PCs I became lazy and never bothered with the new languages required. Now at 76 I've become interested again and dug out my VB 2005. Probably a case of old dog and new tricks.
I know to most of you this will be so simple but feel that a if someone would be kind enough to supply the necessary code the fog may thin if not clear.
I'm using excel and trying to build a for next loop for a sheet where column A contains a shopping list of 100+ items. What I'm trying to do is use a cell in another column where I can enter an item and the loop will take me to that item in column A and let me enter details, rather keep scrolling through the list. I know I can use the Find function but that's a bit long winded when tracking several items.
I seem to get halfway there but when I get to the code where if the Value of Column A = the search value I keep getting an error message about a missing object.
Trouble is, I've always found that examples in books never seem to cover the things I'm trying to do.
I've generally found that given a suitable example I can grasp the idea and use it as a basis for experiment.
So if someone out there has the time I'd appreciate the necessary code to carry out this loop you'd be doing me a kindness.
August 7th, 2013, 10:18 AM
If you post what you have we can help more, but I'm guessing one of your columns is empty or null and your code isn't handling it right.
Originally Posted by boblincs
August 7th, 2013, 11:02 AM
This will probably show you how far off the plot I am
Originally Posted by MBirchmeier
Column A has items listed from a3 to a150
For the moment I have chosen cell h3 as my input cell.
What I want to do is enter an item in h3, press enter or a button and be taken to the item in column A.
x = 3
MyCell1 = Range("A3:A150")
MyCell1.Value = A()
mycell2 = Range("H3")
mycell2.Value = H(3)
For x = 3 To 150
If MyCell1.Value = mycell2.Value Then
' FindCell Macro
' Macro recorded 07/08/2013 by Bob
Application.Run "'book experiment.xls'!FindCell"
Laugh quietly please
August 11th, 2013, 11:44 AM
Just wondering, is there a reason why you don't want to accomplish the same thing using the regular Excel search functionality (CTRL-F)?
Are the same items repeated in other parts of the spreadsheet?
August 11th, 2013, 11:52 AM
Just seems less cumbersome.
Originally Posted by argee