Write Macro from formulas?

[Barryj]

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.

[acw]

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]

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.

[acw]

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]

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.

[acw]

Hi

Attached file worked for me.

Tony

[Barryj]

Thanks again Tony works great.