Use Of Square Brackets In Vba Excel 2003 


Use Of Square Brackets In Vba Excel 2003  Excel 
View Answers 
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:
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
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 Tutorials
Scale Data for Printing in Excel  Fit more onto a Printed Page
This tip shows you how to fit more Excel data onto a page for printing. This does not change the size, shape, or lo ...
This tip shows you how to fit more Excel data onto a page for printing. This does not change the size, shape, or lo ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
No more #DIV/0!
It is a frustration if your Excel calculation results show as #DIV/0!, especially if you are expecting to use this ...
It is a frustration if your Excel calculation results show as #DIV/0!, especially if you are expecting to use this ...
Performing Calculations in Excel  Order of Operations Explained
In this tutorial I am going to cover how to perform calculations in Excel. Calculations in Excel follow the same ba ...
In this tutorial I am going to cover how to perform calculations in Excel. Calculations in Excel follow the same ba ...
Helpful Excel Macros
Excel Macro to Save a Specific Worksheet as a New File
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
 This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Excel Macro that Searches Entire Workbook and Returns All Matches
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Combine Multiple Workbooks into One
 This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
 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
 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
 This free macro will open the Microsoft Outlook program on your computer. You do need to have this program first. This
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 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!
=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
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]
"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 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[[(2hydroxyphenyl)methylene]amino]but2enedinitrilato(2)N2,N3,O2,O3]nickel [64696986]
I want 64696986 to appear in the 2nd column while the the rest remains in the first column.
Any help will be appreciated.
Thanks
Best regards
Santa
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[[(2hydroxyphenyl)methylene]amino]but2enedinitrilato(2)N2,N3,O2,O3]nickel [64696986]
I want 64696986 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:
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!
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
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
Hello,
I am looking for a piece of VBA code that will search for a specific character and then look at the character before and after this specific character for another character....
For example....
Cell contents = worldwideweb[.]co[.]uk
I want the VB code to search for the full stop and then look before and after the full stop for the square brackets. If the square brackets do not exist then replace the full stop with '[.]' but if the square brackets do exist then leave the URL alone.
Thank you.
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.
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?
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?
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.
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
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.
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?
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,
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.
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.
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,
(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 addin).
4. I tried Sheets.Add, but I get an "Applicationdefined 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 addin 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'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 addin).
4. I tried Sheets.Add, but I get an "Applicationdefined 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 addin 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
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
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:
The problem is that Access insists on changing the queries  sometimes just when the database is closed and reopened.
It removes these subSELECT 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.
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 subSELECT 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.
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
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
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"
e.g. If a cell contains:
"This is an [example] cell"
It would change to
"This is an <exampleexample> cell"
At the end of one of my macros, I am deleting two source sheets of data and for some reason it's not working, I think it has to do with the () in the name of the sheet, I tried enclosing that portion in brackets but no luck...
Code:
It errors out on that line every time, even when I try to surround the (AVUPDATEOH001) portion in brackets, and the parenthethes in brackets. Any suggestions, and maybe a wise word on what I did wrong? I'm trying to learn so one day I can give great advice rather than just ask for it
Code:
Sheets("(AVUPDATEOH001) CURRENT PBUSE").Select ActiveWindow.SelectedSheets.Delete
It errors out on that line every time, even when I try to surround the (AVUPDATEOH001) portion in brackets, and the parenthethes in brackets. Any suggestions, and maybe a wise word on what I did wrong? I'm trying to learn so one day I can give great advice rather than just ask for it
I have created a reference table that looks like this:
RFQ Purchased in Conversion Factor ft m 3.28083 m ft 0.3048 in ft 12 mm ft 304.8 mm m 1000 square foot square metre 0.09290304 square metre square foot 10.76391 lb. kg 0.4535924 kg lb. 2.20462 ft ft 1 m m 1 in in 1 mm mm 1 square foot square foot 1 square metre square metre 1 lb. lb. 1 kg kg 1 unit unit 1
What I want to do on another page is fill in values from the first two columns in side by side cells and have a third cell recite the value from the third column. I believe what I am looking for is a pivot table and the getpivotdata function but I am confused haow to go about using it.
RFQ Purchased in Conversion Factor ft m 3.28083 m ft 0.3048 in ft 12 mm ft 304.8 mm m 1000 square foot square metre 0.09290304 square metre square foot 10.76391 lb. kg 0.4535924 kg lb. 2.20462 ft ft 1 m m 1 in in 1 mm mm 1 square foot square foot 1 square metre square metre 1 lb. lb. 1 kg kg 1 unit unit 1
What I want to do on another page is fill in values from the first two columns in side by side cells and have a third cell recite the value from the third column. I believe what I am looking for is a pivot table and the getpivotdata function but I am confused haow to go about using it.
I would like to search all of column A for text in cells in column B that is surrounded by [square brackets].
For example, I have in cell B1 the text "This is an [example] cell".
In cell A276 is the value "example"
...So in this case, the argument is true. If column A did not contain "example" anywhere, the argument is false
Using conditional formatting, I would like to highlight all the cells in column B that contain bracketed text that does NOT appear in column A at all. Note that cells in column B may contain more than one set of brackets.
Is this possible?
For example, I have in cell B1 the text "This is an [example] cell".
In cell A276 is the value "example"
...So in this case, the argument is true. If column A did not contain "example" anywhere, the argument is false
Using conditional formatting, I would like to highlight all the cells in column B that contain bracketed text that does NOT appear in column A at all. Note that cells in column B may contain more than one set of brackets.
Is this possible?
I have an update I am attempting to improve. Luckily, each row of data has a vector number that I can use as a reference in my data moves. The problem is, the source of our data sometimes puts brackets around the vector numbers.
For example:
Our table's vector numbers are like this: v41690974
Our source's vector numbers are like this: (v41690974)
The source though, doesn't always output their data like that, sometimes the brackets are not there. I can use VBA to remove the brackets, here is the code:
Code:
The problem is, if they don't have the brackets, the macros will remove the last number and the "v" from the Cansim number. Is there something I can do that checks to see if there are brackets? Then if so, remove brackets? If not, carry on to next line in VBA?
For example:
Our table's vector numbers are like this: v41690974
Our source's vector numbers are like this: (v41690974)
The source though, doesn't always output their data like that, sometimes the brackets are not there. I can use VBA to remove the brackets, here is the code:
Code:
Sub Macro2() Application.Worksheets("Insert").Select For Each c In Range("SourceCansim") c.Select If WorksheetFunction.IsText(c.Value) Then c.Value = Left(c.Value, Len(c.Value)  1) If WorksheetFunction.IsText(c.Value) Then c.Value = Right(c.Value, Len(c.Value)  1) Next c End Sub
The problem is, if they don't have the brackets, the macros will remove the last number and the "v" from the Cansim number. Is there something I can do that checks to see if there are brackets? Then if so, remove brackets? If not, carry on to next line in VBA?