VBA vs formulas

[dmj120]

I have a file I use to report weekly work orders for several regions and accounts. It works great, but is a bit tedious and I have to remember to copy/paste as values (on two tabs) before sending out.

Would there be any benefit to having these as a VBA marco?

date	=IFERROR(IF(P4="Dispatch",TEXT(TRIM(LEFT(D4,10)),"m/d/yyyy"),TEXT(MID(P4,FIND(" ",P4,FIND(" ",P4)+1)+1,256),"m/d/yyyy")),TEXT(TRIM(LEFT(D4,10)),"m/d/yyyy"))
days	=TODAY()-U4
Type	=IFERROR(IF(ISNUMBER(SEARCH("SM",P4)),"SM",IF(AND(P4="Dispatch",ISNUMBER(SEARCH("Incoming Inspection",Q4))),"Incoming","DM")),"DM")
DM	=INDEX(Act.Summary!$B$4:$D$61,MATCH(G4,Act.Summary!$B$4:$B$61,),2)
AM	=INDEX(Act.Summary!$B$4:$D$61,MATCH(G4,Act.Summary!$B$4:$B$61,),3)

 

[dmj120]

Found a good answer on another thread here.

Do you have 1 column that can be used to determine the end of the data?
Say "I know column A always has data in the last row of my sheet" ?

Then you can define some names like this

lastrow: =LOOKUP(2,1/(Sheet1!$A$1:$A$65535<>""),ROW(Sheet1!$A$1:$A$65535))
Notice the row 65535 (1 short of the last useable row 65536 in xl2003)

Then use that in subsequent dynamic ranges to define the last row

Acol: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,lastrow)


Hope that helps.

'mrexcel.com/board/threads/use-of-vba-vs-excel-formulas-in-cell.562611/post-2780296'

use of VBA vs excel formulas in cell

So far I've only been using VBA if Excel's functions couldn't solve a problem. When I do use VBA, I try to limit myself to writing functions. What I'm wondering is if I'm selling myself short by working like this. I'm looking for examples of situations where using Excel is the inferior solving...

www.mrexcel.com