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.

 

[MARK858]

Try the code below, not sure if you just wanted the cell converted to values or the entire row.
If you meant the entire row then uncomment the commented line and comment out the line above it.

Sub MaxFind()
    Dim rng As Range, MaxVal As Double, rFound As Range

    Set rng = Sheets("45 Degree Data").Range("B6:B50")
    
    MaxVal = Application.Max(rng)
    
    Set rFound = rng.Find(What:=MaxVal, After:=rng.Cells(1, 1), LookIn:=xlValues, _
                          LookAt:=xlWhole, SearchOrder:=xlByRows)
    rFound.Value = rFound.Value
    'rFound.EntireRow.Value = rFound.EntireRow.Value
End Sub

 

[asv83]

Thank you Mark

I would the whole row to be copied and pasted as values. I tried the macro you sent and did as instructed. It gives me the following error;

Object variable or With block variable not set

Not sure how to fix it.

Thank you

 

[MARK858]

Try changing xlValues to xlFormulas in the Find (I am assuming it is that part that is highlighted whan it errors).

I have already given instructions for the value part.

 

[asv83]

Sorry Still did not work. May be I am doing something wrong. I tried the following as per our chat;

Sub MaxFind()
Dim rng As Range, MaxVal As Double, rFound As Range


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

MaxVal = Application.Max(rng)

Set rFound = rng.Find(What:=MaxVal, After:=rng.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows)
'rFound.Value = rFound.Value
rFound.EntireRow.Value = rFound.EntireRow.Value
End Sub

Still giving me the same message





****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Sub MaxFind()
Dim rng As Range, MaxVal As Double, rFound As Range


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

MaxVal = Application.Max(rng)

Set rFound = rng.Find(What:=MaxVal, After:=rng.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows)
'rFound.Value = rFound.Value
rFound.EntireRow.Value = rFound.EntireRow.Value
End Sub
</body>

 

[Rick Rothstein]

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)

Where did you want the converted row of values to go? I am guessing Sheet1, but where on sheet 1 (first row, next row after the last filled row, somewhere else)?

 

[MARK858]

I think you will need to post your workbook to a file sharing site like www.box.com (please note that I only download from certain sites) and post the link here (after marking it for sharing) so that I can look at it as I am sure I tested the code (with values not formulas) before I posted it and I will look at it when I get in.

You also need to still state which line you are getting the error on (the error you are getting if it is on the line I think it is normally means that the find hasn't found the search criteria).

And so slow typing on my phone that I missed Rick's response . Please answer his question first.

 

[asv83]

Hi Rick

I want to the values in the same row they are in.. do you share from dropbox? I will post my worksheet in an hour or so.
The reason I am doing this is for a monthly progress. I have the sheet with formulas that meet certain criteria. every month when that certain criteria is met, the values need to become permanent so that the next month the formulas do not interfere with the previous month data. This excel file will be maintained by some of our PMs and I would not like for them to go in and do this step by hand. I am sure one of them will mess it up.

Mark:

the error message pops up when i run the macro and if I use F8 then it pops up at the last line when i press F8 "rFound.EntireRow.Value = rFound.EntireRow.Value" before jumping to End Sub.

Thank you guys

 

[Rick Rothstein]

Hi Rick

I want to the values in the same row they are in.. ?

But on Sheet1, not Sheet2, correct? If so, you realize you will only be copying a single row there (the one with the maximum value in range B6:B50), correct? If you copy it to the same row, that single row might be, say, row 45... all the other rows will be blank... is that what you want? Or am I assuming something incorrectly?

 

[asv83]

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.