Barryj
03-20-2006, 12:36 PM
I have a sheet which is currently calculated by formulas, but I want to have a macro to do this instead, how can I write a Macro that reflects what the formulas are doing?.
Currently the original data is in columns BCDE & QRST, I have included a file to show how this currently works at the moment.
The Columns that are in use at the moment is how I need the sheet to reflect the Macro.
Hi
As the formulas are working, why don't you have the macro insert the formulas, then copy, pastevalues the results? You could work out how far down to copy the formulas based on the length of the original data lists.
HTH
Tony
Barryj
03-21-2006, 12:25 AM
How would I go about doing this method, as far as getting a Macro to insert the formulas, they only have to go down 30 rows but need to start at row 7.
Any help is greatly appreciated.
Hi
Try the following code. It will in put the formulas as they stand, turn the results into values and remove any #VALUE! results.
Sub EnterFormulas()
Sheets("sheet1").Select
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("G7").Formula = "=b7"
Range("H7").Formula = "=IF(ISERROR(VLOOKUP(G7,$B$7:$C$36,2,0)),"""",VLOOKUP(G7,$B$7:$C$36,2,0))"
Range("H7").AutoFill Destination:=Range("H7:H" & LastRow)
Range("I7").Formula = "=IF(ISERROR(MATCH(G7,$B$7:$B$36,0)),"""",MATCH(G7,$B$7:$B$36,0))"
Range("I7").AutoFill Destination:=Range("I7:I" & LastRow)
Range("K7").Formula = "=IF(D7=B7,B8,D7)"
Range("L7").Formula = "=IF(ISERROR(VLOOKUP(K7,$D$7:$E$36,2,0)),"""",VLOOKUP(K7,$D$7:$E$36,2,0))"
Range("L7").AutoFill Destination:=Range("L7:L" & LastRow)
Range("M7").Formula = "=IF(ISERROR(MATCH(K7,$D$7:$D$36,0)),"""",MATCH(K7,$D$7:$D$36,0))"
Range("M7").AutoFill Destination:=Range("M7:M" & LastRow)
Range("G8").Formula = "=IF(COUNTIF($K$7:$K7,OFFSET($B$7,(I7),0))=0," & _
"OFFSET($B$7,(I7),0),IF(COUNTIF($K$7:$K7,OFFSET($B$7,(I7+1),0))=0," & _
"OFFSET($B$7,(I7+1),0),IF(COUNTIF($K$7:$K7,OFFSET($B$7,(I7+2),0))=0," & _
"OFFSET($B$7,(I7+2),0),OFFSET($B$7,(I7+3),0))))"
Range("G8").Copy Destination:=Range("G9")
Range("K8").Formula = "=IF(COUNTIF($G$7:$G8,OFFSET($D$7,(M7),0))=0," & _
"OFFSET($D$7,(M7),0),IF(COUNTIF($G$7:$G8,OFFSET($D$7,(M7+1),0))=0," & _
"OFFSET($D$7,(M7+1),0),IF(COUNTIF($G$7:$G8,OFFSET($D$7,(M7+2),0))=0," & _
"OFFSET($D$7,(M7+2),0),OFFSET($D$7,(M7+3),0))))"
Range("K8").Copy Destination:=Range("K9")
Range("G10").Formula = "=IF(OFFSET(B$7,I9,0)="""","""",OFFSET(B$7,I9,0))"
Range("G10").AutoFill Destination:=Range("G10:G" & LastRow)
Range("K10").Formula = "=IF(OFFSET(D$7,M9,0)="""","""",OFFSET(D$7,M9,0))"
Range("K10").AutoFill Destination:=Range("K10:K" & LastRow)
Range("G7:M" & LastRow).Value = Range("G7:M" & LastRow).Value
Range("G7:M" & LastRow).Replace what:="#VALUE!", replacement:=""
End Sub
HTH
Tony
Barryj
03-23-2006, 06:12 PM
Tony have put this macro into a module and calling it with a button, but it keeps repeating the first 3 rows down the list.
If you wipe the data in rows G7 to M7 and then run with the button you will see what I mean.
Tried to make some adjustments but still same problem, any thoughts.
Hi
Attached file worked for me.
Tony
Barryj
03-24-2006, 12:25 PM
Thanks again Tony works great.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.