Simple Excel VBA Query?

I've recently inherited a large Excel spreadsheet, from a now retired co-worker, that is used to record staff annual leave. It is huge, cumbersome and slow and I would like to speed it up and maybe lessen the size of the file by using VBA calculations instead of the countless formulas spread throughout the document.

Unfortunately, I'm not particularly good with VBA. I can record macros and work through the resulting VBA code, but as to programming my own VBA code I'm less than a noob. I have various questions about VBA (which increase in difficulty) but I'll work through them one by one instead of dropping them all here at once.

So, first off: Imagine that the first 20 rows of columns A and B will be used to enter figures. I'd like a piece of code that will subtract B1 from A1, and enter the result (not the formula) in C1, then loop through the subsequent rows doing the same (i.e. B2-A2=C2, B3-A3=C3 etc) until row 20, after which it should stop.

Also, I'd like it to calculate only if there is a number in column B.

Any help would be greatly appreciated.

Thanks

2 Answers

Relevance
  • Anonymous
    9 years ago

    Native Excel functions almost always carry less overhead, are faster and better than VBA code. The following simple example for cell C1 will do what you say faster than any VBA?

    =IF(ISNUMBER($B1),$A1-$B1,"")

  • 3 years ago

    there's a sq. interior the top left of an get entry to database that as quickly as you click it it is going to elect each and all of the cells in that table. You click that, replica, then visit Excel and paste right into a worksheet. Excel can import no longer something yet textual content data; this is plenty less difficult to repeat and paste from get entry to to Excel. you additionally can replica updates from Excel and paste them decrease back into get entry to once you're completed. when you replica in Excel, top click that sq. i discussed in get entry to that selects each little thing and then elect Paste.

Still have questions? Get your answers by asking now.