Add COMMENT to a formula ?

[Logit]

I'm trying to add a comment to a formula. Research shows the following will work :

Please Login or Register  to view this content.

Place that formula into a cell and all that will show in the cell is the current date and time.

Now, I'm using a ComboBox control on a User Form, that will hold different formulas for selection which are subsequently written to a different worksheet.

I'd like to include a comment at the end of each formula so the user will understand which formula to choose.
Using the following syntax (without a comment) works for a ComboBox:

Please Login or Register  to view this content.

An error is produced if I use:

Please Login or Register  to view this content.

What syntax will produce the result I am seeking ?

Thank you for your assistance.

[teylyn]

Why don't you show only the comment in the combobox and then use VBA to apply the respective formula?

[xlnitwit]

Hi,

You must escape the quotation marks contained within the string by doubling them so that the compiler knows you are not trying to terminate the string.

"=NOW() + N(""This is my comment"")"

[Winon]

Hello Logit,

Will this Code work for you?

Please Login or Register  to view this content.

[Logit]

Thank you all for your suggestions.

If the .AddItem selection is the first on the list, and it appears as below"

Please Login or Register  to view this content.

I need for it to write Sheet2.Cells(4, 2) =A1+TIME(1,0,0) and not write the remainder +1 Hour GMT

I've tried several variations of the LEFT command but not having success.

Again, thank you for your assistance !

[Logit]

Oh ... the length of text after = A1+TIME (1,0,0) will always be 12.

[Winon]

Hello Logit,

Is this Code below what you want?

Please Login or Register  to view this content.

Regards.

[Logit]

Winon:

Thank you for your reply

Refer to the attached, Sheet #2.

[Winon]

Hi Logit,

Call Letters <---- Call Letters gets written to a different cell. Where?

Please try try the revised Code below;

Please Login or Register  to view this content.

[Logit]

Winon:

Thank you for the reply. I am out of town for a day, will respond upon my return.

[protonLeah]

If you have a time in cell A1 (don't know which sheet, though) try:

Please Login or Register  to view this content.

[Logit]

Winon:

Please see the attached, page #2.

Thanks.

[Logit]

protonLeah:

I get an error message [ Run time error '13': Type Mismatch ] on this line:

Please Login or Register  to view this content.

[mikerickson]

You can include a comment in a cells formula with

=IF(FALSE, someFormula, "this is my comment")

[Winon]

Hi Logit,

Please try the attached revised Workbook

Kind Regards.

[sanram]

May be you are trying to do this.

Please Login or Register  to view this content.

See the attachment.

[sanram]

This is a trick, nothing else. But this trick will work with the numerical data only. Because N("Any String") returns 0 and if you add 0 with anything then the result will be same. But if you use that with a string value then it will show a 0 at the right of your result. Say, A1=Rose, then if you use

Please Login or Register  to view this content.

Then the result will be Rose0

I hope that it makes sense.

[Winon]

Hi sanram, thank you for you input.

I have already tried and tired that approach, but it does not entertain the exact requirement of this Thread. Therefore I have uploaded my latest Workbook as a possible solution. Let us see what the outcome is.

Kind Regards.

[Logit]

Winon

How do I change:

Sheet2.Cells(4, 2).Formula = ("=SUBSTITUTE(OJ2,OJ2,LEFT(OJ2,15))")

so the printed result includes all of the following : =A1+TIME(1,0,0)


... so it prints the EQUAL sign also ...

[Logit]

Winon:

I'm studying your code

Sheet2.Cells(2, 400) = ComboBox1.Value
Sheet2.Cells(4, 2).Formula = ("=SUBSTITUTE(OJ2,OJ2,LEFT(OJ2,15))")
Sheet2.Cells(4, 2).Value = Sheet2.Cells(4, 2).Value
Sheet2.Cells(2, 400).ClearContents
Sheet2.Cells(6, 2) = TextBox1.Value

I understand ("=SUBSTITUTE(OJ2,OJ2,LEFT(OJ2,15))") is doing the work of extracting what is needed from the
text string - however - I don't understand what OJ2 is. I suspect OJ2 refers to the text string because in the last part of the formula is
LEFT(OJ2,15) which is what we are trying to extract ... the first 15 characters on the left side.

Still ... what is OJ2?

Also, why write the ComboBox. Value to a temporary location of B400 ? Unless something in the formula is using that information ?
I don't understand what part of the formula is referring to B400.

[protonLeah]

In post #1, you said:
"... Place that formula into a cell and all that will show in the cell is the current date and time..."
So I assumed that the A1 in your code was referring to a time in cell A1 on either sheet1 or sheet2 ( I didn't know which). In the workbook I attached, the code is looking for a time in Sheet2, A1. If it is blank the code (as written) will error. If there is a time, then the output is a time adjusted by the GMT selected from the combobox.

However, based on re-reading #1 I have modified the code to include the date. But I still don't know which Sheet A1 refers to so I have included both in the code. Comment out/delete as desired:

Please Login or Register  to view this content.

[Logit]

protonLeah:

Thank you for your response.

From the code for ListBox1:

Please Login or Register  to view this content.

(For the purpose of discussion I'm just selecting one item at random):

.AddItem "=A1+TIME(15,0,0) Your Zone +15 GMT"

... only =A1+TIME(15,0,0) needs to parsed from the line of text and written to a specific cell on sheet one.

The cell where it is written will refer to a time value in A1 of the same sheet and (because of the formula) display the current time
15 hours ahead of the time in cell A1.

Without the equal sign at the front of the parsed text, it won't be recognized as a formula.

Winon's last suggestion does everything needed but include the equal sign. I have attempted to modify the line of code:

Please Login or Register  to view this content.

without success. Although, this line
of code does specify the first 15 characters on the left side of the parsed text, I suspect VBA is thinking it needs to drop
the equal sign for some reason. I can't figure that part out.

A big thank you to all for your patience.

[Winon]

Hello Logit,

Thank you for your continued interest an intelligent observations.

OJ2 = Cells(2, 400), which i need to employ as a temporary helper column which is cleared after completion of the Code.

B400 = Me being tired and making a mistake. Please ignore B400.

With regards to your last request, i.e. -

...so the printed result includes all of the following : =A1+TIME(1,0,0)

In your last uploaded sheet however you state:

But all it needs to prints is: =+TIME(3,0,0)

It took me quite some time to get the latter working, but now I am confused. Could you please cofirm if you want =+TIME(3,0,0) or =A1+TIME(1,0,0)?

Thank you.

[Winon]

O.K. Logit,

I have decided to send you two sample Workbooks. One caters for =+TIME(3,0,0), and the other will give you =A1+TIME(1,0,0), so take your pick!

Should you still experience any issues, please feel free to advise accordingly.

Kind Regards.

[Logit]

Winon:

YOU ARE THE MAN !!!!

Rev3 did it. Thank you so much.

There is alot for me to digest in the code. I find this self-study stimulating and rewarding. A great big THANK YOU to all who participated
in this thread. Your responses are greatly appreciated.

Time to move to the next segment of this project.

[Winon]

Hi Logit,

Thank you for the feedback, and also for adding to my Reputation, much appreciated!

You are welcome, glad I could help.

Good luck with your Project!

Regards.