Use Of Square Brackets In Vba Excel 2003
Use Of Square Brackets In Vba Excel 2003 - Excel
can someone help me. My code works great on my computer but when I bring it to another computer it gives me errors. I think it has to do with my use of square brackets to reference cells. the error that comes up is
can't find project or library
What I'm doin here is going to my template sheet then getting the info from different cells and then later using it somewhere else. in place of using
range and selecting cell I am using square brackets to select a cell. it worked fine on my computer and was a lot less writing
here is a small part of my code
Sheets("Template").Select' go to template sheet
NumberofRowsToSetup = [b15] + 8
'''' NumberofRowsToSetup = what ever number is in B15 on the sheet+8
Numberofdropdown = [b16]
NumberofDateSetup = [a16]
NumberofFieldSetup = [o16]
I'm new at excel an using square brackets in this way might be incorrect, but the thing I don't understand is it work awesome on the computer I created the program on
thanks for any help you give me
Similar Excel Video Tutorials
- See how to create formulas with workbook references. See how to make a formula that looks at a range of cells in another workbook. Learn about the syn ...
Names in Excel 2003 & 2007
- Learn the basics of how to create and use an Excel Name in Excel 2003 & 2007. See how some names from Excel 2003 are not allowed in 2007! See the ...
Basics of Array Formulas
- See how to create a basic array formula. Learn about the syntax, the Ctrl + Shift + Enter method of formula entry and the curly brackets that get ente ...
Store Lookup Tables in Memory
- See how to store Lookup Tables in memory using a named array. See how to use a named array in a VLOOKUP function. Then the table won't waste space ...
Helpful Excel Macros
Open any Program from Excel
- This free excel macro allows you to open any program on your computer from excel. You can open a media player, file vie
I have a column with a series of sentences generated from a server. After the word "Problem" it then gives an error code between square brackets, i.e.
"Problem in Error  please check in [ABCD]"
The error codes between square brackets are of diff lengths, and I need to export into a diff column the value between the square brackets AFTER the word "Error" (not the latter one). Is this possible please?
I am using the following formula which searches for instances of [square brackets] around text in column A and returns the same text but with <angular brackets> around it:
=IF(AND(SEARCH("[",A1),SEARCH("]",A1)),LEFT(A1, SEARCH("[", A1)-1) & "<" & MID(A1, SEARCH("[", A1)+1, SEARCH("]", A1) - (SEARCH("[", A1)+1)) & MID(A1, SEARCH("[", A1)+1, SEARCH("]", A1) - (SEARCH("[", A1)+1)) &">" & RIGHT(A1, LEN(A1)-SEARCH("]", A1)),A1)
This formula works fine, when there is an instance of [square brackets] in the cell in column A.
However, if the cell doesn't contain square brackets, the formula returns #VALUE!; clearly there is an issue with the underlying IF function.
Can anyone offer any solution for this? Any help would be very much appreciated!
I am using a function that replaces [square brackets] found around data in a cell with <angular brackets>. If the function does not find square brackets (and returns an error), it just returns the original cell value.
For example, a cell that says "This is an [example]" will be returned as "This is an <example>".
However, I have cells that contain multiple instances of square brackets. If I run the function on the following data: "This is [an] [example]", it just returns "This is <an> [example]".
The solution I am currently using is just to run the function on the result of the first function. In some instances however, I have up to ten bracketed words in one cell, which makes for a lot of unnecessary functions.
Is there any way of getting the function to repeat until it no longer finds brackets?
Here is the function I am working with:
=IFERROR(IF(AND(SEARCH("[",A1),SEARCH("]",A1)),LEFT(A1, SEARCH("[", A1)-1) & "<" & MID(A1, SEARCH("[", A1)+1, SEARCH("]", A1) - (SEARCH("[", A1)+1)) & MID(A1, SEARCH("[", A1)+1, SEARCH("]", A1) - (SEARCH("[", A1)+1)) &">" & RIGHT(A1, LEN(A1)-SEARCH("]", A1)),A1),A1)
Thanks in advance
I have a column full of strings of data. Some of the data is contained within [square brackets].
"For example, [this] could be [one of] my cell's [contents]."
What I would like to do is have and instances of apostrophes: ' be replaced with a backslash and apostrophe: \' , but only if the apostrophe appears within [square brackets].
"For example, [this cell's] apostrophes [should] be replaced if within [brackets], but shouldn't be replaced if [not within] brackets"
The result would look like this:
"For example, [this cell\'s] apostrophes [should] be replaced if within [brackets], but shouldn't be replaced if [not within] brackets"
Note: there could be any number of [apostro'phes] [withi'n any nu'mber] of [brack'ets]
We switched to excel2003. We are having problems with our excel sheet where
worksheetnames contain [ and ]. It seems that excel changes them to ( and ).
But the application (Quicktest Pro) that generates our excelsheets and uses
them needs those square brackets in the datasheet name.
Does anyone know how we can prevent this conversion when we save the changes
in the cell that we made?
I've just imported a huge column of chemicals and their registartion numbers. The registration numbers are always in square brackets . Problem is that both the name and the registration number of each chemical are in the same cell.
Usually this problem would be easy by using the Data>Text to Column option and choosing the opening square bracket as the delimeter
But the problem is that the square brackets appear in the name of some of the chemicals as well.
The only thing common to the registration numbers is that they are the LAST pieces of information in the cell.
Is there a macro which will find the first opening square bracket from the RIGHT and move all the contents after that bracket into the next cell. Or is there a better approach to this problem
I want 64696-98-6 to appear in the 2nd column while the the rest remains in the first column.
Any help will be appreciated.
Thank goodness i had a coffee this morning already :/
Ok, i have a piece of software that allows me to build queries. Problem is that it doesn't help you much with the syntax, and just 1 thing out of place, or 1 missing bracket etc, and the thing wont work. It also wont tell you what's wrong with the syntax, just that the entire thing is incorrect. I'm putting together a little spreadsheet that'll allow me to do some simple checks, like check that the total number of brackets add up.
The next thing is the tricky bit. I'd like to try & split out unnecessary brackets. (might need some code for this one!) For eg, here's some simple syntax:
([Customer.Change_Of_Address_Date] >= 19032009) AND (([Customer.VF_Exclusion_UK_Email_NH] = "0"))
Note the 2 unnecessary brackets around the 2nd field (a field is always distinguished by square brackets)
Is this possible or is it past the remit of XL?
Does anybody have a UDF handy to extract the text contained between two square brackets i.e.
'='F:\WIP\2007 Files\[Cse5.xls]Schedule 1 Calc'!$D11
thanks in advance
Is it possible to format a text cell so that the user can just type in
a text reference from a catalogue - i.e., letters of the alphabet -
but that Excel then puts in square brackets?
i.e., rather than typing in [A1], the user types in A1 and Excel shows
the cell contents as [A1}.
I am currently translating a client's Excel VBA into TSQL in Server 2005.
In the VBA it makes cell references using the style [cellname].
I've never seen that before.
If you set a break point and hover the mouse over one of these references you don't get to see the value of the cell (because the value is held in a cell not a variable).
I'd be grateful for any tips on working with this style of referencing (do, dont's, piftalls and advantages). Couldn't find any references to this method in the online Help.
I tried replacing all those style of references with Range("cellname") types references and found the application no longer worked correctly.
Do the square brackets do something extra other than reference a cell's value I should know about?
It's a bit of a tricky situation here.
What I've got is database of article citations including Authors, Title, Year etc.
Now what I need to do is find out which articles are not in English. If an article is not in English then all the details (Title, etc) are translated to english and the title is enclosed with square brackets.
However I didn't know that the closed brackets meant this at the time so I had removed them all from the titles before importing the list into the excel database. Since then I've done lots of sorting and aggregating since then I don't want to start all over again, back to before I removed the closed brackets.
However I believe there's another way to get what I need now. I've imported the old version of the references with just one square bracket at the end of the title instead of completely enclosing the title. Each article has a unique ID number (AccNo) which can be used to identify articles between the two databases. What I want to do is:
Look in the database of articles with ending bracket in title ('Square bracket titles' tab in excel) to find ones with a title ending with a closed square bracket ']'
Copy the unique ID number (AccNo) of each of these articles
Find the article with the corresponding AccNo in the main "APMEN Merged and Aggregated Ref" database tab.
Put a '1' in the column AD titled 'Non-English' for each of these articles
So basically I end up with each article in the main aggregated database tagged with 1 if they are non-english. And this is determined by whether or not the corresponding article title has a square bracket at the end in the "Square bracket titles" database.
Attached is a sample, the full database has over 5000 articles.
Any help will be much appreciated!
I have an Excel spreadsheet where cell A1 contains the following string of text:
[text] more text (even more text)
I want cell B1 to contain:
and cell C1 to contain:
(even more text)
Repeat for the rest of the rows.
Basically, for column B I need some formula to parse column A text, search for text inside square brackets and copy it along with the brackets. Same thing for column C, except the text in this column has to be the one inside curved brackets, along with the brackets, of course.
I'm an Excel noob. Cheers.
XPHomeSP3, Excel 97, VS2005,C++
Hi, I have a need to communicate from my c++ dll to Excel.
I can Poke & Request data ok however when I try to run a macro it does not run..
Using Application "Excel", topic "System" or topic "C:\test.xls" and DdeClientTransaction with XTYP_EXECUTE i can submit a DDE command that has no parameters such as [QUIT] but any commands (including RUN) that have paramters in brackets does not run.. eg RUN("MyMacro").
By removing the square brackets i can see the command gets written to the active cell as RUN"MyMacro" ie. the round brackets have been stripped.
Debugging the text from the c++ code i can see the brackets are delivered to the DdeClientTransaction... but seem to be stripped somewhere.
All the references/examples make no mention of not having the brackets or issues in transporting them..
I have tried both unicode and ascii versions of the DDE calls in c++...
Does anyone have some clues or working examples (from c++).
Thanks if you can share some info.. (thanks for reading too
On my sheet is a square of -let's say- 10 x 10 cm. When I print this square to my printer the square measures about 9.9 x 10.8... (I have tried this on two printers)
How can I make sure my square on screen comes out of the printer as a square. ??
I don't really mind about the dimensions of the square, but it has to be a square
Thanx in advance.
I have many word documents (all with the same template), and I am trying to extract the info from all the .doc files and compile them into one Excel database. I have the code done and it works.... but I ran into one problem: The resulting String values in Excel have weird square symbols in them.
Take a look at it in the attachments. I've attached both the .doc template and a general database as a reference.
Please fix my problem: how do I get rid of these square symbols?
Hi, i want to lookup some strings contains square bracket like "aaa[bbc]", but cannot. I find that even if i F5 to search in excel sheet aaa[bbc], i cannot find anything aslo. Can anyone helps? Thanks
Is it possible to write a macros to solve the following issue:
In a cell, there is a sentence containing the open square brackets character: "["
For the next space " " that is found after the "[" character, this space should be replaced with a close square brackets character "]".
The space to be replaced would be an undefined number of characters after the "[".
Only the next space following the "[" character should be replaced with "]". All other spaces should be left as they are.
I hope I explained that OK.
Good afternoon folks,
I am using =cell("Filename",A1) to return the path and filname of a particular worksheet. The returned data usinb the formula is;
Can anybody tell me how to strip the brackets square brackets from the ouput please.
Many thanks in advance,
PS: If any other info is required just shout. :P
(I'm using Excel 97.)
I'm running into a problem adding a sheet to a workbook that was created by downloading a file off the internet.
1. Use a "Export to CSV" feature on a webpage w/tabular data.
2. Choose "Open" with Excel.
3. Now I have a new workbook with one worksheet. I want to add another worksheet (via an add-in).
4. I tried Sheets.Add, but I get an "Application-defined Error"
I think the problem is the downloaded worksheet is automatically named something like report]_2008_03.csv. I've manually removed the brackets and then run the add-in and all is fine.
Is there a way I can automatically rename the sheet? I've tried simply Sheets(1).Name = "New Name" with no such luck either.
Thanks in advance.
I've got a problem that goes as follows;
I have three types of data;
I want to return dogs for all three i.e. remove either square brackets or speech marks.
Assuming that the data starts in cell A2 the formula I put together goes thus;
However instead of following the IF=FALSE path into the next IF statement the formula returns #VALUE! if the data does not contain [.
What have I done wrong?
Thanks in advance
I have 2 scenarios that I am trying to cater for both which work well but I am trying to tweak one to facilitate reporting later on.
The first allows me to copy a sheet to the workbook from a hidden "template" sheet then rename it to the name of the current sheet and then auto number the new sheet in sequence.
Example - Scenario 1
1. Template sheet called My template and hidden
2. Active sheet called (Test)
Copies a sheet to workbook based on template and rename's it to Test 1
Code again would add template sheet and rename sheet Test 2 etc.
Using just the ActiveSheet.Copy After:=ActiveSheet allows me to take the active sheet and then in excel fashion add Sheet name and then number in brackets.
This again is fine but i was wondering if there is anyway of having fixed text within the brackets preceeding the number
Active sheet..........Test 1
Result of copy....... Test 1 (1)
Result of another copy...... Test 1 (2)
I am trying to to get it so I can have a result like this...
Active sheet..........Test 1
Result of copy....... Test 1 (Option 1)
Result of another copy...... Test 1 (Option 2)
The actual copy is perfect but later processes and reporting will be much improved and identified if I could add "Option" within the bracketed number which is being generated by the ActiveSheet.Copy After:=ActiveSheet code.
The other nice thing about this for me is that all sheets are displayed correctly in sequence as the book is built up.
(On site survey form)
I just can't see where I can add the additional text as it seems it's just the way excel deals with the copy and nothing that I can pick apart.
Any thoughts greatly received
Can someone please help me out with this? I have just had some help setting up an Input Box
I have the code below, which works fine apart from I get a little square coming up every time I run the code, as this gives you a line the Chr(10) has to be there. When I tale the Chr(10) out the little square disappears but then I don't get a new line. I have set the formatting to "wrap text" but it is still showing up. I am not quite sure where to go from here, I have also done a ="test"&CHAR(10)&"test" and that worked fine with no little square.
Me.TextBox3 = "Order No: " & Me.TextBox1 & Chr(10) & "Date: " & Me.TextBox2 & Chr(10) & "Stock Order Sales"
Does anyone know how to hide this little square?
I'm currently writing some Access queries in SQL view. The queries contain plenty of Joins that the design view cannot represent, such as:
LEFT JOIN (
SELECT DISTINCT tbl_Res_Audit.nBookingID, dteEditDateAndTime, nUserID
) AS ResAudit1
ON ResAudit1.nBookingID = tbl_RES_RoomBookings.anBookingID
The problem is that Access insists on changing the queries - sometimes just when the database is closed and reopened.
It removes these sub-SELECT joins, puts square brackets in and in one case it even put a ; in halfway through the statement.
This basically breaks the queries - is there anyway of telling Access to leave the queries alone?
I just want it to leave the SQL as I entered it. I don't mind if it takes my formatting out and puts it all in one continuous line - just please, don't remove the JOINS or put square brackets in.
Really hope someone can help with this, cos it's doing my head in now.
I have a spreadsheet containing lots of text. I would like to find all instances of text encased in [square brackets] and replace the brackets with < and >, with the original word duplicated inside.
e.g. If a cell contains:
"This is an [example] cell"
It would change to
"This is an <exampleexample> cell"
Hi there everyone,
I have a workbook which contains links to named ranges that are in
external workbooks stored on the server. When the external workbook is
open the cells show the correct values. When the external workbooks
are closed the cells show #REF. The formulae are obviously correct so
why can excel only read from files that are open? Can anyone explain
this? Also - a small additional query - why is it that when excel
links to an external file it drops the square brackets round the file
name & path if the link is to a named range. The square brackets
appear if the link is to a cell address?
Thanks in Advance