Hi everybody,
i have an excel workbook which has 50 rows of data in 15 columns. i have named this workbook as baseWb in my macro.
i have created a new excel workbook which will have the minimum values. i have named this workbook as DestWb in the macro.
i need to find the minimum value of each column in baseWb and write the minimum value in DestWb.
i am getting stuck with the formula to be used to calulate the minimum from one excel book and write it to another workbook..
the code which i have used is as follows :
Sub Merge()
Dim DestWb As Workbook, WB As Workbook, WS As Worksheet, SourceSheet As String
Dim BaseWb As Workbook
Dim Sheets As Worksheets
Dim n As Integer
SourceSheet = "Input"
FileNames = Application.GetOpenFilename( _
filefilter:="All Files (*.*),*.*", _
Title:="Open file.", MultiSelect:=True)
If IsArray(FileNames) = False Then
If FileNames = False Then
Exit Sub
End If
End If
For n = LBound(FileNames) To UBound(FileNames)
Set BaseWb = Workbooks.Open(Filename:=FileNames(n), ReadOnly:=False)
Set BaseWb = ActiveWorkbook
Set DestWb = Workbooks.Add
Windows("Book1").Activate
ActiveCell.FormulaR1C1 = "Crank angle "
Range("B1").Select
ActiveCell.FormulaR1C1 = "Cam angle"
Range("D1").Select
Columns("A:A").ColumnWidth = 11.43
Columns("B:B").ColumnWidth = 10.12
ActiveCell.FormulaR1C1 = "1st injector"
Range("D2").Select
ActiveCell.FormulaR1C1 = "min"
Range("E2").Select
ActiveCell.FormulaR1C1 = "mean"
Range("F2").Select
ActiveCell.FormulaR1C1 = "max"
Range("G2").Select
ActiveCell.FormulaR1C1 = "3S"
Range("A3").Select
ActiveCell.FormulaR1C1 = "0"
Range("A4").Select
ActiveCell.FormulaR1C1 = "10"
Range("A5").Select
ActiveCell.FormulaR1C1 = "20"
Range("A4:A5").Select
Selection.AutoFill Destination:=Range("A4:A15"), Type:=xlFillDefault
Range("A4:A15").Select
Range("B3").Select
ActiveCell.FormulaR1C1 = "0"
Range("B4").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B5").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B8").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B10").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B11").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B12").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B13").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B14").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Range("B15").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]*2)/3"
Set BaseWb = ActiveWorkbook
DestWb.Activate
D3 = "=MIN('[BaseWb.xlsx]Sheet1'!R5C2:R54C2)"
D4 = "=MIN('[BaseWb.xlsx]Sheet1'!R5C3:R54C3)"
Exit For
Next n
End Sub
i have also attached my base file from where i need to extract the data.
it would be really helpful if someone can give me options to correct my macro code.
thank you
Bookmarks