Thread: For Next Loops

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    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.
  2. #2
  3. I <3 ASCII
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2003
    Posts
    2,399
    Rep Power
    1232
    Originally Posted by boblincs
    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.
    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.

    -MBirchmeier
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by MBirchmeier
    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.

    -MBirchmeier
    This will probably show you how far off the plot I am
    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.
    Sub selectCell()

    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
    Select mycell1(x)


    End If
    Next x

    ' FindCell Macro
    ' Macro recorded 07/08/2013 by Bob
    ''
    Range("A3:A150").Select
    Application.Run "'book experiment.xls'!FindCell"
    End Sub
    Laugh quietly please
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    14
    Rep Power
    0
    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?

    Cheers,

    argee
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by argee
    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?

    Cheers,

    argee
    Just seems less cumbersome.

IMN logo majestic logo threadwatch logo seochat tools logo