+ Reply to Thread
Results 1 to 7 of 7

If cell in column contains a # add 1 to it

  1. #1
    Registered User
    Join Date
    04-06-2010
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Arrow If cell in column contains a # add 1 to it

    Hey,

    I am having trouble comming up with a macro that if column K has any cells that contain numbers add 1 to that cell. Example if K16=8 after the macro K16=9. but i have many cells with numbers in column K and with text.

    Thanks!
    Last edited by Dodge_Caravan23; 04-07-2010 at 01:55 PM.

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: If cell in column contains a # add 1 to it

    You can add a number to a range of cells without using VBA.
    In a empty cells, type the number you want to add, copy that cell then select the range of cells which are to be increased and choose Paste Special > Add option.

    Post back if you really need a VBA solution.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: If cell in column contains a # add 1 to it

    Use the IsNumeric function:

    Please Login or Register  to view this content.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  4. #4
    Registered User
    Join Date
    04-06-2010
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: If cell in column contains a # add 1 to it

    Hey guys,

    Thanks palmetto but i need a macro since I have over a thousand lines. THanks Dave the macro works except all blank cells get +1 also. If i paste a space in all the blank cells it works fine.

    after the "then" in the formula can another if be put for "if numeric value >0 then +1"?

    Thanks again!

  5. #5
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: If cell in column contains a # add 1 to it

    The variable should be declared. Here is amended code per your request.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: If cell in column contains a # add 1 to it

    Or you could change this line in Dave's macro

    If IsNumeric(ActiveSheet.Cells(i, "K")) Then
    to

    If IsNumeric(ActiveSheet.Cells(i, “K”)) And ActiveSheet.Cells(i, “K”).Value > 0 Then
    HTH

    Alf

  7. #7
    Registered User
    Join Date
    04-06-2010
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: If cell in column contains a # add 1 to it

    Thanks guys both are the same and both work!!

+ 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