Excel custom function help please!

[stupidusagi]

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!

[Steve21]

Your entire logic on your offsets is wrong.

 

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

 

 

You'd want to do it like this:

 

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

[CESIL]

You don't seem to have set the active cell at the start

 

Try this

ActiveSheet.Range("XX99").Activate

Insert your top left cell address instead of XX99

[Steve21]

You don't seem to have set the active cell at the start

 

Try this

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

[stupidusagi]

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?

[Steve21]

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

[stupidusagi]

Thanks!

 

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

[Steve21]

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

[stupidusagi]

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

[Steve21]

Yeah your edits are causing the error.

 

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

[stupidusagi]

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?

[Steve21]

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:

 

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

[stupidusagi]

Thank you very much for your help