Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Use Of Square Brackets In Vba Excel 2003

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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

compile error:
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
Code:

Sub SheetSetup()
         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]
Call ColunmWidth
......


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

mattsgame


Similar Excel Video Tutorials

Helpful Excel Macros

Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
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
Open Microsoft Outlook from Excel
- This free macro will open the Microsoft Outlook program on your computer. You do need to have this program first. This
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the

Similar Topics







Hi

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 [55143] 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"

Any ideas?

Note: there could be any number of [apostro'phes] [withi'n any nu'mber] of [brack'ets]

hi,

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?

Kind regards,

Jo



Hi there

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

Eg:

[2,3'-Bis[[(2-hydroxyphenyl)methylene]amino]but-2-enedinitrilato(2-)-N2,N3,O2,O3]nickel [64696-98-6]

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.

Thanks
Best regards
Santa


Hiya,

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:

Code:

([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?

Thanks!


Hello,

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

should give

Cse5.xls


thanks in advance
Andy


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}.

Thanks.




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!


Hello,

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:

[text]

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

Cheers


Hello,

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

Hi,

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;
Code:

  
 
C:\Users\david.oneill\Documents\[KUK_RSP_UAT_Assesment_Individual_macro2.xlsm]JJN




Can anybody tell me how to strip the brackets square brackets from the ouput please.

Many thanks in advance,
David

PS: If any other info is required just shout. :P




Hi,

(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.

The steps:
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[1].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.


Hello everybody,

I've got a problem that goes as follows;

I have three types of data;
[dogs]
"dogs"
dogs

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;

=IF(FIND("[",A2,1),MID(A2,FIND("[",A2)+1,FIND("]",A2)-2),IF(FIND("""",A2,1),MID(A2,FIND("""",A2)+1,FIND("""",A2)-2),A2))

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

Swarmthief


Hi,

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)
Code then
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.

Scenario 2
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

Example

Active sheet..........Test 1
Result of copy....... Test 1 (1)
Result of another copy...... Test 1 (2)
Etc.

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)
Etc.

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

Dean


Can someone please help me out with this? I have just had some help setting up an Input Box

Ref Post

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.

Code:

  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:
Code:

LEFT JOIN (
    SELECT DISTINCT tbl_Res_Audit.nBookingID, dteEditDateAndTime, nUserID
    FROM tbl_RES_Audit
     ) 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

Stuart