To calculate the Minimum values in an excel sheet

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

Hi,

thank you.. i tried it now. but there is no result minimum value displayed in the DestWb and there is no error also while compiling it.

Sorry I did not read your description of your problem so my first answer was not helpfull at all. Will try again later and see if I can manage a more helpfull suggestion.

Alf

okay sure.. thanks.

any suggestion is welcome.

i just need to solve this problem.

I thought ALF line was correct too. I have not read your request , but to find a Min value, let say in column 1, you can use

Please Login or Register  to view this content.

This line returns the min value of column A.

Hi AB33

I think the problem is that the OP wishes to read the min value form the file "BaseWb.xlsx" sheet1 range ?? (never could read R C notions) into the "DestWB.xlsx" sheet?? and range D3 and then another min value to D4 ....

Looking at his macro both files seems to be "opened" so one don't need to read data form a closed file atleast. Got to rush hopefully you can solve it. If not I'll have a go at it tomorrow.

Alf

Hi AB33,

the line by Alf is definitely correct but its not writing the min value to destwb which is the resultant excel wb.

@ Alf: you absolutely understood it correct.

I think you almost got it right try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alf

Hi,

i tried it. again, i am not getting any error but the value is not written in my destWb.

can you check if my declarations and making the workbook active is correct or not?

Thanks

Halo,

any ideas on it yet?

is it possible to loop the minimum formula? for "n" number of rows and "m" number of columns.

any kind of suggestions are welcome

You are adding a new workbook but the name is Book1.

Please Login or Register  to view this content.

If you wish to name it "DestWB" you must save it first i.e.

Please Login or Register  to view this content.

before writing the formula you must activate it i.e.

Please Login or Register  to view this content.

and then add the formula command

Please Login or Register  to view this content.

so now it should be written to DestWB.xlsx

Re looping will come back to you on that as I'm a tad bussy at the moment.

Alf

Please Login or Register  to view this content.

Hi,

Saving the file worked perfectly.

but i am still getting the error for the last 2 codes.

saying " it is out of range"

i do not know if the BaseWb and DestWb is get co-related or not.

Kindly help

thank you

Should i declare the "sheets" also?

Sorry for the delay as I've been away.

Should i declare the "sheets" also?

Yes if you always wish to place formula in the same sheet in the "DestWB.xlsx" file. As it was written it would add formula to active sheet so changing it to:

Please Login or Register  to view this content.

will always place formula on sheet1 cell D3 in the "DestWB.xlsx" file.

Alf

Hi,

sorry for the delay.

it is still not working. i dont know where the problem lies.. the result value is just not getting pasted into DestWb. i dont know if it is no reading from BaseWb or if there is any other problem.

i am not getting any error box. but the cell in DestWb has an error coz of no value.

is it possible to solve this issue?

thank you
Roopa

Some issues. You say that your Excel version is 2003 but the uploaded file is called "dummy.xlsx". That indicates you are either running Excel 2007 or 2010. So I think you should update your forum profile.

Since both "DestWB" and "BaseWB" are "xlsx" files you can not run the macro from either of them so my best advice is that you step through the macro and see what happens i.e. that files get created and activated as they are supposed to do.

If your "dummy.xlsx" is similar to your "BaseWB.xlsx" then there is no Sheet1 in your "dummy.xlsx" file could that be your problem?

Alf

Hi,

The excel version im using is definitely 2003 version. but i am able to open and edit .xlsx file.

dummy file is just an example and it has one sheet.

i want this macro to work on any similar excel file with many sheets. that is the reason y i have called it as "baseWb".

i also checked now by converted everything into .xls format and run the program. it still does not ge my result value.

in D3, the error is " invalid cell reference error".

thank you
Roopa

I've uploaded 3 files "Master.xls", "BaseWB.xls" and "DestWB.xls". The file “Master.xls” is the file that contains the macro.

Keep all 3 files open and select file "Master.xls" and run macro "Update". In the "DestWB.xls" you should find the result you wanted at least it worked for me. Hopefully this will help you to solve your problem.

Alf

Hi

thanks a lot for your effort.

i tried running the macro. but i am getting an error for the below statement as APPLICATION DEFINED OR OBJECT DEFINED ERROR.
"Range("D" & n + 1).Formula = "=MIN('[BaseWb[1].xls]Sheet1'!R5C" & n & ":R54C" & n & ")"

I fail to understand where i am going wrong.
Roopa

As this works for me without any problem I wonder about the line

Please Login or Register  to view this content.

as my macro uses

Please Login or Register  to view this content.

So what happened that changed "=MIN('[BaseWB.xls]" to "=MIN('[BaseWB[1].xls]" ?

Alf

i changed it from baseWb to basewb[1] because when i opened the three files,
instead of opening as master.xls , basewb.xls and destwb.xls,
it opened as master[1].xls, basewb[1].xls and destwb[1].xls respectively.

