I am unable to change the value of a cell in my spreadsheet

Question

For some reason I can't change the value using a VBA function in a spreadsheet, I am infuriated at this moment. I have wasted an hour of my life because of this line. Can someone please tell me why it is not working?

Public Function GenerateDetailedReport(data As Range)
     Worksheets(5).Cells(2, 2).Value2 = "Maybe"
     GenerateDetailedReport = 1
End Function

I keep getting #VALUE! error every time I run it. I can read the cell value with no issue, but I cannot change the value.

I can't see "data" used in your function. it might be the issue. — Tehscript, Mar 19 '18 at 00:36
data is irrelevant because I am not attempting to use that variable. I am honestly attempting to generate a report that needs to be flexible because it's not a static report. — Matthew, Mar 19 '18 at 00:51
There's been a lot of discussion about this and certainly in plain straight answer as Gary pointed out, you can't change a cell value via UDF (*user defined function*). [Here's](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) one post by Tim though that do that however, this is a *use at your own risk* example. — L42, Mar 19 '18 at 00:57
So a UDF or a user defined SUB is not allowed to change cell values? — Matthew, Mar 19 '18 at 01:08
Cell does not need .Value, your basic code in a Sub would be `Sheet5.Cells(2, 1) = "Test"` — GMalc, Mar 19 '18 at 02:48
If you want to use the variable "cell" then you need to `Set` it like Gary's Student said below. `Dim cell As Range Set cell = Worksheets(1).Cells(2, 1) cell.Value = "Test"` — GMalc, Mar 19 '18 at 03:14

score 2 · Answer 1 · answered Mar 19 '18 at 00:46

2

You can't change a cell value in a function, only return value.

answered Mar 19 '18 at 00:46

Gary's Student

95,722
10
59
99

Then why do all the tutorials show it that way? Can I change it using a Sub instead? – Matthew Mar 19 '18 at 00:49
Yes...use a sub – Gary's Student Mar 19 '18 at 00:50
I've tried using a sub and this is what I had, `Public Sub ChangeCell() cell = Worksheets(5).Range("A2") cell.Value = "Test" End Sub` – Matthew Mar 19 '18 at 00:53
You are missing a **Set** – Gary's Student Mar 19 '18 at 00:55
Does not work. When I try using the set it gives me a 424 error. I switched it up to this `Worksheet(5).cells(2,1).Value = "test"` and it gives me a **Error 1004: Application-defined or object-defined error** – Matthew Mar 19 '18 at 00:57
@Gary if that's all the answer you are going to give, then you should close as a dup rather than answer. This has been asked and answered countless times – chris neilsen Mar 19 '18 at 06:04

I am unable to change the value of a cell in my spreadsheet

1 Answers1