+ Reply to Thread
Results 1 to 41 of 41

Syntax error on looping through each cell

  1. #1
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Syntax error on looping through each cell

    Please help with the following code. Although there may be some others on the code for now I am stuck with the line

    If Cel("E3:E") = " Stockwell Motors" Then
    Basically, I have many sheets. They all have the same number of rows and column numbers, hence I set up rng as Set rng = ws.Range("A3:R100"). Where I am stuck is when I try to loop throught each each cell in column "E" of ws.Range("A3:R100"). If any cell in each sheet of column "E" has a name " Stockwell Motors", then copy, but I am stuck. i am sure these lines are wrong .

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.


    S
    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Syntax error on looping through each cell

    Hi,

    I added a bit of code your's. You need to specify the cel range.
    OOP's code provided was wrong
    Last edited by Charles; 06-03-2012 at 02:58 PM. Reason: removed code.
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    Try this instead
    Please Login or Register  to view this content.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Charles: I thought, I specified the cell range: The range in each worksheet is Set rng = ws.Range("A3:R100"). What I am trying to do is: To look at each sheet with in the specified range on each cell of column E.

  5. #5
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Syntax error on looping through each cell

    AB33,

    Please check arlu1201 response.
    My code was wrong. Sorry.

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    arlu1201,

    The below line shows a red line. Syntax error
    ws.range("F" & i & ":G" & i").copy worksheets("Totalsums").range("A" & rows.count).end(xlup).offset(1,0)

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    arlu1201,,
    I though Cell is missing from rows.count line

  8. #8
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Syntax error on looping through each cell

    AB33,

    Change to this. To see if works.

    Please Login or Register  to view this content.

  9. #9
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Charles and arlu1201! Thanks again!
    I have a new problem.
    The line : sh.Name = "Totalsums" shows an error.
    Run-time Error 1004: can not rename a sheet to the same name as another sheet.
    I changed the name to different name, but still shows an error.

  10. #10
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Syntax error on looping through each cell

    Hi,

    Delete the sheet and then run the code. Else you need to code to delet it that add it back.

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    I think I am gone crazy. This code is meant to copy the rows which have " Stockwell Motors" on column "E" and paste each line in to a new created sheet called "New". Instead it is creating new blank sheets. I had many syntax errros with the creation of new name, so i have created these two line which may creating the problem. Please help

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    Maybe i can help you with this.

    If i understand you clearly, you are trying to copy all rows containing Stockwell Motors in column E to a sheet called "New".

    Now, if the sheet already exists, you want to clear the contents and load fresh data, right?

    Am i missing anything?

  13. #13
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Arlette! You are spot on!

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    Try this code. I have not tested it.
    Please Login or Register  to view this content.

  15. #15
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Arlette!

    Run time error 1004

    Please Login or Register  to view this content.
    )

  16. #16
    Valued Forum Contributor StevenM's Avatar
    Join Date
    03-23-2008
    Location
    New Lenox, IL USA
    MS-Off Ver
    2007
    Posts
    910

    Re: Syntax error on looping through each cell

    Can you upload a workbook (or a mockup of a workbook)?

  17. #17
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Syntax error on looping through each cell

    shouldn't that just be
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    Out of the filtered range, the user just needs column E & F to be copied, not the entire data.

  19. #19
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Syntax error on looping through each cell

    oh ok then
    Please Login or Register  to view this content.
    or if you wanna use currentregion
    Please Login or Register  to view this content.

  20. #20
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Please see attached a sample file. Please also note that Column B is now becomes what was Column E

    After using Joseph's line, the code does not show an error and creates a new sheet, but does not copy any value.

    I would like to see two things. First, the code works,and secondly, I want to understand the code. The first code (See Below) by Arlette was simple to understand, but it creates new sheets instead of copying the cells. I thought the code is to loop through all sheets in rows 1-100 and column E and look for a string "Stockwell Motors".And then copy certain rows from each sheet in to a new created sheet. Instead the code is creating 100 sheets. I am really lost on this one and keen to understand what is wrong with the code.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  21. #21
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    Do not use my earlier code, instead use this one (which is in post 14)
    Please Login or Register  to view this content.
    Add in the changes suggested by JosephP in post 19 and it should be working fine.

  22. #22
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Arlette
    After using Joseph's line, the code does not show an error and creates a new sheet, but does not copy any value. I used your code, but had an errror and then incorporated Josheph's line. The code did not show an error, but does not copy the rows. Yes, a new file "New" is created with blank values

  23. #23
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    I corrected the code for you.
    Please Login or Register  to view this content.
    You have asked for columns E & F to be copied but in your file there is no data in these 2 columns so the output has only the headers.
    Last edited by arlu1201; 06-05-2012 at 09:43 AM. Reason: Added code line

  24. #24
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Please see attached. Still shows an error
    Attached Files Attached Files

  25. #25
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    I added a line of code to the code in post 23, so that it clears the contents of the sheet "New" for fresh entry.

    I am not seeing any error. Try putting in some test values in columns E & F for rows containing Stock well Motors and you should get the output.

  26. #26
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Auto-filter method of range has failed

    Please Login or Register  to view this content.

  27. #27
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    Is your text "Stock well Motors" written the same way in your file? Is it in column 2? Is your header in row 1?

  28. #28
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    Arlette,
    On the sample file, All I have done is to remove the first three columns, as a result column E becomes column B. I have tried your code on the sample and the actual data with Stock well Motors is in column E. Both show syntax errors.

    Is it because my cells have formulas, so the autofilter is not working? Can only be done on filter bases, or they may be other options, like your first code? I thought your first code was simple to follow and understand.

  29. #29
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Syntax error on looping through each cell

    code should be
    Please Login or Register  to view this content.

  30. #30
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Syntax error on looping through each cell

    Joseph,

    That was the same code i had in post 23, except that the sheet was getting cleared and fresh data included.

  31. #31
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    I have tried the code using the same sample sheet twice. In the first test, I left the sheet as it was-got an error. On the second test, I have changed the format in to my actual data by inserting 3 columns so that Column E becomes the searching column. Please see the attached result.
    May be I need to clarifay again what I am looking to get. If you see on Column E there are many rows with the name stockwell motors. As soon as the code finds that name, it should copy that row(be it from A-R, or F-G) in to the new sheet, but what I am getting is just the two top rows, or headings.
    It is diffcult for me to see why the error is as I do not understand filter coding. I did understand the first code by Arlette, but that code does not work
    Attached Files Attached Files

  32. #32
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Syntax error on looping through each cell

    arlette,
    no it's not quite the same. this code only turns off autofilters for sheets that are not the New sheet (that was causing an error in the code in the workbook since it was trying to clear a filter that wasn't there).

    @AB33
    adding blank columns throws the code off. try
    Please Login or Register  to view this content.

  33. #33
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    JosephP and arlette! It now works. Thank you very much for your time! If I am not demanding your time,I would like to ask you a question as my intention is to not only looking for a solution, but above all a learning process. I do not know how filters work in a code, and would not know if cells have formulas on them. I can easily understand the following code, but wish to know why this code is creating a new 97 blank sheets instead of the other code. How do match this below code with the filter one which is now working?

    Please Login or Register  to view this content.

  34. #34
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Syntax error on looping through each cell

    your code that adds the new sheet is inside the for each...next loop so it creates a new sheet for every existing sheet if its name is not New. this is a good example of why you should not use a blanket on error resume next statement at the top of your code without being sure what you are suppressing.

  35. #35
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    JosephP! You are right! I see what I did. The code is looping through each sheet and see if a sheet name called "new" exist, if it does not exist, create 97 sheets. My original code was in line 3 and arlette amended the code, but I kept getting an error on name, so I messed up with the code, hoping that it might work. Well being a novice can cost you a fortune. But how do you correct the looping code that your new filter works the same as this one? Thanks again!

  36. #36
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Syntax error on looping through each cell

    sorry I'm not sure what your question is? the filter code does the same as that code (only faster) so why not use it?

  37. #37
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    JosephP! I know the filter works, but wondering if the other code could be modified to do the same, as I can easily understand it and do not have to remember the filter lines. It is more a learning idea but do not worry i can use the filter code, and thanks again.

  38. #38
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Syntax error on looping through each cell

    well if it's for learning
    Please Login or Register  to view this content.

  39. #39
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    JoseohP! Thank you very much for your time!

    My main aim of joining this forum is to learn. I am new to VBAs and programming. Since joining this forum, I have been learning quite a lot from you and other exel experts. Although, I know I have a long way to go, I can understand a basic looping code with the help of others.

    I have tried your code twice. If the new sheet exist, it came up with a message "if I wanted to delete it". is this because we have not built up in the code a display alerts as false? And I tried again deleting the sheet named "new", but on both occasions, it did not copy the rows. All it did was to create a sheet name "New"

    Please see attached.
    Attached Files Attached Files

  40. #40
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Syntax error on looping through each cell

    that's because your search string was wrong-" Stockwell Motors" in stead of "Stock well Motors"
    and yeah you need a displayalerts=false line to stop that message - you're learning :-)

  41. #41
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Syntax error on looping through each cell

    JosephP. Well spot! It may be this line of code which has caused us lots of problem in the first place. When I copied the name directly from you code , initially it did not work. I then spotted there was a space before Stock. I then copied it directly from the cell itself. It now works perfectly. As I said I have learned lots from you, Arlette and others, and can not thank you enough. I can easily apply this concept to other problems, it is easy to follow the logic on looping as I have been seeing them lots on this forum. Filering requires lots of name argument to remeber.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1