Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Excel macro Error 13 Mismatch Error

    I know exactly what the problem but not how to solve it !

    Here's the code section:

    "Do
    Rem 'need to trap any text entries'
    For Ctr = 1 To ActiveCell.Value
    Cells(Row, Ctr + Col + 1).Interior.Color = vbYellow
    Next
    Rem 'move to nexr cell in column'
    Row = Row + 1
    Application.Goto Reference:=Cells(Row, Col)
    Loop Until IsEmpty(ActiveCell)"

    The purpose is to create a row of yellow boxes equal to the value in the ActiveCell. The problem is that every so often the Activecell is containing a text rather than a number, and I don't want to change that text, so I need to be able to test for ActiveCell being text, and set the Value to zero.

    Many thanks
    Rob

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Instead of Activecell.value try val(Activecell.value).

    Steve

  3. #3
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thanks Steve - not too convinced about that as it would surely produce the ascii value of the character?

    No - I went and looked up Val in the VBA Help file for the 2003 version I'm working on, and it throws a wobbly apparantly if it meets a non-numerical character.

    "The Val function stops reading the string at the first character it can't recognize as part of a number. "

    The sheet I'm working on lists a number of items for sale with differing numbers off per item - the yellow squares allow a manual entry on a printed sheet each time an item is sold. This problem arose because the test sheet had a '?' against some items as we didn't know how many we had at that point - obviously there are ways round that in filling in the sheet but that introduces a requirement for instructions which would be limiting.

    I'm beginning to realise that there doesn't seem to be simple capability to meet a requirement that I would have thought quite common - scanning through cells for numbers and either flagging up or ignoring ones which have text in them.

    Rob

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rob,

    Perhaps this will accomplish what you are trying to do?
    Code:
    Option Explicit
    
    Sub Test()
    
       Dim lCtr  As Long
       Dim lCol  As Long
       Dim lRow  As Long
    
       lRow = ActiveCell.Row
       lCol = ActiveCell.Column
       
       Do
         Rem 'need to trap any text entries'
         If IsNumeric(ActiveCell) Then
    
           For lCtr = 1 To ActiveCell.Value
              Cells(lRow, lCtr + lCol + 1).Interior.Color = vbYellow
           Next
    
         End If
    
        Rem 'move to nexr cell in column'
        lRow = lRow + 1
        Cells(lRow, lCol).Select
        
       Loop Until IsEmpty(ActiveCell)
    
    End Sub
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Ahha - that looks like what I want. Very many thanks RG. Why is that how ever hard you look you never find something as simple as that

    As a novice at this type of thing, can I ask what the Option Explicit is; why should the 'Dim' be Long; I take it that the 1 in front of the variables is so that they do not get confused with the VBA terms; and you didn't like my GotoReference instruction ?

    Many thanks

    Rob

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by oldwood View Post
    Ahha - that looks like what I want. Very many thanks RG. Why is that how ever hard you look you never find something as simple as that

    As a novice at this type of thing, can I ask what the Option Explicit is; why should the 'Dim' be Long; I take it that the 1 in front of the variables is so that they do not get confused with the VBA terms; and you didn't like my GotoReference instruction ?

    Many thanks

    Rob
    Rob,

    Questions are always welcome it's the reason for the lounge.

    Option Explicit forces you to DIM {declare} all your variables good programming practice as it will catch mis-spellings which can be hard to find and reek havoc when the code runs but doesn't give the right results.

    I DIMed the Rows & Columns as Long because I didn't know what version of Excel you had and/or how many rows were involved. I also think that the Row property returns a long and this way it doesn't need to be converted thus more efficient.

    That isn't a 1 {one} in front of the variable names it is an l for {long} again another programming convention to help find errors with type mis-matches, in short code like this not a big problem but in larger programs a big help and it is good to get in the habit by always using the conventions. YMMV.

    As for the goto reference you can use what you like as long as it gets the job done. There are multiple ways to accomplish most things and I just happen to prefer less code to read.

    I hope this helps you on your VBA journey. Keep coding and keep asking questions. Remember you never learn anything while you're talking!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    oldwood (2012-01-02)

  8. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi RT

    Many thanks for your help - that works, but there is a BUT which results from my googling on IsNumeric, and it's a bit of a turn up for the books - read this guy's webpage on IsNumeric.

    http://excelsemipro.com/2010/12/functions-isnumber-or-isnumeric-in-excel/

    A
    nd it's true. If I clear one of the 'Quantity' cells, the IsNumeric VBA function stops the process dead - it steps over any cells with text in them but not empty ones. An empty cell is quite possible so I'll have to take that into account in the code.

    I'm comfortable with the guy talking about a Value towards the end of the piece but what does he mean by an Expression ?

    Rob

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rob,

    You're already stopping the loop if you find an empty cell {Loop Until IsEmpty(ActiveCell)} so this shouldn't be a problem. If your loop wasn't on the cell you were using the ISNUMERIC on it could be a problem. I haven't tested this but will assume it is correct till I get a chance.

    Update: Ok, I've tested it and can't get the results as I read them in the linked article. I changed the VBA so the loop is controlled by the last row containing data rather than a blank and it still works even with the accent character & blank as shown in the link. Note I'm using Excel 2003 for this test.

    Update: It also works correctly in Excel 2010.

    Note: the changed VBA is TEST2()
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-01-02 at 16:15. Reason: Update
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi Rg
    Yes -- oops -- I then discovered that too, and had to modify the Loop condition. Should test fully before crying wolf !!

    It does have the effect the article says though -
    if you look at the IF statement I now have (the old one is commented out), it jumps over the FOR loop with an empty cell or one containing some text element. With the 'simpler' IF line, it goes through the FOR loop for an empty cell but takes Value as zero. So the effect is the same but in terms of coding the new IF statement proves the point and is strictly better code (umm I think!)


    Do
    If Not (IsEmpty(ActiveCell)) And IsNumeric(ActiveCell) Then
    'If IsNumeric(ActiveCell) Then
    For Ctr = 1 To ActiveCell.Value
    Cells(Row, Ctr + Col + 1).Interior.Color = vbYellow
    Next
    End If


    ' 'move to nexr cell in column'
    Row = Row + 1
    Cells(Row, Col).Select
    Loop Until IsEmpty(Cells(Row, "A")) 'Column A contains the item name


    End Sub

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rob,

    Well, maybe yes and maybe no it depends on your point of view or maybe the size of your data.
    From an efficiency point, only important with lots of data, you are now performing two tests on every row in your worksheet with data. If there are only a few no biggie but if there are hundreds, thousands, ten of thousands {been there done that} things can really get slow. Personally, if the single IsNumeric test accomplishes the task at hand, i.e. coloring the correct number of adjacent columns, I'd stick with it.

    While we're on the topic of efficiency it's a good idea to put an Application.ScreenUpdating = False line at the beginning of this macro to both keep the screen from flashing it's brains out and also making the code run faster. You can end the code with the same line = True but it is unncecssary since the end of the routine has the same effect but it is clearer to include it. It is also possible to code the routine w/o doing the .selects again making it faster code {see Steve you've finally pounded this into my thick skull }

    Update: You just made me do it! Attached is the worksheet with a 3rd macro Test3Objects() that no longer does any selects and is as efficient as I can make it. I'm sure some of the other Excel guys here can tweak it some more but this at least gives you 3 iterations of the code you can compare and contrast in your VBA journey.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2012-01-02 at 19:44.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If efficiency is required, I would bypass the macro and use conditional formatting. If the values are in Col A with row 1 and the B starts the colored items you can select the range B1 with all the rows and enough columns so that the highest value in A will be colored, then use the conditional formatting formula to:

    =column(b1)-2 < value($a1)
    And set the format as desired

    Steve

  13. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    I don't know the size of the data here but couldn't using conditional formatting lead to a recalcualtion problem? I'm guessing here since I don't know what effect conditional formatting has on recalculation and any insight would be greatly appreciated.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I would not anticipate any issues. What kind of recalculation problem are you suspecting?

    Note that in XL2010, you can make these type of single bar charts within a cell directly with conditional formatting so don't need to use multiple cells]

    Steve

  15. #14
    Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, Scotland
    Posts
    85
    Thanks
    10
    Thanked 0 Times in 0 Posts
    OK, guys - many thanks for all your help and advise. I should have pointed out at the beginning that this is a mickey-mouse project with a data stack of about 30 items; that it is an amateur selling requirement (craft fairs) and was started as a bit of personal entertainment on a wet and miserable afternoon.

    The slightly sad thing about it is that I have learnt a lot from you, and have very much enjoyed that and your company, but the number of times I do a bit of VBA programming is so infrequent that by the next time something comes up I will,have forgotten it all. Mind you at my age that could be next week.

    Again many thanks
    Rob

    Edit - ps forgot the final question ! I wanted to show off my skills (!!!?) to my partner in crime and sent him the spreadsheet, but the macro didn't seem to go with it. Any idea why ?
    Last edited by oldwood; 2012-01-03 at 17:41.

  16. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps you stored it somewhere else?

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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