A Macro With Message Box To Rearrange Text In A Cell

May 13, 2009

Is there a quick Macro I could use to rearrange text in a cell. For example our organisation list all the departments in an unconsistant way and it makes finding the departments hard using A-Z. For example some department will say "Department of ...", "Dept of ...", Division of ..." or "Div of ...". (Obviously excluding the quotes).

What I wanted to know is this. If you highlight the range you wish to work with, could you use a message box to ask you what text you wish to edit and then automatically search the range and make the changes.

For example search for "Dept of " (note I would need to keep the space after of otherwise I would have leading spaces) so that "Dept of ABC" becomes: "ABC, Dept of" and say "Division of XYZ" becomes "XYZ, Division of"

View 4 Replies


ADVERTISEMENT

Rearrange Sheets Name By Using Macro?

Mar 13, 2014

I used this code to sort sheets,

Code:
Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")

[code].....

now if my sheets name is like this 001_Tommy, 002_Alex, 003_John, 004_Daniel, 005_Alan,......., and I want to sort sheets name after the underscore "_", arranged like this, 005_Alan, 002_Alex, 004_Daniel, 003_John, 001_Tommy,......, how to do this?

View 2 Replies View Related

Macro To Rearrange Data

Dec 6, 2007

I need to write a macro which rearranges my data for me. The data looks like this;

Data______________Unit_____Jan06_____Feb06_____Mar06___(etc)
Coal consumption___tonnes
Coal energy________GJ
Electricity__________Mwh
Production_________tonnes
etc

This macro will need to be applied to a number of sheets with different time periods and different types of "Data". What I want to do is write a macro that will transform the data to look like this (where xxx is the data entries);

Coal consumption___xxxx____tonnes___Jan06
Coal consumption___xxxx____tonnes___Feb06
Coal consumption___xxxx____tonnes___Mar06
Coal energy________xxxx______GJ____Jan06
Coal energy________xxxx______GJ____Feb06
Coal energy________xxxx______GJ____Mar06
etc.

So that I can easily import it into access. I know basically how to do it, but I can't automate it to apply to a sheet with any stretch of data fields or types of data.

View 9 Replies View Related

Using A Macro To Extract And Rearrange Data?

Sep 6, 2009

I have a big collection of spreadsheets that are all laid out in the same fashion: from A2 down are the names of different individuals (one name per row). Column titles (from B1 right) are the names of unique qualifications that these individuals possess. In the cell where a row and column intersects is the date at which this qualification was awarded. If this doesn’t make total sense, I’ve attached a very basic version of this to the post (Sheet 1) – although the spreadsheets I’m working with have hundreds of individuals and scores of qualifications, rather than just a couple!

This data needs exporting/rearranging into a format that’s recognisable by another piece of software that we use. In this format, the data is split into three columns: the person’s name, a possessed qualification and the date it was awarded. This means that a single individual’s name may have multiple rows, since a different qualification will be present in each row for that person. An example of this layout is shown on Sheet 2 of the attachment.

View 2 Replies View Related

Excel 2010 :: Macro Or VBA Code To Rearrange Data

Jul 5, 2012

I'm using Excel 2010 and XP. I have data in rows 1 -4 that can not be altered. There are headings in Row 5, B-L & Row 6, F-J.

Data starts in Row 7 and follows the headings. This repeats for several thousand rows. I need a way to move Row 6, F-J and put it on Row 5, M-Q

Row 6, F-J and put it on Row 5, M-Q
Row 8, F-J and put it on Row 7, M-Q
Row 10, F-J and put it on Row 9, M-Q
Row 12, F-J and put it on Row 11, M-Q
....
....
....

Then delete blank rows below Row 5

View 7 Replies View Related

Insert Text From Cell Into Inputbox Message

Apr 21, 2006

I want to be able to specify inside the message the word that is in cell F6. So it would say "You Must Give A Reason For The Amount Of Mgr Voids For Shawn"... shawn being the name in cell F6.

If Range("F9").Value > 50 Then
MyInput = Application.InputBox("You Must Give A Reason For The Amount Of Mgr Voids For This Employee")
If MyInput = "" Then End
If MyInput = False Then End
ActiveSheet. Unprotect ("13792468")
ActiveSheet.Range("F9").AddComment
Range("F9").Comment.Visible = False
Range("F9").Comment.Text Text:="" & Chr(10) & (MyInput) & Chr(10) & ""
ActiveSheet.Protect ("13792468")
End If

View 3 Replies View Related

Show A Message When Cell Text Is Changed

May 16, 2006

In a range of cells (e.g. B26-B40) I have names (first name and second name or first initial and second name). This data is carried from sheet 1 to the second sheet via formula. Some cells may be blank as well.

A VB code to display a message when the user changes the name in any of these cells with data or adds a new name to a cell that is blank, in sheet2

View 3 Replies View Related

Rearrange Numbers In A Cell

Jul 31, 2009

I have a column of numbers that are "dated" like this:

90421

This number represents the date: April 21 2009

