+ Post New Thread
Results 1 to 13 of 13
Office Software Thread, Excel custom function help please! in Technical; Hi, I'm trying to create a custom function that will only add certain columns (the ones labeled "sales") from a ...
  1. #1

    Join Date
    Oct 2011
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Excel custom function help please!

    Hi,

    I'm trying to create a custom function that will only add certain columns (the ones labeled "sales") from a certain range. However i keep getting an error message on the first ActiveCell.Offset. Can someone please tell me where the error is and how to correct the function?


    Function Week_Sales()
    v = 0
    Worksheets("Sheet1").Activate
    For i = 1 To 15
    ActiveCell.Offset(-1, -i).Activate
    If ActiveCell.Value = "Sales" Then
    ActiveCell.Offset(1, 0).Activate
    v = v + ActiveCell.Value
    End If
    Next i
    Week_Sales = v
    End Function

    Just to clear things up: my range has 15 cells, Monday - Sunday (each weekday has a column for sales and one for stock). Under Sunday I've inserted a 3rd column with total sales in the current week (Week_Sales), which is where i will be using the function.

    Many Thanks!

  2. #2

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Your entire logic on your offsets is wrong.

    If your data is as such: (How I understood from your above description)

    esxcelexample.JPG

    You'd want to do it like this:

    Code:
    Sub Week_Sales()
    
    Dim Week_Sales As Integer
    Week_Sales = 0
    
    Worksheets("Sheet1").Activate
    ActiveSheet.Range("A2").Select
    
    For i = 1 To 15
    If ActiveCell.Value = "Sales" Then
    ActiveCell.Offset(1, 0).Activate
    Week_Sales = Week_Sales + ActiveCell.Value
    ActiveCell.Offset(-1, 0).Activate
    End If
    ActiveCell.Offset(0, 1).Activate
    Next i
    
    ActiveSheet.Range("O3").Select
    ActiveCell.Value = Week_Sales
    
    End Sub
    It tests if it's Sales, If so moves down 1, and adds the value to the total. Then goes back up one, and moves right. Repeat.

    Steve

  3. #3

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,403
    Thank Post
    109
    Thanked 267 Times in 198 Posts
    Rep Power
    168
    You don't seem to have set the active cell at the start

    Try this
    Code:
    ActiveSheet.Range("XX99").Activate
    Insert your top left cell address instead of XX99

  4. #4

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Quote Originally Posted by CESIL View Post
    You don't seem to have set the active cell at the start

    Try this
    Code:
    ActiveSheet.Range("XX99").Activate
    Insert your top left cell address instead of XX99

    By default you always have "an" activecell, It's whatever your mouse last selected, aka black border.

    The problem is he's looping "minuses" off the edge of the page as it's doing a minus.

    Steve

  5. #5

    Join Date
    Oct 2011
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi Steve,

    Thanks for the quick reply. My excel look just like what you posted.

    The only problem is, and i should have mentioned this before of course, that once i'm done with a week i start a new one right after (to the right).

    So if i set the ActiveSheet.Range("A2").Select then it will always begin on A2..obviously, but i need it to begin on "Monday", whatever cell that is.

    Do you know how i might do that?

  6. #6

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Code:
    Column = 1
    Do While Cells(2, Column) <> ""
    Cells(2, Column).Activate
    Column = Column + 1
    Loop
    Cells(2, (Column - 15)).Activate
    Will scan along Row 2, for the last empty cell. Then backtrack to the last Monday.

    However you'll need to edit the setting Value part too O3 part.

    But test above and see if it's what you want.

    (Replace ActiveSheet.Range("A2").Select with the above)

    Steve

  7. #7

    Join Date
    Oct 2011
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Thanks!

    Unfortunately i get an error in Cells(2, (Column - 15)).Activate

  8. #8

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Quote Originally Posted by stupidusagi View Post
    Thanks!

    Unfortunately i get an error in Cells(2, (Column - 15)).Activate
    I'm going to go on the assumption, Your setup isn't same as mine. Can you post a screenshot of the sheet you're trying it on please, and attach to here so I can see. Easier than going around in circles trying to find it

    Steve

  9. #9

    Join Date
    Oct 2011
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    well....it's true. but I've made the changes to what you suggested accordingly. test.xlsx

    right now it looks like this:

    Sub Week_Sales()

    Dim Week_Sales As Integer
    Week_Sales = 0

    Worksheets("Sheet1").Activate
    Column = 5
    Do While Cells(4, Column) <> ""
    Cells(4, Column).Activate
    Column = Column + 1
    Loop
    Cells(3, (Column - 14)).Activate

    For i = 1 To 15
    If ActiveCell.Value = "Sales" Then
    ActiveCell.Offset(1, 0).Activate
    Week_Sales = Week_Sales + ActiveCell.Value
    ActiveCell.Offset(-1, 0).Activate
    End If
    ActiveCell.Offset(0, 1).Activate
    Next i

    ActiveSheet.Range("S4").Select
    ActiveCell.Value = Week_Sales

    End Sub

  10. #10

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Yeah your edits are causing the error.

    Code:
    Sub Week_Sales()
    
    Dim Week_Sales As Integer
    Week_Sales = 0
    
    Worksheets("Sheet1").Activate
    Column = 5
    Do While Cells(3, Column) <> ""
    Cells(3, Column).Activate
    Column = Column + 1
    Loop
    Cells(3, (Column - 15)).Activate
    
    For i = 1 To 15
    If ActiveCell.Value = "Sales" Then
    ActiveCell.Offset(1, 0).Activate
    Week_Sales = Week_Sales + ActiveCell.Value
    ActiveCell.Offset(-1, 0).Activate
    End If
    ActiveCell.Offset(0, 1).Activate
    Next i
    
    ActiveCell.Offset(1, -1).Activate
    ActiveCell.Value = Week_Sales
    
    End Sub
    Will do it for "the last week", but remember as you have multiple weeks added in, it'll currently add up to 0. If you delete the "sales/restock" past this week, it'll add it up properly.

    So if you're planning to constantly have the extra 4-5 weeks info in there you'll need some date checking.

    Steve

  11. #11

    Join Date
    Oct 2011
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Thank you so much! it's working!

    but about what you were saying, that if i want to keep the extra weeks i need to do date checking. What do you mean with that exactly?

  12. #12

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Quote Originally Posted by stupidusagi View Post
    Thank you so much! it's working!

    but about what you were saying, that if i want to keep the extra weeks i need to do date checking. What do you mean with that exactly?
    Well as you said you want it to always check the latest week, I just made it scan across the row, finding the last empty cell. But as you've filled it in weeks before it'll find the last week aka:

    Code:
    Monday		Tuesday		Wednesday		Thursday		Friday		Saturday		Sunday		
    24/10/2011		25/10/2011		26/10/2011		27/10/2011		28/10/2011		29/10/2011		30/10/2011
    As that's the last week in there.

    The easiest answer is don't prefill the "sales/restock" boxes until it's that week. Then the code above works fine, but if you want it prefilled you'll need to check if it's the actual right dates etc etc

    Steve

  13. #13

    Join Date
    Oct 2011
    Posts
    6
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Thank you very much for your help

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2003] Excel expert required - Help please
    By elsiegee40 in forum Office Software
    Replies: 14
    Last Post: 17th May 2010, 08:23 AM
  2. Help with a custom ADM file please
    By Robot in forum Windows
    Replies: 4
    Last Post: 15th December 2009, 10:01 AM
  3. Web page in open office or Excel help please.
    By MrWestwood in forum Office Software
    Replies: 0
    Last Post: 1st May 2009, 11:34 AM
  4. Custom GPO - Help
    By mloveless in forum Coding
    Replies: 1
    Last Post: 3rd February 2006, 10:19 AM
  5. E Learning Credits - Help Please
    By MuppetQueen in forum Budgets and Expenditure
    Replies: 4
    Last Post: 12th December 2005, 03:10 PM

Thread Information

Users Browsing this Thread

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

Posting Permissions

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