Ask Your Question
0

Setting a formula in macro basic

asked 2016-09-16 03:21:39 +0200

hornetster@gmail.com gravatar image

Morning, Trying to set a formula in a specific cell, but it keeps recognising it as just text...

Using the following code:

sheet.getCellByPosition(4,4).String = date()
sheet.getCellByPosition(4,5).String = InvoiceNum
sheet.getCellByPosition(3,16).String = "=IF(B17="";"";1)"

but it keeps coming up a text, and (obviously) not working. Others work fine, and looking at the Watch results, looks like it should be ok (ie Formula = "=IF(H5="";"";15)".

Anyone? Thanks.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-09-16 03:37:37 +0200

Ratslinger gravatar image

updated 2016-09-16 03:45:14 +0200

You need to set it as a formula:

sheet.getCellByPosition(3,16).setFormula("=IF(B17="";"";1)")

Edit: Sorry - forgot a ) - this time for sure

edit flag offensive delete link more

Comments

If this answer resolves your question please tick the ✔ (upper left area of answer).

Ratslinger gravatar imageRatslinger ( 2016-09-16 03:53:27 +0200 )edit

That does 'seem' to work, but now have another issue. When I do that, it successfully puts a formula in the cell, but now the formula just won't work....? ie puts the formula in the cell, but when I populate B17, it doesn't place a "1" in the cell, just stays blank. It appears to get rid of 1 of each of the pairs of inverted commas. ie formula becomes: =IF(B17=";";1) (with no error....)

hornetster@gmail.com gravatar imagehornetster@gmail.com ( 2016-09-16 06:32:54 +0200 )edit

Hi

The formula is a string, opened and closed by a quote. In Basic quotes contained within must be doubled

.setFormula("=IF(B17="""";"""";1)")

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-09-16 09:06:55 +0200 )edit

Excellent! Yes, that works, but.... it keeps reformatting the cell as Boolean.... :-(

hornetster@gmail.com gravatar imagehornetster@gmail.com ( 2016-09-16 13:42:32 +0200 )edit

@pierre-yves samyn - Thank you for input; I should have caught that.

hornetster - Because of entering the original formula with single quotes, a Boolean Value resulted. You should be able to correct this with a right click on the cell and selecting Clear Direct Formatting.

Ratslinger gravatar imageRatslinger ( 2016-09-16 15:00:11 +0200 )edit

Thanks a heap! All working now....

hornetster@gmail.com gravatar imagehornetster@gmail.com ( 2016-09-17 06:53:18 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-16 03:21:39 +0200

Seen: 2,141 times

Last updated: Sep 16 '16