Is there a way to rearrange the numbers in the cell so I can then format as a date that looks like this: 4/21/2009?

View 11 Replies View Related

Rearrange Cell Contents

Oct 17, 2012

How do I turn cell contents 20121015 into 15/10/2012 and as a date format.

The original cell value starts out as below as a filename that contains the days date:

C:FixedHoldings Report20121015 - holdings Main.xls

I then use this to turn it into 20121015:

Code:
Range("D16").Value = Mid(Range("D16"), 25, 8)

The value will always be in a similar format but the dates will change.

View 9 Replies View Related

Find Text, Go To Found Cell & Show Message If More Than 1 Occurrence

Oct 1, 2008

I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.

My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.

Here is the code I presently have for this part of my macro.

Sub GetOpenFileName()
strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")

If strFind = vbNullString Then Exit Sub

With Sheets(strSheetsMainCompProfile)
If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then
MsgBox strFind & " cannot be found on this sheet"
Else

View 9 Replies View Related

Search Multiple Strings And Rearrange Cell Value

Oct 28, 2013

I have some 100k+ cells with values as "ID_code - Value_1 - Value_2 - Value_3". Lets say this is sequence A.

"ID_code" is a fixed 6 digit value. "Name_1", "Name_2" and "Name_3" are variables and they also vary in length, but they never contain a sign "-". Sign "-" is only used as separator between these four values.

Now, VBA code that would rearrange all the values in a selected column from the one as listed above to a different sequence B, for instant:

Value_2 - Value_1 - ID_code - Value_3

View 3 Replies View Related

How To Get Automatic Popup Message When Cell Text Is Entered To Column Range

Jul 10, 2013

I have an excel file with many worksheets. I want to get a pop up message providing definitions of different subjects when they are entered from a drop down list in a specific range within a column to improve the users understanding of the subjects.

Worksheet 1
Range (where I want the pop up message to be valid): J85:J385
Subjects from drop down list in specified range: "x,y,z"
Pop up message: "Definitions of x,y,z"

Do I have to make a new module, or write the macro in the selected worksheet? What should be the settings of the macro (general, worksheet, declarations etc.)

View 7 Replies View Related

Macro Sub Auto_open() Add Cell Info To A Welcome Message

Jan 19, 2009

When I open my workbook I get a popup that says: Welcome Back...
Using a module with the following
Sub auto_open()
MsgBox "Welcome Back..."
End Sub

I want to have it say: Welcome Back, Phixtit

Where "Phixtit" comes from the cell "C15" on my "INFO" sheet.
Example:
='INFO'!$C$15

View 9 Replies View Related

Macro To Find First Empty Cell In Column And Display Message Box

Dec 17, 2012

I have a spreadsheet and in column D, I have look up formula which looks up value from another tab. What I want to do is if the look up finds #N/A or blank cell. There should be a message box appear warning that all the cells in column D might not be updated properly.

View 4 Replies View Related

If Cell = "ERROR", Need Pop Up (user Form?) Message With A Macro.

Jun 24, 2009

If cell K6= "ERROR" I need a message to pop up on screen with the message "Wrong Entry click OKAY to find, Click CANCEL to ignore". Clicking OKAY would simply go to worksheet named "MONITOR". Clicking Cancel would just clear the pop up from on screen.

View 5 Replies View Related

Macro To Automatically Convert PrtScr Image Based Text To Real Text In Cell

Mar 22, 2014

Programming Excel VBA Macro to do OCR (text recognition) from a prt scr screen capture image and input the text into cells. Currently my Excel file has a push-button, and upon clicking on it the macro pastes into Excel the current clipboard image I have created by pressing prt scr while in another program. The macro then crops the image to the region with the applicable text. I have to then manually type the text I see in image format into the appropriate cells.

the VBA coding to automate this? I'd like it to use the clipboard image and run it through OneNote OCR, after which the applicable text values are automatically entered into the cells. Ideally the code will first crop to the region with the desired text before it does OCR. If this is not feasible, it will need to incorporate a method (keyword search?) to hone in on the desired text after the entire prt scr image has been OCRed.

View 8 Replies View Related

Message Box For Text

Dec 3, 2007

I'm trying to setup vb message box code on a group on cells so it looks at cells that doesn't belongs to 'ortho'. I want the message box to appears when it's not 'ortho'.

Cells between A1 to A10 will show ortho, but if one of those cells contains a different text when entered (a human error), this is where a would like the message box to appear saying something like 'Title name "Error Group"; 'message saying "this person is not part of this group"

View 9 Replies View Related

Macro To Read Just The Text In A Cell Containing Text And Numbers

Apr 10, 2014

I need to get a code that will just read the text in a cell that contains text and numbers example abc123 I want it to only read the abc as the numbers can change and cant write them all into my macro all the time.

View 9 Replies View Related

Send SMS Text Message With VBA

Sep 20, 2007

Does anyone know if there is a way of sending SMS text messages from excel?

And if there is can this be automated from within VBA so that the OnTime method could be used?

