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.
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.
You might try one of these.
Change references as required.
Code:Sub Maybe_A()
Sheets("Sheet1").Range("D5").Value = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("E:E"))
End Sub
Code:Sub Maybe_B()
Sheets("Sheet1").Range("D5").Value = Application.WorksheetFunction.Large(Sheets("Sheet2").Range("E:E"), 1)
End Sub
Or by formula =MAX(Sheet2!E:E)
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
Your Cell D5 can be custom formatted as mmddyy
or
orCode:Sub Maybe_A()
With Sheets("Sheet2").Range("D5")
.Value = WorksheetFunction.Max(Sheets("Sheet3").Range("E:E"))
.NumberFormat = "mmddyy"
End With
End Sub
Code: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
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.
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.
Code:Sub test()
MsgBox Replace((Range("D2").Value & " " & Range("C7").Value), "/", "")
End Sub
Thank you for your help.