I have a cell containing a post code (U.K). For example CF678JU however I need it to be formatted to show CF67 8JU. Is there a quick option of setting a formatting rule to add the space instead of me going into each cell and changing... (there's thousands of them!!!)
View 2 RepliesFind a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.
View 3 Replies View RelatedI have been searching for hours trying to find a solution to no avail. What I need to do is make sure that when I press a button on a form, the value in a combo-box should be populated as "text format" not as a number. The reason for this is that when my numbers start with a 0, the leading zero gets dropped and I can't run the rest of my code as the items don't match...
I have tried substituting the "Value" to "text" in : Me.cboItem.value and have tried for hours trying to name dim variables as a String and pass this into the cboItem...with no luck...
VB:
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Order")
'find first empty row in database
iRow = ws.cells.Find(What:="*", SearchOrder:=xlRows, _
[Code]...
The below populated the combo box cboItem:
VB:
Private Sub OptionButton1_Click()
With Me.cboItem
.Clear
.List = Application.Transpose(Worksheets("Item List").Range("List1"))
End With
End Sub
Without using VBA, how can i generate strings say
x1
x2
x3
x4
x5
assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.
Hears a quick question..
Is there away in VB that a cell can be automatically set to time
What i mean at the moment when i need to type in a time i have to type it in as follows 10:00 ( the cell is formatted to time)
What i was wondering is there a way of programming in VB so that when the user types in 1645 it automatically changes it to 16:45
is there a way to have the number formatting in Text() variable? For instance, I want to reference a cell that can change between $1.00 and $10,000,000.00.
Let's say that in A3, the number turns out to be $10,000.00.
When I put in text(a3,"$0.00") it spits out $10000.00. But if I go the other route and go text(a3,"$000,000,000) it displays "$000,010,000.00".
I have an excel sheet, in that excel sheet is a command button, once clicked the command button creates a Word document.
Im trying to get my VBA script to bold certain text that will be in the word document. But all my attempts to bold certain cell contents have failed.
Im not getting any errors from the editor, its just not bolding certain text Ive told it too when the word document gets created.
I have included my script below (And Ive tried the following methods already)
1. ActiveCell.Font.Bold & "This Is Function One, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2) & _
2. "This is Function Two, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2).Font.Bold
3. "This is Function Three, This Text Will Appear Bold" & wks.Cells(ActiveCell.Row, 2) & (ActiveCell.Font.Bold) & _
Sub TransferData()
'This macro transfers the data range to a Word Document
'
'Constants:
'docFullName = The full name of an already existing Word document
'
Is it possible to format a character in a string using vba?
For example if i had a string of GGGFF, and i wanted to make the G's appear as Green font and the F's as Red font.
I have created a couple of conditional formatting rules on the first row that fill the whole row of the table based on whether there a number in one cell or another. (Fill the whole row green if cell E3 has a number in it, or fill the whole row red if Cell F3 has a number in it).
I want to apply this to each row of the sheet but when I auto fill it down, the conditional formats fill but the reference cells stay as E3 and F3. This is causing the entire table to be filled instead of just the individual rows.
I don't want to have to put in conditional formatting rules manually for each row. Is there a way to auto fill down with the reference cells changing to suit each row like a normal formula would?
for some reason every number in excel is automatically formatting as long date. while i can go into the individual cells, columns, rows, sheets and change the format it always auto formats to date at the outset.
View 7 Replies View RelatedCode:
ActiveSheet.Range("$A$1:$AM$14502").AutoFilter Field:=1, Criteria1:= _ "30/06/2013"
When I run the above on my Worksheet to Filter for Rows not equal to 30/06/13 this works fine
I want amend the Criteria1 to a string that is derived off a value in my worksheet I have done this as below where Range("D8").Value is 30/06/13
It is deleting al my data and not keeping the rows with 30/06/13 in it.
Code:
Dim rng As Range
Dim LastQtrDate As Date
Dim LastQtrDateString As String
LastQtrDate = shtControlTab.Range("D8").Value
LastQtrDateString = "" & LastQtrDate
[Code]...
So I have been playing around with the tab colors on my workbook and am trying to figure out the correlation between color brightness and the auto change from black text to white text. I've noticed that if the Green color value is higher, excel is more likely to use the black text. If Blue is high, white. Green takes precedence over Blue and Red is just kind of in its own world.how excel calculates this?
View 2 Replies View RelatedI am trying to auto-populate text in cells in area A, based on data I enter into other cells in area B. I want the area A cells to be for display only, as all editing will be done in area B. The problem is: how do I do this such that the text I write does not get cut off if is longer than the column width? See the attached document for a clear example and description of what I am trying to do...
Excel Q.xlsx
I have a column of cells with compound if statements like this:
VB:
=If(And(N10,K10<>""),"X","")
I'm trying to conditionally format this column of cells so that if the cell changes from this formula it'll turn orange. I've tried different variations of the following:
VB:
=N10<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=mid(N10,1,len(N10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
=left(n10,len(n10))<>"=IF(AND(N" & ROW() & ",K" & ROW() & "<>""""),""X"","""")"
Basically, I'm trying to do a string comparison of the formula contained within a cell (N10) to a string that is assembled on-the-fly. If they don't match, then the cell should fill orange. I think I'm most of the way there, but can't quite get it to work. While this is simple to do with VBA, I'm rebuilding a lot of the functionality of this sheet using the functions available through the Excel UI -- users always forget to enable macros and always save copies as xlsx.
I have a mapping program that uploads data with the date and time but when it exports it the time is converted to just a number string, e.g. 15:12:37 becomes 151237000. Is there way to eliminate the last three zeros and insert a ":" between the second and third, and fourth and fifth characters?
View 8 Replies View RelatedI have written in some conditional formatting to have a cells automatically updated based on a response in a cell above. That is: If a no response is chosen to a certain question, then all other questions in that range are to also be a No response. I have attached the document im working on with an outline of what the problem is!!!!
View 4 Replies View RelatedIn column H I have a list of dates as I will add new rows each day for different records - it's a payment collection. e.g.
18/06/14
18/06/14
19/06/14
19/06/14
19/06/14
21/06/14
23/06/14
21/06/14 is Saturday & 23/06/14 is Monday, Sunday is holiday so that day is no collection but I want Sunday row also be there with date in that column. Currently I am doing it manually.
Is there a method with VBA (or other) to automatically insert a row with formulas & formatting above Monday date after workbook save.
I have data validations, conditional formatting & lot of formulas there. I have other date column also on workbook but I want it happens only with column with H. Also what if I have to change column H in future.
I use a query table to import data into a workbook which works fine
With wsActiveSheet.QueryTables.Add(Connection:=sQueryString, _
Destination:=wsActiveSheet. Range("A1"))
.Name = CStr("main.html?LANG=de&search=true&searchterm=" & sIdentifier & "_1")
.PreserveFormatting = True
.BackgroundQuery = False
.RefreshStyle = xlInsertEntireRows 'xlInsertDeleteCells
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebTables = "9,14"
.WebPreFormattedTextToColumns = True
.WebSingleBlockTextImport = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
One of the fields I import has a value like 100:01 or 10:01 or 01:01 or other combinations (the above ones are the most frequent ones).
Unfortunately excel always converts this field into a date and a time but this is not what I want.
Is there any way to stop excel doing this? I tried to work around it with the text property e.g. formating it to text after I imported the data and formating the entire sheet to use text before
I have many autoshapes in my excel file and I want to use the "VBA version of conditional formatting" one two of these autoshapes. I am saying VBA version because conditional formatting does not work on autoshapes (just found that out). The autoshapes on which I want the conditional formatting to work are "AutoShape 73" and "AutoShape 74" (from Autoshapes > Stars & Banners > Explosion1).
This is how I want the conditional formatting to work:
If the text in the autoshape is "A", fill the autoshape with RED color and bold + white arial 12 font
If the text in the autoshape is "B", fill the autoshape with BLUE color and white arial 12 font
If the text in the autoshape is "C", fill the autoshape with GREEN color and bold + black arial 12 font
I need each row in the Range("A2:G" & iLastRow) to be formatted with the top and bottom border. I'd prefer to go loopless but if that's not possible I will use the loop.
View 2 Replies View RelatedI have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.
View 3 Replies View RelatedHow do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.
A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.
After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.
New words will be added to the sets of words continually as required.
Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.
I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.
The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.
If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.
Happy to consider a different solution if the task can be done better a different way.
Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.
I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. We have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?
View 8 Replies View RelatedI have a cell that gives a title and then a cell value:
="Design and Build "& if(B19>=0,B19,0)
The issue is, I need the B19 value it displays to be an integer. Right now it's giving me about 8 decimal places. I've tried formatting that cell and the cell it calls, but neither has any effect.
i have 2 listboxes with numbers if user has selected choice in both then i want to enable okbutton. i tried in okbutton's mousemove: if listbox1.selected = true AND listbox2.selected = true then okbutton.enabled=true.
View 3 Replies View RelatedSystem is generating IDs like 1-OCTO33, 1-NOVE44, which on exporting in excel converts to 1-OCT-33 & 1-NOV-44. How can this auto-formatting be restricted in excel?
To test, you can try entering in excel 1-OCTO33, it converts to 1-OCT-33.
I am looking for some setting in excel, which would prevent it on exporting to excel. Other options like changing column format to text or using a limiter ' will not work in this case.
I decided to format my data as a table so that excel will auto-fill my formulas when inserting new rows which works quite well. I have one snag though, when trying to insert a new row at the very top (i.e. inbetween the header and first row) and choosing Format As Below, it also applies Data Validation and Conditional Formatting to the header. The inserted row, however, is actually formatted fine and works well.
How do I stop this from happening, and why would Excel do this anyway (as it is effectively applying formatting to TWO rows when only one row is being inserted)?
I have the cell data as below
How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
I still want the original data as I need to check that the splits worked well?
16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5