View 7 Replies View Related

Bold Part Text In Message Box

Feb 29, 2008

I've got a messagebox in my VBA code and I would like to bold the title of the filename that I've stored in a variable. My message box code (even though you probably don't need to see it) is as follows:

MsgBox ("There has been error. Please check the file - " & strFile & " and make sure there are no #REF errors." & vbCrLf _
& "If there are, correct the errors, save the workbook and regenerate the report. If the error still persists, reboot your machine and try again.")

Is this possible, and if so, how would I go about bolding the strFile variable?

View 2 Replies View Related

Suppress Message When Macro Called By Another Macro

Aug 15, 2007

I have a Macro (AutomateReport) that, among other things, calls and runs a macro (ReportStep1) in another workbook.

ReportStep1 includes three prompts asking the user to press "OK" to confirm that the data included is correct. When I call ReportStep1 as part of my larger macro I would like to suppress these prompts or automatically respond "OK" so that I don't have to be bothered with them.

SendKeys works sometimes, but I am hoping for a more reliable method.

View 3 Replies View Related

How To Send A Range Of Cells In A Sms Text Message By Using VB

Sep 6, 2009

How to send a range of cells in a sms text message by using Visual Basic for Applications in Excel?

BTW: I really need this to work for my job! So any feedback would be greatly welcomed. Please read below to understand what I'm trying to do. I need to be able to send my guys their daily goals via sms text message:

The following works great for sending a range of cells in a email message: ....

View 14 Replies View Related

Data Validation Can Be Used To Enter Text Message?

Jul 12, 2013

Can data validation be used to enter a text message if no numeric value is entered in the cell?

I want to force the cell to have to be populated with a number and if not show "--".

View 3 Replies View Related

Highlite Textbox Text After Error Message

May 15, 2006

iam trying to hightlite the textbox text after the error message.

Private Sub txtRoofWidth_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(Me.txtRoofWidth.Value) Then MsgBox "Use numbers only", vbCritical, "RoofWidth"
With txtRoofWidth
.SelStart = 0
.SelLength = Len(txtRoofWidth)
End With
Frame23.Visible = False
End Sub

View 5 Replies View Related

Wrap Text If Input From Message Box Is Over 100 Characters

Jun 17, 2007

A user inputs a string into a message box. A particular cell on a worksheet is then given the value of this string. I would like to have this cell automatically format to Wrap Text if the string is longer than 100 characters and skip to the next line after each 100 characters. However, if 101th character is part of a word, then that word would be on the next line. If the string is less than 100 characters then the cell is simply given the value of the inputted string ( I know how to do this )

View 3 Replies View Related

Rearrange The Data

Jan 1, 2010

I've the data like this

Item 1,Item 2,Item 3
Product 1a,Product 1b,Product 1c
Product 2a,Product 2b,Product 2c

And I want to rearrange like this
Product 1a
Product 1b
Product 1c
Product 2a
Product 2b
Product 2c
Product 3a

View 9 Replies View Related

Get Certain Text From Email Body Message And Paste It To Excel

Jul 19, 2013

I am having trouble with copy-paste-from-outlook-to-excel routine. Is there a way where I can get a certain text (or numbers, perhaps) from an outlook email message and transfer it to an excel? It is fine to copy and paste one or a hundred emails, but it already exceeds 30 thousand email messages. I just wanted to know is there is a way for me to simplify my work through VBA.

Here is a sample of the email body:

"The lead with phone number 1231234567 is in the federal DNC list and has been rejected. We apologize for any inconvenience this may have caused. This email was automatically generated, please do not reply to it."

In this email body, i wanted to get only the number 1231234567 and paste it in the excel.

View 2 Replies View Related

Rearrange Particular Data In Spreadsheet

Jun 11, 2014

Looking to re-arrange particular data in attached SS. Sample has the highlighted data which is then converted to the result SS. Have many rows with variable lengths for each record.

View 5 Replies View Related

Rearrange Data Into A Certain Format

Nov 24, 2007

I am seeking the expertise of you knowledge folks to advise me whether it is possible to create a "script" in Excel to jumble up certain letters in an entry?

Let me explain. What I have are 4 separate columns with data in them. I need to somehow rearrange them into a certain format, like an encryption I suppose.

Here's an example of some data I have :

COL 1 COL 2 COL 3 COL 4
ABCD EFGHIJ KLM NOPQ

What I need to do is jumble those up so they appear in the following format:

COL 1 COL 2 COL 3 COL 4
CDAB GFEHIJ LKM NPOQ

Is it possible to do this in Excel? You're probably wondering why I don't do it manually, but there are over 20,000 records that need jumbling up and obviously that would take a very long time.

View 10 Replies View Related

Rearrange Data Supplied By Others

Aug 28, 2009

I have been supplied a list of data (company name, address, tel no, email ect.) unfortunatly the data is not in the correct order. There is about 300 companies on the sheet.

Is there a way i can rearrange the data, i have attached a file to show the end result i am trying to acheive.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved