what am i doing wrong please with adding a function

sleepers

Board Regular
Joined
May 26, 2004
Messages
203
Hi have i done this right as i get a #Name? error

i have put the follows code in the macro sheets and then in the cell at the end of the row for angel gear (as displayed above in image) i have put:

=concatmth(B4:T4,"Jul")
_______________________________________________________________________

Function ConcatMth(rng As Range, item As String)
Application.Volatile
For Each ce In rng
If ce <> "" And Sheets(rng.Parent.Name).Cells(1, ce.Column) = item Then
holder = holder & ce & ","
End If
Next ce
ConcatMth = Left(holder, Len(holder) - 1)
End Function

Function ConcatDay(rng As Range, item As String)
Application.Volatile
For Each ce In rng
If ce <> "" And Sheets(rng.Parent.Name).Cells(2, ce.Column) = item Then
holder = holder & ce & ","
End If
Next ce
ConcatDay = Left(holder, Len(holder) - 1)
End Function
______________________________________________________________________

what have i dont wrong please ??

Image00048.jpg
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
What type does the function return? You haven't specified that in your declaration:

sleepers said:
Function ConcatMth(rng As Range, item As String)

And what is "holder"?
 
Upvote 0
From the VBA help under "Function Statement":

To return a value from a function, assign the value to the function name. Any number of such assignments can appear anywhere within the procedure. If no value is assigned to name, the procedure returns a default value: a numeric function returns 0, a string function returns a zero-length string (""), and a Variant function returns Empty. A function that returns an object reference returns Nothing if no object reference is assigned to name (using Set) within the Function.

The following example shows how to assign a return value to a function named BinarySearch. In this case, False is assigned to the name to indicate that some value was not found.

Function BinarySearch(. . .) As Boolean

Boolean would be the return type of this fuction. You assign the value you want to return to the function name, (in this case BinarySearch).
 
Upvote 0
sleepers said:
i have put the follows code in the macro sheets and then in the cell at the end of the row for angel gear (as displayed above in image) i have put:

=concatmth(B4:T4,"Jul")
Custom Functions need to be in a standard code module not a sheet or workbook module. In the VB Editor select Insert | Module from the menu bar and paste the code in the new module.
 
Upvote 0
this is a code that i was shown to make it able to pull together numbers in a row into a single cell based on either a particular day or month etc

the Holder was in the script

DID I DO IT RIGHT?

when i put this script in i right clicked on the excel icon and went to View code i then pasted the code in and saved.

i then went to the cell that i wanted the outcome to show and put in the
=concatmth(B4:T4,"Jul")

i am quite new to macros and functions and need a little hand holding please

can you step me through the actions to put a funtion in please

Michelle
 
Upvote 0
i have got the function to show okay now but when i put in the following i get: #value!

=concatmth(D5:K5,"Jul")

from a range as below

Image00048.jpg
 
Upvote 0
oh oh i worked it out
i was calling in the function the wrong row

it now shows me

,3/07/05,4/07/05,5/07/05, , ,8/07/05,
as the output

can i make it only show the "D" day instead of the full date as a text()
thanks
 
Upvote 0
Change the function to this. If the range will contain dates make the last argument "True" otherwise make it "False" or leave it blank. Formula can be for no dates
Code:
=ConcatMth(B2:I2,"Jul")
or with dates
Code:
=ConcatMth(B3:I3,"Jul",TRUE)
<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> ConcatMth(rng <SPAN style="color:#00007F">As</SPAN> Range, item <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> dte = <SPAN style="color:#00007F">False</SPAN>)
    Application.Volatile
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ce <SPAN style="color:#00007F">In</SPAN> rng
        <SPAN style="color:#00007F">If</SPAN> ce <> "" And Sheets(rng.Parent.Name).Cells(1, ce.Column) = item <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">If</SPAN> dte <SPAN style="color:#00007F">Then</SPAN>
                holder = holder & Format(ce, "ddd") & ","
            <SPAN style="color:#00007F">Else</SPAN>
                holder = holder & ce & ","
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> ce
    ConcatMth = Left(holder, Len(holder) - 1)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top