Macro to convert some formulas into values?

[tonywatsonhelp]

Hi everyone,

OK I'll try and explain this the best way I can,

I need a macro that when run will look along Row 55 until it finds the word "Today" in one of the cells, then convert all the Cells from Row 56 (Starting in column B) to Row 86 from formulas to Values?

Basically I have very simple formulas in Columns B to DZ,
In Row 55 I have a simple formula to put the word Today into one of the cells in row 55,
Now I need to "Fix" all data before the word "Today" in Row 55 so it wont get altered .

So if you imagine Row 55 has Today in Cell Z55 then I want the macro to look along row 55 until it comes to column Z the convert all the cell from B56 to Z86 from formulas into values.

But if it looked along and the word "today" was in Cell AH55 it will do this up to AH etc.

I really need help on this, been stuck on it all day.
Thanks

Tony

 

[JoeMo]

Sub IfTodayThenValues()
Dim Col As Long, F As Range
Set F = Rows(55).Find(what:="Today", after:=[A55], LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlNext)
If Not F Is Nothing Then
       Col = F.Column
       Range("B56", Cells(86, Col)).Value = Range("B56", Cells(86, Col)).Value
Else
       MsgBox "Today not found in row 55"
End If
End Sub

 

[Trebor76]

Hi Tony,

Try this:

Option Explicit
Sub Macro1()

    Dim rngFoundCell As Range
    Dim strMyCol As String

    Set rngFoundCell = Rows(55).Find(What:="Today", LookIn:=xlFormulas, LookAt:=xlWhole) 'xlWhole is a non case sensitive exact match (i.e. will find Today or today). Change to xlPart for part match (i.e. Today's or today's)
    
    Application.ScreenUpdating = False
    
    If Not rngFoundCell Is Nothing Then
        strMyCol = Left(Cells(rngFoundCell.Row, rngFoundCell.Column).Address(True, False), Application.WorksheetFunction.Search("$", Cells(rngFoundCell.Row, rngFoundCell.Column).Address(True, False)) - 1)
        Range("B56:" & strMyCol & "86").Value = Range("B56:" & strMyCol & "86").Value
        MsgBox "The formulas in the range B56:" & strMyCol & "86 have now been conerted to values.", vbInformation
        Set rngFoundCell = Nothing
    Else
        MsgBox "The text ""Today"" was not founfd in row 55.", vbExclamation
    End If
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Trebor76]

Duplicated and potentially solved here

 

[Rick Rothstein]

Duplicated and potentially solved here

@tonywatsonhelp

Tony, you have been posting in this forum for quite awhile now and should know better... do not post the same question more than once. You may not see this as a problem, but those of us who volunteer answering questions here do see it as a problem. You can't imagine how annoying it is for a volunteer to read a question, research background material, test sample code and then formulate and post an answer to the original question only to go to another message and find it is the question he/she just answered was already answered there either with the same solution or a better one. Do you know what that ultimately means... that volunteer wasted his time answering your question when it was no longer needed thus robbing him/her of using that time to help another person who like you was looking for help. Another reason not to post duplicate messages is it gives you a better ultimate answer. Why? Because when everyone who wants to help you can see what everyone else has posted, they can make comments/corrections that others may have missed or they may see a modification that can make a good solution that was posted even better. Finally, something else for you to consider... if the volunteers see that you are continually doing multiple posting, they will tend to avoid your messages altogether.

 

[tonywatsonhelp]

First let me say sorry for making any of you feel this post has been repeated in another post, I have gone to great lengths below to try to explain why this happened (So please have a read) and I apologies if any of you feel you wasted your time helping me today, but let me assure your all your help has not been in vain, it has all been needed and I'm truly grateful to everyone who has helped me.

JoeMo, Thank you, that does exactly what I needed I can't thank you enough.

Trebor 76, Thank you very much, the addition of a message box is going to be a real help and something I never thought of so thank you very much as well.

As for the Thread being duplicated, I'm sorry if you think this is the case but my requests where quite different in my mind,
Maybe to you guys this is "The same as my other thread" but to me they are worlds apart, for example, this thread needs all the formulas up to a certain name converted into values which is exactly what it does, my other thread only wants a small amount of data copied into another area, to me, a very different request as far as im concerned. (And both I needed help on)

Now to make sure I'm not being stupid,, I've compared the VBA code for the 2 threads and I think they are worlds apart! I certainly could not have edited one to create the other, and I do a lot of editing of the codes people help me with so I can use them again on other projects, but this would have been beyond my knowledge. Now to you guys that might sound crazy, but if I can use an analogy!

I'm Sure the Spanish for, "Can you show me where the local toilets are?" uses a lot of the same words and sounds very similar to, "Can you tell me where the local bars and restaurants are located please?" so if your quite fluent in Spanish and know the words for Toilet, bars, restaurant etc. you could probably be helped with the Spanish for the first sentence and work out the second using logic and your own understanding. but if you only know a very small amount of Spanish, and you didn't want to make lots of mistakes, you would probably ask someone to help you with both sentences, even though they may be similar, they are still very different.

The similarity's here are they both require going along a line and looking for a name, then doing something once its found that name, and this is because my brain works in very similar ways when I'm trying to find solutions to problems, so I adopted the same idea of identifying the rows with a header that can be searched for, the "Today" one is so all data before today is no longer a formula, something that was vital for other calculations I was doing in the book and the second is to copy and paste one block of data out of what will be a long string of names and their data and bring it into the second sheet for analysis. and to me these are both very different requests.

I'm not saying you are wrong if you feel the post was repeated, clearly you would not have made this statement if you didn't feel that way so sorry for this, with your skills these two needs are clearly the same, but for me, I felt I needed help with both methods so I asked for both.

I will of course say SORRY, for it was not my intention to upset or annoy any one, I wouldn't know what to do without your help so please don't let this put you off helping me again as you guys are amazing and I really do appreciate all the help everyone has given me.

Once again, thank you so much for your help and I'm very sorry for the misunderstanding.

Tony