hello everybody..

anybody there?
any updates on the solution?
im still stuck with the last part.

Thanks
Roopa

Dear Alf,

i tried the macro which u sent earlier with 3 excel sheets inserted. it worked perfectly fine now. im really Thankful to you.

thank you

should i use the same formula for another set of columns?

Thanks
Roopa

what if i have to get another set of min values similar to this but write it on the next column of destWb?

should i use the same formula for another set of columns?

As the macro loops from n = 2 to 17 and the formula for min is

Please Login or Register  to view this content.

then the checked area goes from row 5 to row 54 and column goes from 2 to 17 i.e. from B column to Q column.

but write it on the next column of destWb?

then change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

if you wish to write to the E column.

Alf

Hi,

i tried that. it worked.

but i am trying to read the data from Sheet2 of BaseWb and do the same calculation of min values.

but it is not reading form sheet2 but replicating the values of sheet1.

i changed the code to this

Please Login or Register  to view this content.

the rest of the coding remains the same.

Thanks
Roopa

Hi,

i checked the values in sheet 1 and 2. they are completely different. and the formula in destWb read as

Please Login or Register  to view this content.

as sheet1 itself. but in the formula i have given as sheet2 in the macro code.

Roopa

Then I have no explanation as the formula used in macro will only link to Sheet2.

You could do this test, delete sheet1 from the BaseWB file and run macro and see what happends. If there is a line in the macro refering to Sheet1 you will get a run-time error 1004: "Application-defined or object-defined error.

i know what the problem is.

in the baseWb excel, only sheet1 is getting saved. how do i save the whole excel file as BaseWb?

the code used is given below.

Please Login or Register  to view this content.

and the rest of it remains the same.

any changes in the declaration of the baseWb?

also, since im using excel 2003 version, can this macro run only .xls file and not .xlsx file?

should i update my microsoft office to run macro on .xlsx files?

thanks
Roopa

can this macro run only .xls file and not .xlsx file?

No, change ".xls" to ".xlsx" the macro will work with Excel 2007 files and probably 2010 files if you are running the macro from an Excel 2003 file in excel 2003 environment.

in the baseWb excel, only sheet1 is getting saved

In your macro you are setting all files you opens as "BaseWB"

Please Login or Register  to view this content.

so you will only get Sheet1 if the files you open only contain 1 sheet.


Alf

okay..

it is rectified.

how do i generalise calling the sheets?
because each excel file will have sheets saved in different names and not as "sheet1" or sheet2".

i just need to switch from 1 sheet to another irrespective of the sheet name.

Roopa

how do i generalise calling the sheets?

See Activate_sheet.png

The first name Sheet1 is set by Excel. The second name i.e. in brackets is the one I can change to what suits me. This name will also be my tab name.

To activate a sheet one can use either the Excel name i.e.

Please Login or Register  to view this content.

or I could use the sheet number i.e.

Please Login or Register  to view this content.

then of course I can also use tab name i.e.

Please Login or Register  to view this content.

If you move say sheet "Second" after sheet "Third" the tab order will be "Sheet1", "Third" and "Second" but in the Visual Basic window the order will be as it was i.e. Sheet1(Sheet1), Sheet2(Second) and Sheet3(Third).


Alf

okay.. so u mean to say that the sheets can be addressed only using the name of the sheet or by calling it as sheet1, sheet2 etc.

but is there no way you can call the first sheet and then on increment a variable to point it out to the next sheet in the loop? like by defining, sheet(n).

is there such a pointer to he sheets?

Please Login or Register  to view this content.

Yes of cource a loop like this would activate everey sheet in a workbook.

Please Login or Register  to view this content.

Alf

okay.
should i declare any variable in the begining? because it is saying iundefined variable and lot of errors.

Thanks
Roopa

also,

i have 3 columns of "minimum values" which has been calculated earlier with the code.

now, i need to find the average of the 3 columns and paste the result in the next column.

i.e it has to calculate columnwise and jump to the next row of such data.

Please Login or Register  to view this content.

what changes should i make in this formula? w.r.t "n" and R & C values?

Thanks
Roopa

should i declare any variable in the begining

Yes

Please Login or Register  to view this content.

what changes should i make in this formula? w.r.t "n" and R & C values?

See my earier comments in post #26

Alf

I agree with post #26, but this moves column wise first and then goes into the next row.

unlike previously, it was moving row wise first and then column wise.

thats the change i would require.

thanks
Roopa

but this moves column wise first and then goes into the next row

Not right it searches row 4 to row 54 i.e. (R4 R54) this is the fixed row range and it then loops through columns 2 to 17 i.e. B to Q since columns is defined as "C & n" and n loops from 2 to 17.

Alf

dear all,

this topic is solved and is closed

thanks all. thanks a lot Alf.

Regards
Roopa

Glad to be of help! Thanks for feedback and rep.

Alf

PS

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save