Macro for Max Value, convert to value

[asv83]

HI

I created a excel spreadsheet full of formulas to automatically update for reporting purposes. There are a 2 sheets in my excel file where i would like to;

1) Sheet2 (45 Degree Data) Find the date max value between B6:B50
2) Select the max value row and convert the content in values (for example if the formula in row gives out $4500..I would like to take the formula out and convert $4500 in value field)

I am new to macros and have spend a lot of time to figure it out, but couldn't.

Please help me..!!

Thank you in advance.

 

[asv83]

https://www.dropbox.com/s/21ddro2yuhyfx3v/Update 1.xlsm?dl=0

 

[Rick Rothstein]

[table="width: 500"]
[tr]
[td][/td]
[/tr]
[/table]

Yes, Same Row in the same sheet.
So if the Max value is cell B10 then I will be copying Row 10 and pasting it at the same spot, Row 10. basically to get rid of the formula and let the value exist.

Seems strange to me that you would convert the live formulas for the maximum to constant values while leaving all of the other live formulas as is. I am also not sure why you told us you had two sheets in your first message given you are only going to affect one of them. However, if that is what you want...

Sub ChangeMaxRowToConstants()
  With Sheets("45 Degree Data")
    With .Range("B6:B50").Find(Application.Max(.Range("B6:B50")), , xlValues, xlWhole).EntireRow
      .Value = .Value
    End With
  End With
End Sub

 

[MARK858]

Seeing as you are using non-US dates and in a custom format try...

Sub MaxFind()
    Dim rng As Range, MaxVal, rFound As Range, r As Long

    Set rng = Sheets("45 Degree Data").Range("B6:B50")

    MaxVal = Application.Max(rng)
    r = Application.Match(MaxVal, rng, 0)
    Set rFound = rng.Cells(r, 1)
    rFound.EntireRow.Value = rFound.EntireRow.Value

End Sub

 

[asv83]

Mark: The macro you provided worked..!!! Thank you soo much.

Rick: Sorry for the confusion. There are 2 sheets that I plan to run the macro. At this point, I was asking help on one sheet and plan to duplicate the macro to work on another sheet.
The reason I want to convert values to constants is because, the sheet will be updated monthly and I do not want the values of previous months to change based on the formula.
The macro you provided shows the error message "Object variable or With black variable not set". I want to learn how to work with macros and I think it will be a good assignment for me to fix this.

Thank you to both of you..!!!

 

[MARK858]

The macro you provided shows the error message "Object variable or With black variable not set". I want to learn how to work with macros and I think it will be a good assignment for me to fix this.

It is not easy to fix. The error you are getting is because Find is very sensitive to it's search criteria and VBA only recognises US dates and so it is not finding the cell with the criteria.

You need to force a conversion, which is hard with a custom format rather than a recognised date setting.

So if your regional settings aren't in US then it sometimes is a lot easier to use another method.