Formula in macro not working

[kosherboy]

Hi.
Can anyone fix this formula I have in the macros so that it can work properly?

This is the outcome I want the formula below to do: In the 2nd to last column, starting from the 18th row, I would like to multiply the 3rd to last column by the value in cell A1.

Please Login or Register  to view this content.

Any help would be greatly appreciated.

Thank s in advance.

[Richard Buttrey]

Hi,

Untested but try

Please Login or Register  to view this content.

note the double quotes....

[kosherboy]

Thanks.
I tried the code but the 'formula bar' in the spreadsheet shows this =(A1*"&Range(Cells(18, lc - 3), Cells(lr - 1, lc - 1))") and the relevant cell shows this #VALUE!

[kosherboy]

Also, I can get this formula to work if I use the R1C1 method but the problem with this is that cell A1 (which has the value I want) changes to ascending cells (A2, A3, A4 A5 etc) when the formula is dragged down the cells.

[Richard Buttrey]

Hi,

Looking more closely you appear to be trying to incorporate VBA syntax in and Excel formula.

Upload your workbook and manually add some typical results. Explain in a note where the results are how you have calculated them.

[kosherboy]

Sure. Copy of Row Columns.xlsm

Thanks.

[Richard Buttrey]

How are you calculating cost? Please give specific cell references.

[kosherboy]

I'm calculating the retail multiplied by the percent off the retail. So in this example the client is getting 70% off the retail therefore I multiply the retail by 0.3 which is found in cell A1. The cost will differ with each invoice.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[kosherboy]

Thanks, that worked but how can I get this cell to show the formula that's in it as opposed to just the values?

[Richard Buttrey]

In that case...

Please Login or Register  to view this content.

[kosherboy]

Thank you but your code works only if I keep the columns as is, however, I want to work with the last column being unknown since I may insert columns or take away columns for all my invoices.

[kosherboy]

Your code really works for the original question I asked. Thank you for your time. This thread is solved.

[Richard Buttrey]

Hi,

See attached.

My preferred method for this sort of stuff is to hold any master formula on the sheet and copy them as appropriate. Here I've used a new hidden row 1.

And specifically identifying the last column with a range name is to be preferred since the used range property is not particularly safe. For instance entering a value past the end column of the invoice and then deleting it changes the used range until you subsequently save the workbook.

So the attached uses range names and the following macro

Please Login or Register  to view this content.

[kosherboy]

That's another approach but it worked great!
Thank you for you efforts

[kosherboy]

Hi. Didn't realize I can continue posting on a thread that I marked as solved.
Can you help me with this issue?


The code below inserts a formula into the given cell resulting in "=E18*$A$1" where "A1" = the cost of the customer, see attachment: Copy of Row Columns.xlsm

Is there a way to modify this section of the code only (see below) where the result of the cell will return "=E18*0.3"? Now obviously I can enter "0.3" into the macro but the value in A1 will always change with every invoice, so the code needs to be flexible.

Please Login or Register  to view this content.