adding equation to cell via VBA code

[wyldjokre69]

Hello again,

Would someone take a look at this code and tell me where I'm going wrong. It's more of a copy and paste code. I started by doing a macros for coding an equation to convert pounds to stones + pounds. Once I got the equation into a VBA code, I tried to use a previous code and have the equation continue to convert the pounds cell to the stones + pounds cell after conversion, unfortunately this is where I've erred as I'm getting error code: 450.

Here's the code, highlighted is the copy and paste code I tried to use from an earlier code which works like a charm:

Please Login or Register  to view this content.

I'm trying to populate all cells that I have assigned for the sterling + pounds equation. When there are no more conversions to do, the code should stop.

CheerZ!
Wyldjokre69

[fredlo2010]

The best part of populating formulas with FormulaR1C1 is that you can extend them as much as you want and apply to as many cell as you want at tyhe same time.

for example if you want to aply the formula you created to the Range "A1:A10" just type this:

Please Login or Register  to view this content.

and you are done!!

[TMS]

Be interesting to see what C[-5] gives you in column A ... either an error or if it wraps around. I'd be betting on an error but you ever know.


Regards, TMS

[fredlo2010]

Be interesting to see what C[-5] gives you in column A ... either an error or if it wraps around. I'd be betting on an error but you ever know.


Regards, TMS

hahhaha I have no idea but I think she user will understand the point. There is no way for me to see where she has the formula with " ActiveCell"

[wyldjokre69]

Thanx fredlo, it worked, but is it possible to be able to loop the process where I don't have to keep changing the range should the data exceed the last range I put in.

CheerZ!
Wyldjokre69

[fredlo2010]

where are you trying to place the formula ? what range right now? I suggested column "A" but according to the formula and as suggested by TMShucks its most likely impossible

That would help me to figure out the whole thing

Thanks

[TMS]

Just checked ... it doesn't error, it wraps around. However, I suspect that isn't the required outcome.

A1: =CONCATENATE(ROUNDDOWN(XEZ1/14,0), "-", MOD(XEZ1,14))

Please Login or Register  to view this content.

Regards, TMS

[fredlo2010]

Just checked ... it doesn't error, it wraps around. However, I suspect that isn't the required outcome.

A1: =CONCATENATE(ROUNDDOWN(XEZ1/14,0), "-", MOD(XEZ1,14))

@ TMShucks Oh wow nice you literally meant it wraps around Nice check

@ Wyldjokre69 or you can use my favorite version using cells to count instead of range. Both solutions TMShucks's and mine will provide the same outcome

Please Login or Register  to view this content.

[wyldjokre69]

Fredlo2010 and TMShucks, you guys are brilliant, made the necessary adjustment to the above code and it worked out perfectly.

Thank you to you both.

CheerZ!
Wyldjokre69

[fredlo2010]

I am glad we were able to help. For the future and to keep good practices in the post and also help other who might stumble across this in their search for possible solutions, please indicate which answer code you picked out of all the possibilities (in this case two) and also the code you obtained at the end after modifying it.

Thanks

[TMS]

@fredlo2010: there's really no difference in the solutions, more a matter of personal taste. I also use the Cells option but usually when I also want the last column and I'll use Cells for consistency.

Regards, TMS

[wyldjokre69]

Hello,

You are correct, here is the final version of the vbs code I used. You will see as to which version of the code you wrote is the one I used. Had to make the necessary adjustments to fit my spreadsheet, but it's still the same code you guys so brilliant made at least the part of the code I was having problems with:

Please Login or Register  to view this content.

Once again I just like to thank y'all for your help and am so glad I found this forum. I'll be going back to check my other solved forum queries and put the final codes on to those as well.

CheerZ!
Wyldjokre69

[TMS]

You're welcome. Glad we could help. Thanks for the feedback.