VBA code required

[hrayani]

Hello Friends,

In a worksheet "Master" I have the below formulas

In Range B4:B9

=IFERROR(AGGREGATE(15,6,'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref/((('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_status="In Process")*('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_customer="abc"))*ISNA(MATCH('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref,B$3:B3,0))),1),"")

In Range C4:C9

=LOOKUP(2,1/('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref=$B4),'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_po_1)

In Range G4:G9

=SUMPRODUCT(--('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref=B4),'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_quantity)

What I want is a VBA code which when run does the following

1) get the values in all 3 above mentioned columns with the formulas
2) Change the formulas to values

Regards,

Humayun

 

[Legacy 143009]

Sub test()
  For i = 2 To 3
    For ii = 4 To 9
      Cells(ii, i).Value = Cells(ii, i).Value
    Next
  Next
  For ii = 4 To 9
    Cells(ii, 7).Value = Cells(ii, 7).Value
  Next
End Sub

 

[hrayani]

Sub test()
  For i = 2 To 3
    For ii = 4 To 9
      Cells(ii, i).Value = Cells(ii, i).Value
    Next
  Next
  For ii = 4 To 9
    Cells(ii, 7).Value = Cells(ii, 7).Value
  Next
End Sub

Thanks for the reply & sorry for coming back late on this - but this is not I want
What your code is doing is converting the formulas to values which I know can easily be done with below simple line of code. not sure if it's the appropriate way - but it works

Sub getdata()
Range("B4:G9").Formula = Range("B4:G20").Value
End Sub

But what would happen, when I next run the code ? How will we pull the data ?
So, I want the the formulas to be there in the code, which could pull the results & then convert those formulas results to normal values

Regards,

 

[Legacy 143009]

I don't understand. Are you looking for something like this?

Sub test()
  Cells(4, 2).Formula = "=IFERROR(AGGREGATE(15,6,'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref/((('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_status=""In Process"")*('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_customer=""abc""))*ISNA(MATCH('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref,B$3:B3,0))),1),"""")"
  Selection.AutoFill Destination:=Range("B9"), Type:=xlFillDefault
  Cells(4, 3).Formula = "=LOOKUP(2,1/('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref=$B4),'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_po_1)"
  Selection.AutoFill Destination:=Range("C9"), Type:=xlFillDefault
  Cells(4, 7).Formula = "=SUMPRODUCT(--('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref=B4),'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_quantity)"
  Range("B4:G4").Select
  Selection.AutoFill Destination:=Range("B9:G9"), Type:=xlFillDefault
  Range("B4:G9").Formula = Range("B4:G20").Value
End Sub

 

[hrayani]

Dear Flashbound,

Thanks for coming back again on this. I tried your code but it's giving me error Autofill method of Range Class Failed

Anyways, I worked out the same thing & come up with this. I was just about to come here

Sub getdata()
Application.ScreenUpdating = False

Range("B4:G20").ClearContents

Range("B4:B20").Formula = "=IFERROR(AGGREGATE(15,6,'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref/((('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_status=""In Process"")*('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_customer=""abc""))*ISNA(MATCH('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref,B$3:B3,0))),1),"""")"
Range("C4:C20").Formula = "=LOOKUP(2,1/('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref=$B4),'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_po_1)"
Range("D4:D20").Formula = "=IFERROR(INDEX('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_supplier,MATCH(B4,'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref,0)),"""")"
Range("E4:E20").Formula = "=LOOKUP(2,1/('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref=$B4),'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_article_1)"
Range("F4:F20").Formula = "=IFERROR(INDEX('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_quality,MATCH(B4,'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref,0)),"""")"
Range("G4:G20").Formula = "=SUMPRODUCT(--('C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_ref=B4),'C:\OneDrive\Documents\DATA ENTRY.xlsm'!orders_quantity)"

Range("B4:G20").Formula = Range("B4:G20").Value

Application.ScreenUpdating = False
End Sub

 

[Legacy 143009]

Oh good. OK. I didn't worked with formulas in VBA that much.
When you write like this "Range("B4:B20").Formula = "=IFERROR(....", does it adjust the formula content according to column and row address?

 

[hrayani]

Oh good. OK. I didn't worked with formulas in VBA that much.
When you write like this "Range("B4:B20").Formula = "=IFERROR(....", does it adjust the formula content according to column and row address?

Yes It does

 

[Legacy 143009]

Ok, then so you don't have to write code to pull down. Good to know Thanks

 

[hrayani]

Ok, then so you don't have to write code to pull down. Good to know Thanks

Yes exactly

Thanks for you time & efforts
I appreciate
Stay blessed

 

[Rich4689]

Hi, I have some code which i believe is copy and pasting the full contents of one file into another inc Formula and formatting. what i need is for the VB to write the info as values only
Could you let me know what to change - Thanks
With Workbooks.Open(Application.GetOpenFilename("Excel Files, *.xls*"), UpdateLinks = True)
.Sheets("DW IN").Range("A1:CK15").Copy _
wsDest1.Range("A1:CK15")
.Close False
End With