PDA

View Full Version : Solved: Inserting formulae into Used range



Anomandaris
11-16-2009, 03:14 AM
Hi guys,

What I'm trying to do is use a macro to insert these formulae into the 2nd row and then copying it down their respective columns depending on the usedrange of Column A.

Any suggestions would be welcome
Many thanks


Column W




=VLOOKUP(D2,Summary!$D$2:$S$30000,16,FALSE)



Column X


=V2*W2


Column Y


=TEXT($F2,"00")&TEXT($H2,"00")&IF($K2,"O","F")


Column Z


=INDEX(IMAP!A:A, MATCH(Y2,IMAP!B:B,0))

Bob Phillips
11-16-2009, 03:46 AM
Untested


With Activesheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("W2").Resize(LastRow - 1).Formula = "=VLOOKUP(D2,Summary!$D$2:$S$30000,16,FALSE)"
.Range("X2").Resize(LastRow - 1).Formula = "=V2*W2"
.Range("Y2").Resize(LastRow - 1).Formula = "=TEXT($F2,""00"")&TEXT($H2,""00"")&IF($K2,""O"",""F"")"
.Range("Z2").Resize(LastRow - 1).Formula = "=INDEX(IMAP!A:A, MATCH(Y2,IMAP!B:B,0))"

Anomandaris
11-16-2009, 04:16 AM
Awesome ! works great

thanks