I have created (via recording) a very simple macro in Microsoft Word to convert a fraction of the form a/b to the form where a is directly over b with a horizontal line between them (like how you would actually write it in math class). Before I send it to my co-workers, I want to beef it up a little.
Right now, it will only work if a and b are integers or if a and b are words or phrases with no spaces (i.e. change/time will convert correctly but the phrase change in x/change in y will not). Also, it would be great if it could work with parenthesis (i.e. (2x-1)/(2x+1)).
Just so you know, I am very comfortable working with code in order to come up with a beefier macro. However, Word uses Visual Basic and I'm afraid I don't know much about the syntax of that code. So, if any of you know of a site I could view to learn a little Visual Basic or if you know a site that already has good code already written for this purpose, I would be much obliged.
I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.
Which works fine if there is a value in cell O, but if not, then a semi-colon appears at the end of the string. It also seems incredibly clunky.
Basically, I'm struggling (being a total n00b) to get the semi-colons in the right place, blank cells to be skipped or not included, and for there to be no semi-colon after the last value.
is there a way to reduce spaces between text stings to one space only when there are many spaces? To make it worse, the number of spaces between the text strings vary. I am using Excel 2010.
I am having a ton of trouble with this. I am trying to add a single text row in between existing data. I have tried multiple loops to try to place an array into Column G, but I cannot figure it out. Is there some coding that will produce the sort of result below in column G?
I have a list of names I used in data validation (dropdown list)....I need to add more names to the list....What is the best way to change the range to include new listings? Is the offset function suitable for data validation? How do you use the Offset function in data validation?
I have two columns of data and cannot get the column to change the format to display as currenty or accounting, with a currency £ sign and comma separator...I select it all and go to Format cells to do the changes, but it does nothing, at all!
I am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
In my sheet I have a range on the go called 'cells_replen', I then need to search through the sheet and if a cell matching a given criteria is found I want to add it to the named range. The idea is that when I've done all the adding I can just select the range and then go back through the cells later on without searching for them all over again. The first code that declares the first cell I need in the range is:
I have attached excel with the logic I am using, but I have a problem and needed logic to update to make sure it is not inserting same data (duplicate data).
Please update the logic so that if it is having same data in sheet 3 it should not insert or delete duplicate.
Code: Public Sub CopyData() Dim ws As Worksheet, bi As Byte, vData(1 To 9), bi1 As Byte, vData1(1 To 9), bi2 As Byte, vData2(1 To 9), bi3 As Byte, vData3(1 To 9), bi4 As Byte, vData4(1 To 9) Set ws = Sheets("Sheet2") For bi = 1 To 9
The purpose of which is to isolate the sheet name from the cell reference, which is typically like: 'Bob Sheet'!$B$9
This works fine where there are no spaces, but 'dies' with a runtime error when there are. With the apostrophes I assumed it would work around spaces in the string but I can't get it to work.
In our language the thousand groups between numbers are separated with a space, like 2 345 345. (so we do not use , or . ) How can I do it in a macro, is there any possibility?
How should I change this: MERGEFIELD brutto # ### # ## ### Ft.
(I know this is without braces, I cant copy them). so this gives me a result 2345678 Ft. and I want 2 345 678 Ft.
I have a excel spread sheet, which consists of huge numerical data. I have Huge data in Column “C1 to C350” and each cell Contains numbers like “ 456 584” and “5899 528”. So, I need a macro to give enter instead of space between the numbers.(i.e. 456(here enter should give)854).
in creating a macro that will remove all frontspaces, trailing spaces from entire sheet + remove characters like (), *,-, &, @,/,',;,. from columns E and F,
in col E and F there should either be string or numbers, everything else should be removed.
I'm writing quite a large electronic logging program using excel and vba and have hit a snag in one of my subs which reads from a text init file. Let's take a look at the code.
Private Sub ReadInit() 'Opens txt init file, looks for target list, splits line by commas and enters into array
Dim FileNumber As Integer Dim TargetsRead As Boolean, LastFileRead As Boolean
Set ObjFSO = CreateObject("Scripting.FileSystemObject") Set objFile = ObjFSO.OpenTextFile(SaveDir & Init)
TargetsRead = False LastFileRead = False Do Until objFile.AtEndOfStream 'do until end of file
I'm working on a sheet with about 10,000 rows and 8 columns worth of data. Most of them are formatted as 12345,12345,12345,23456 how they are supposed to be, but some of them will have spaces inbetween each sequence, or some even a couple spaces, or spaces at the end. For the most part I can use the replacement function with ", " to "," but some of the double spaces throw it off, and end spaces also. Is there a macro that can just search through the selected cells I pick to just remove all spaces so the data falls back onto the commas?
I am using Excel 2007. I have pasted my code below. It works fine unless it has a space, in which case it inserts %20. In this case, sRange2 = ActiveSheet.Range("E11") will always have a space in it since this is where i store a job's name.
Sub Macro1() Dim sRange1 As String Dim sRange2 As String Dim sRange3 As String Dim sRange4 As String Dim sFullPath As String
I am attempting to multiply the value of strings/integers for an output in a function macro. I know it's a simple question, but a simple answer would be suffice. I'm in the middle of a massive brain fart...
for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.
I have a list in Column A" Job List" (the amount of entries will change based on job openings). There are some cells that are blank; randomly throughout. I need to create a new Column B "Current Jobs List" with no spaces. I have to do this weekly and each time I update my "Current Jobs List" from the new data in the "Job List" without any spaces.(I do not want to just do a filter) .
I have come up with this to Trim all of the data from rows 2:30 removing any trailing spaces after the last word in each cell. The macro takes a couple of minutes to run have I got something wrong that is making it run slowly or does the Trim process just take longer?
Sub TRIM_RANGE() Dim myRange As Range Dim myRow As Range Sheets("CAMPAIGNS_2007").Select Set myRange = Range("2:30") If myRange Is Nothing Then Exit Sub Application. ScreenUpdating = False myRange.Replace What:=Chr(160), Replacement:=Chr(32), _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False For Each myRow In myRange.Columns If Application. CountA(myRow) > 0 Then myRow.TextToColumns Destination:=myRow(1), _ DataType:=xlFixedWidth, FieldInfo:=Array(0, 1) End If Next myRow Application.ScreenUpdating = True End Sub
Is there a way to either change this so that it lets me to select the whole area or a way to make a macro to do what this does to one cell?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("M13:IR458")) Is Nothing Then Select Case Target.Value Case "1" Target.Font.ColorIndex = 20 Target.Interior.ColorIndex = 10 Case "Good" Target.Font.ColorIndex = 2 Target.Interior.ColorIndex = 35 Case "Stable" Target.Font.ColorIndex = 2 Target.Interior.ColorIndex = 27......................
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I want to sum number values in a column. I have used "=SUM(C4:C100)" and it works a treat. Now the twist. I have an adjacent column (Column D) and only want to add values in Column C, where a corresponding value is in Column D on the same row.
So for example if I have C5 as $1000 and D5 as 13/5/13, I want to include the $1000 value in the sum of Column C. However, if I have C5 as $1000 and D5 as blank, I don't want to include it in the sum.
I want it to show me that if B3 is bigger than any of the other cells that you should "eat less". It works if a number is entered into every cell, but if a 0 is entered into one of them it comes up with "eat more" when it should be "eat less"
