PDA

View Full Version : [SOLVED] Code works in Macro, but not in Function



daniel.ex1st
03-29-2014, 02:44 PM
Hello,
I'm trying to create function, where user defines 2 ranges- first for products, second for stores. Code copies products and for each product assigns color and first store. Then it copies same products under first "batch" and assignes them color and second store, and so on.
Btw is there any standard Excel function to do that?

My code in makro1 works fine, but there are defined constant ranges. So Iīve copied code into function Assign, where user defines his own ranges, but it doesnīt work with same ranges ("A1:B101", "D9:D21") as in makro1. It ends with "Error in value". Makro and function are saved in attached file.

Can you help me to fix Assign function, please?

Thanks,
Daniel

Teeroy
03-29-2014, 03:58 PM
Hi Daniel,

My first suggestion is change


Public Function Assign(Product As Range, Store As Range)

to


Public Sub Assign(Product As Range, Store As Range)

A Sub performs an action, a Function returns a value. This should give you the result you want.

There's no ways for a built in function to change the colour of the cell directly, although Conditional Formatting can give you the sense that it does.

GTO
03-29-2014, 05:00 PM
Greetings Daniel,

How are you calling your Function? I believe I only declared the variables, and did not change a bit of coding. This appears to work for me.


Option Explicit

Sub CallIt()
Call Assign(ActiveWorkbook.ActiveSheet.Range("A1:B101"), ActiveWorkbook.ActiveSheet.Range("D9:D21"))
End Sub

Public Function Assign(Product As Range, Store As Range)
Dim MyCell As Range
Dim lastRow As Long
Dim i As Long
Dim j As Long

Set MyCell = ActiveCell
lastRow = 0

' cyklus pre kazdu predajnu
'cycle for each sale
For i = 1 To Store.Rows.Count

Product.Copy Destination:=Cells(MyCell.Row + lastRow, MyCell.Column)

' cyklus priraduje Store a farby k polozkam
' Cycle Store assign a color to items
For j = 1 To Product.Rows.Count

ActiveWorkbook.ActiveSheet.Cells(MyCell.Row - 1 + j + lastRow, MyCell.Column - 1).Value = Store(i)
ActiveWorkbook.ActiveSheet.Cells(MyCell.Row - 1 + j + lastRow, MyCell.Column - 1).Interior.ColorIndex = i + 3
ActiveWorkbook.ActiveSheet.Cells(MyCell.Row - 1 + j + lastRow, MyCell.Column).Interior.ColorIndex = i + 3
ActiveWorkbook.ActiveSheet.Cells(MyCell.Row - 1 + j + lastRow, MyCell.Column + 1).Interior.ColorIndex = i + 3

Next j

lastRow = lastRow + Product.Rows.Count

Next I

ActiveWorkbook.ActiveSheet.Columns.AutoFit

End Function


Mark

daniel.ex1st
03-30-2014, 04:32 AM
Thank you both for replies.
I apologize, I didnīt write it clearly- product range and store range may be located anywhere in workbook. Number of product rows and store rows may vary. Because of that, user has to be able to call function Assign from any cell in workbook by typing "=Assign(Range1, Range2)", where he will specify ranges and press Enter. That will call function Assign with user specified ranges and produce output. The problem is, when I do this, it ends with "Error in value".

Teeroy- it looks like good logic(I see, that Mark used same logic), but it still will not work as I wrote thereinbefore.

Mark- it works fine, I hope this is step forward. But there are still constant parameters(ranges "A1:B101" and "D9:D21") used and I need call function from anywhere from workbook by typing function name and specifying parameters.

Thanks for ideas,
Daniel

Paul_Hossler
03-30-2014, 05:48 AM
any cell in workbook by typing "=Assign(Range1, Range2)",


I believe you're referring to a 'User Defined Function' or UDF

I think you're out of luck. A worksheet function cannot change the formatting of the sheets.

It can only return a value (which can have any of the normal worksheet formatting assigned)

In addition a number of VBA statements cannot be used inside a UDF

It really sounds like you ...

1. want the user to enter the ranges to operate on each time
2. do something to the ranges


Maybe a more flexible sub would work that the user can call from the worksheet would work



Option Explicit
Sub StoresAndProducts()

Dim Product As Range, Store As Range, MyCell As Range
Dim lastrow As Long, i As Long, j As Long

On Error Resume Next
Set Store = Application.InputBox("Select Store cells, blank to exit", "Enter Stores", , , , , , 8)
If Store Is Nothing Then Exit Sub

Set Product = Application.InputBox("Select Product cells, blank to exit", "Enter Products", , , , , , 8)
If Product Is Nothing Then Exit Sub

Set MyCell = Application.InputBox("Select where to put the data, blank to exit", "Enter Destination", , , , , , 8)
If MyCell Is Nothing Then Exit Sub

On Error GoTo 0

Set MyCell = MyCell.Cells(1, 1)


lastrow = 0
For i = 1 To Store.Rows.Count
Product.Copy Destination:=Cells(MyCell.Row + lastrow, MyCell.Column)
For j = 1 To Product.Rows.Count
ActiveWorkbook.ActiveSheet.Cells(MyCell.Row - 1 + j + lastrow, MyCell.Column - 1).Value = Store(i)
ActiveWorkbook.ActiveSheet.Cells(MyCell.Row - 1 + j + lastrow, MyCell.Column - 1).Interior.ColorIndex = i + 3
ActiveWorkbook.ActiveSheet.Cells(MyCell.Row - 1 + j + lastrow, MyCell.Column).Interior.ColorIndex = i + 3
ActiveWorkbook.ActiveSheet.Cells(MyCell.Row - 1 + j + lastrow, MyCell.Column + 1).Interior.ColorIndex = i + 3
Next j
lastrow = lastrow + Product.Rows.Count
Next i
ActiveWorkbook.ActiveSheet.Columns.AutoFit
End Sub




Paul

SamT
03-30-2014, 07:01 AM
I'm trying to create function, where user defines 2 ranges- first for products, second for stores. Code copies products and for each product assigns color and first store. Then it copies same products under first "batch" and assignes them color and second store, and so on.

...

user has to be able to call function Assign from any cell in workbook by typing "=Assign(Range1, Range2)", where he will specify ranges and press Enter.A function cannot do that.

A Macro (Sub Procedure) can. Here's one algorithm:

User runs Macro
Macro tells User
Select cell to place stores and products lists under 'Can select this cell before running macro
Select Stores
Select Products

Macro does its job.

daniel.ex1st
03-31-2014, 04:09 AM
1. want the user to enter the ranges to operate on each time
2. do something to the ranges
...
A worksheet function cannot change the formatting of the sheets.

Paul, SamT -> I didnīt know about that, I just made my part of code by googling and debugging, so I'm little bit smarter now :)
Thanks all, who tried to help; especially to Paul, who made a workaround solution to my problem.

P.S.
Set MyCell = MyCell.Cells(1, 1) seems unnecessary.

Daniel

Paul_Hossler
03-31-2014, 05:45 AM
Set MyCell = MyCell.Cells(1, 1)


It's unnecessary IF you can PROMISE your users are ALWAYS correct

If they accidently selected more that one cell, then they would be applying the results to a group of cells

I was planning to limit the Store and Product to the first column of the users selection and the worksheet's UsedRange



Set Store = Intersect(Store.Columns(1), Store.Parent.UsedRange)



Paul