PDA

View Full Version : [SOLVED] Lookup Max value on one sheet and populate on another in used range



sharc316
04-08-2017, 10:05 AM
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
04-08-2017, 06:24 PM
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
04-09-2017, 10:59 AM
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
04-10-2017, 03:13 AM
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
04-10-2017, 05:35 PM
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
04-11-2017, 02:56 AM
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
04-11-2017, 05:36 PM
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
04-11-2017, 07:21 PM
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
04-12-2017, 08:20 PM
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))