PDA

View Full Version : Find Max Value in one worksheet and return the value to a cell in another



sharc316
03-21-2017, 08:49 PM
Hi,

Been searching for some help with this issue to:

1) Look up max value in a column (number of rows varies) on worksheet B
2) Then return the the max value to a cell on worksheet A

Please help.

jolivanes
03-22-2017, 12:51 AM
You might try one of these.
Change references as required.

Sub Maybe_A()
Sheets("Sheet1").Range("D5").Value = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("E:E"))
End Sub



Sub Maybe_B()
Sheets("Sheet1").Range("D5").Value = Application.WorksheetFunction.Large(Sheets("Sheet2").Range("E:E"), 1)
End Sub

mdmackillop
03-22-2017, 01:20 PM
Or by formula =MAX(Sheet2!E:E)

sharc316
03-22-2017, 07:10 PM
Thank you jolivanes. I've used method A and it works. The max value that I'm pulling is a date. How would I format the destination cell ("D5") to date and have it display without slashes: ex, 031517

jolivanes
03-22-2017, 10:09 PM
Your Cell D5 can be custom formatted as mmddyy
or

Sub Maybe_A()
With Sheets("Sheet2").Range("D5")
.Value = WorksheetFunction.Max(Sheets("Sheet3").Range("E:E"))
.NumberFormat = "mmddyy"
End With
End Sub


or

Sub Maybe_B()
Dim a As Date
a = Application.WorksheetFunction.Max(Sheets("Sheet3").Range("E:E"))
Sheets("Sheet2").Range("D5").Value = "'" & Format(a, "mmddyy", "000000")
End Sub

sharc316
03-23-2017, 04:41 AM
The destination cell format displays without slashes ex: 031517. I'm using this cell to concatenate with another cell and the slashes come back in the concatenated cell ex: companyname 03/15/17. Any way to remove slashes from the concatenated cell? I'm using this cell as the file name.

jolivanes
03-23-2017, 10:59 AM
If you want to use the date in a filename, why not delete the slashes at that time like after concatenating?
That way you need to remove the slashes once only.

If the Company name is in cell D2 and the date with slashes in in cell C7, this will give you want you indicated you need.



Sub test()
MsgBox Replace((Range("D2").Value & " " & Range("C7").Value), "/", "")
End Sub

sharc316
03-23-2017, 04:02 PM
Thank you for your help.