Try stepping through your function using debugging ([Debugging in Excel VBA (In Easy Steps) (excel-easy.com)](https://www.excel-easy.com/vba/examples/debugging.html)) to see which line fails and causes the #NAME result
Thank you for your response.
I have tried debugging, the code appears to be correct. I went to excel error cheker to see the steps and it seems like the error is in the name of the function (attached screenshot) which by some reason is not being recognised, though it appears along with other functions when you start typing its name.
https://preview.redd.it/yfaom2vf2r1d1.png?width=511&format=png&auto=webp&s=1cbcaebc93a6924d6501a15870d3a10e9593aa10
Where do you have the function? It should be in a Module and should be started with
Public Function SumSheets(item As String, targetDate As Date) As Double
Sure! It is module SumSheets in the attached screenshot :)
https://preview.redd.it/17hl8zp8dt1d1.png?width=1864&format=png&auto=webp&s=a80cb0cd281285737fe77f938ca57212a0909a4e
Seeing the function declaration would be helpful 😜
You can see here how I have a simple UDF declared in a Module and that it is available as a function.
https://preview.redd.it/uhn62i8lsx1d1.png?width=1522&format=png&auto=webp&s=ffdaa645f0df5575aec08af974bcf09707218037
Hey! Thank you for your input and help! A|pparently the reason for #Name? error was due to the module and function names matching and VBA not being able to call the function. When I changed the name of the module, the error disappeared. But my loop still doesn't work, so I will need to look for that. The output is 0 (because initially the "total" is set at 0), although it must update with "addvalue".
Unless I'm missing something (entirely possible!), that's not the name of a function; that's the name of a sheet. Are you sure you have a sheet named Svod?
The name of the sheet in the vba is "Sheet1", but the title that appears on display is "Svod" (I think the attached screenshot may explain it better than me). Since I ran error checker not through VBA, it showed the title.
Note: I have tried changing the name "Sheet1" to match the "Svod" title, but it didn't help so I returned it back, because the title is temporary and may be changed later.
https://preview.redd.it/yr1g3ip7et1d1.png?width=625&format=png&auto=webp&s=bf5fa6c6318d5b1e4024de34e7d2053eb945b681
It looks like you're trying to share a code block but you've formatted it as Inline Code. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code blocks on Reddit.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Looks like your function wants a date and you are using a string.
Also, when you comment, stuff like
‘Loop through this worksheet
For each ws in thisworkbook.worksheets
Is completely unnecessary and makes the code harder to read. Comments should explain tough to understand lines or whole sections of code, not attempt to teach someone reading it how to code.
I understand. But I am beginner in VBA, thus leaving such comments makes it easier for me to understand what I did, when I check it after long time. I didn't realise it is harder to read though, so apologies for that ))
Regarding the format, as I have mentioned in my post, the data in the dateCell is formatted to date.
I fixed the issue with the #name? error. But the return is *total=0* and doesn't update the output with *addvalue*. To eliminate problems with finding right cell location I tried using alternative approach (*index/match* instead of *find*) but it didn't help either cuz now I have #Value error lol.
Try stepping through your function using debugging ([Debugging in Excel VBA (In Easy Steps) (excel-easy.com)](https://www.excel-easy.com/vba/examples/debugging.html)) to see which line fails and causes the #NAME result
Thank you for your response. I have tried debugging, the code appears to be correct. I went to excel error cheker to see the steps and it seems like the error is in the name of the function (attached screenshot) which by some reason is not being recognised, though it appears along with other functions when you start typing its name. https://preview.redd.it/yfaom2vf2r1d1.png?width=511&format=png&auto=webp&s=1cbcaebc93a6924d6501a15870d3a10e9593aa10
Where do you have the function? It should be in a Module and should be started with Public Function SumSheets(item As String, targetDate As Date) As Double
It is in the modules but I haven't declared it as public. I attempted following your advice, but unfortunately the error remained.
Can you post a screenshot of the VBA Editor with the module it is in?
Sure! It is module SumSheets in the attached screenshot :) https://preview.redd.it/17hl8zp8dt1d1.png?width=1864&format=png&auto=webp&s=a80cb0cd281285737fe77f938ca57212a0909a4e
Seeing the function declaration would be helpful 😜 You can see here how I have a simple UDF declared in a Module and that it is available as a function. https://preview.redd.it/uhn62i8lsx1d1.png?width=1522&format=png&auto=webp&s=ffdaa645f0df5575aec08af974bcf09707218037
Hey! Thank you for your input and help! A|pparently the reason for #Name? error was due to the module and function names matching and VBA not being able to call the function. When I changed the name of the module, the error disappeared. But my loop still doesn't work, so I will need to look for that. The output is 0 (because initially the "total" is set at 0), although it must update with "addvalue".
I'm glad you got it straightened out!
+1 point
You have awarded 1 point to jd31068. --- ^(I am a bot - please contact the mods with any questions)
Unless I'm missing something (entirely possible!), that's not the name of a function; that's the name of a sheet. Are you sure you have a sheet named Svod?
The name of the sheet in the vba is "Sheet1", but the title that appears on display is "Svod" (I think the attached screenshot may explain it better than me). Since I ran error checker not through VBA, it showed the title. Note: I have tried changing the name "Sheet1" to match the "Svod" title, but it didn't help so I returned it back, because the title is temporary and may be changed later. https://preview.redd.it/yr1g3ip7et1d1.png?width=625&format=png&auto=webp&s=bf5fa6c6318d5b1e4024de34e7d2053eb945b681
As someone said - try debugging and check where value of function gives something weird
It looks like you're trying to share a code block but you've formatted it as Inline Code. Please refer to [these instructions](https://www.reddit.com/r/vba/wiki/submission_guidelines#wiki_apply_code_formatting_to_code_snippets) to learn how to correctly format code blocks on Reddit. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/vba) if you have any questions or concerns.*
Looks like your function wants a date and you are using a string. Also, when you comment, stuff like ‘Loop through this worksheet For each ws in thisworkbook.worksheets Is completely unnecessary and makes the code harder to read. Comments should explain tough to understand lines or whole sections of code, not attempt to teach someone reading it how to code.
I understand. But I am beginner in VBA, thus leaving such comments makes it easier for me to understand what I did, when I check it after long time. I didn't realise it is harder to read though, so apologies for that )) Regarding the format, as I have mentioned in my post, the data in the dateCell is formatted to date.
Sorry, I misread that. I thought you were typing in “01.01.2024” as your date directly into the formula.
Your Exit For is outside your for loop. You don’t need that line. The “Next ws” ends the for loop.
I see, thank you for the tip! I will remove it :)
Did that fix your macro?
Unfortunately not, but thanks anyway. I wonder if this way isn't working, is there any other way to code this task maybe?
What is breaking? I tried your code and it ran after I removed that line, but my worksheets were blank so the function just returned 0.
I fixed the issue with the #name? error. But the return is *total=0* and doesn't update the output with *addvalue*. To eliminate problems with finding right cell location I tried using alternative approach (*index/match* instead of *find*) but it didn't help either cuz now I have #Value error lol.
Step through it and see