Lookup Max value on one sheet and populate on another in used range

[sharc316]

Hi,

I have the code below that pulls max value from Sheet1.
Need some assistance with modifying the second line. I would like to populate each row on Sheet2 column A that's being used. So it would need to check column B for how many rows there are in Sheet2.

Thank you for your help.

 Sheets("Sheet1").Range("D1").Value = Application.WorksheetFunction.Max(Sheets("Sheet1").Range("A:A"))
   Sheets("Sheet1").Range("D1").NumberFormat = "mmddyy"

[mana]

Dim r As Long


r = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("D1").Value = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("A1:A" & r))

[sharc316]

Thank you Mana. I'm having an issue where it only pulls the max value into cell D1 and not all the way down all rows where there is data.

[mana]

I'm sorry.
I can't understand what you want to do with my poor English ability.
Would you please post the small sample and expected result.

[sharc316]

Hi Mana,

When I run the code below is the result. It only populates cell A2, and I would like to have the values in column A to run all the way down as long as there are values in column B. If you can please take a look, I pasted code I edited as well.

DATE	PSA ID
2/17/2017	101198
	101198
	101198
	101198
	101198
	101198
	101198
	101198

Sub DateTest()

'Inserts max date
    Dim r As Long
 
 
r = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Sheet1").Range("A2").Value = Application.WorksheetFunction.Max(Sheets("Sheet2").Range("A2:A" & r))

End Sub

[mana]

I can't guess your sheet1 data.
I can't guess your sheet2 data.
I can't guess your expected results.


I'm sorry.
I can't understand your question at all.

[sharc316]

Sorry if it wasnt clear. Please see below.

I'm getting this from the code:

DATE	PSA ID
2/17/2017	101198
	101198
	101198
	101198
	101198
	101198
	101198
	101198

I would like to get this:

DATE	PSA ID
2/17/2017	101198
2/17/2017	101198
2/17/2017	101198
2/17/2017	101198
2/17/2017	101198
2/17/2017	101198
2/17/2017	101198
2/17/2017	101198

[jolivanes]

I think this is what sharc3160 meant mana.

Sub Maybe()
Dim sh2 As Worksheet
Set sh2 = Sheets("Sheet2")
    sh2.Range("B2:B" & sh2.Cells(Rows.Count, 2).End(xlUp).Row).Offset(, -1).Value = Application.WorksheetFunction.Max(Sheets("Sheet1").Range("A:A"))
End Sub

[sharc316]

Played with Mana's code and was able to get it to work! Added :A" & r to the range below.


r = Sheets("Authorized Chargebacks").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Authorized Chargebacks").Range("A2:A" & r).Value = Application.WorksheetFunction.Max(Sheets("Linehaul Trips").Range("A2:A" & r))