Square Brackets - Vba Notation
Mar 22, 2007I often see square brackets around ranges in code. eg [A1]
if there are any advantages/disadvantages with using this notation, apart from the obvious its shorter?
ADVERTISEMENT
Use Of Square Brackets In Vba 2003
Jan 8, 2010My 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
Macro For Removing Contents Within Square Brackets
Feb 22, 2012(Split cell into multiple rows) I need a macro to remove contents within brackets in a cell.
Example:
A1 contains
[something, separated; a, couple of times; like, this] With, some; other, text
The result should be:
With, some; other, text
I want the macro to remove the brackets and everything within it for all marked cells. (I do not want to use search and replace function since I got a lot of these data and needs to repeat this procedure).
Finding Whether A Number Is A Square, Or Whether Square Root Is Integer
Aug 19, 2009I need a formula to tell me whether a number is a square number, or I can do it so to see if the square root of a number is an interger. So far Iv been doing
A1 = 2
A2 = AND(RIGHT(A1,1)=INT(A1))
True or false outcome is fine, and it has been working fine on some examples, but the problem comes when I have 49, as it is 2 digits long. I've seen a formula similar to find the root symbol, and look at all the numbers upto this point (maybe FIND).
Formula To Ignore The Brackets And The Figures In The Brackets
Feb 27, 2009I have a small problem that I may be able to deal with in another way, but if all else fails then I need some assistance.
I have this formula:
=AND(N3<=$U$1,MOD($U$1,N3)=0)
But unfortunately there will be a time when cell U1 will have the contents of perhaps 12(1). Is there any way to get the formula to ignore the brackets and the figures in the brackets?
RC Notation
Jun 5, 2009I dont know why this wont work
it falls over on the ".Columns(6).FormulaR1C1=" line
Sigma Notation Using VBA?
Mar 21, 2014Similar to this thread: [URL]
I'm trying to come up with a VBA solution to.... Sigma from j=0 to i of: L((i-j)/i)*b*t.
Convert Formula To RC Notation
Feb 22, 2008I have got a list of formulae in column A e.g.
'=SUM(E14:E24)
'=SUM(E14:I14)
'=SUM(J14:N14)
'=SUM(Q14:Q24)
'=SUM(V14:V24)
(formulae is stored as text, i.e. preceeding with an ')
and i am looking for a VBA macro to cycle through the list and give the relative notation in column B i.e.
'=SUM(R[-11]C:R[-1]C)
'=SUM(RC[-12]:RC[-8])
'=SUM(RC[-8]:RC[-4])
'=SUM(R[-11]C:R[-1]C)
'=SUM(R[-11]C:R[-1]C)
Meaning Of Bracket Notation
Feb 9, 2007The following code was posted in another thread:
Private Sub UserForm_Initialize()
Dim a, v, x, ws As Worksheet
Set ws = Sheets("Scheduled Courses")
a = ws. Range("a2", ws.[a65536].End(xlUp)).Value
With CreateObject("scripting.dictionary")
For Each v In a
If Not IsEmpty(v) And Not .exists(v) Then
.Add v, Nothing
End If
Next
x = .keys
End With
With Me.ComboBox1
.Clear ' clear the listbox content
.List = x
.ListIndex = 0 ' select the first item
End With
End Sub
How are the brackets used in the 4th line? I have not seen this notation before. Is this some kind of a shorthand for a Range("A65536") reference?
Data Converted To Scientific Notation
Nov 3, 2008I work for a company that exports data from a website into Excel.
So here's my problem:
The data is usually Part Numbers using numbers and letters. When a Part Number is, for example, 248E12 we have this reaccurring issue. Importing it into Excel automatically converts it to scientific notation (ie. 248E+12 or 2.48E+14 to be exact). "Format Cell" doesn't convert it back because it was auto-converted when "exported". I need a way of fixing this problem without going line by line and doing the math to convert it myself.
I don't have time for that. Some of my files have thousands of lines.
Automatically Formatted As Scientific Notation
Dec 27, 2006I'm trying to filter a long list to look for specific employee numbers. The employee numbers are so long that they have to be formatted as text or they are automatically formatted as scientific notation (they are 18 digits long). For some reason this is causing the filter not to work properly.
Employee ID
006010000000000661
006015000000000082
006015000000000319
006015000000000319
006015000000000552
006010000000000370
006010000000000370
006015000000000921
006010000000000503
006015000000000835
006015000000000834
006015000000000689
006010000000000391
006010000000000391
006010000000000030
006010000000000030
006015000000000718
006015000000000202
006015000000000202
006015000000000202
006010000000000585
006015000000000712
Disable Scientific Notation - Permanently, How
Mar 30, 2007AHH Scientific notation is annoying me, I don't want to see auction #'s in scientific notation. How can I disable the feature in excel 2007?
View 9 Replies View RelatedReferring To Range() Using Shortcut Notation
Sep 23, 2008I have noticed in many of the Excel help files that the developers liked to use shortcut notation for Range reference in VBA.
For example, Range("A1:B10") would be [A1:B10].
I was doing a bit of testing with this, and was not able to make the shortcut notation work with a variable. Does the shortcut method have the capabilities to do the equivalent of Range("A1:B" & LR)?
If it does have that capability, is it just due to force of habit that we always use Range() to refer to ranges, or would there be a more in-depth reason.
Selecting A Range Of Cells With R1C1 Notation
Oct 31, 2008I'm trying to select a range of cells using the R1C1 notation. But I'm making an error in the syntax. I know it's really simple, I just don't know what's wrong.
View 2 Replies View RelatedNotation Of Formula With Range (small Question)
Jul 28, 2009Ok, I want to use a dynamic range in my Vlookup formula.
I want to use this formula: ....
Code Converting Text To Exponential Notation
Dec 26, 2009I have a bit of code that is grabbing only the first 9 positions of an alpha-numeric string. The problem I am running into is that when the alpha-numberic value contains an "E", the code turns it into an exponential number in error.
For example, I have the following in column G: 01877E107000. When I run the code, it turns into an alpha-numeric value. The result should be 01877E107 (in column K). I tried using the text function within the code.
Referencing Sheet Using Cell Notation In Range
Aug 12, 2012repTot = Application.WorksheetFunction.Sum(Worksheets("Revenue").Range(Cells(xx, 65), Cells(xx, 65 + moNum)))
I am trying to debug the above line of code (moNum is just a variable the tells me how many months to extend the range to the right inorder to get year to date sums).
I have gotten to the point that it will run error free if "Revenue" is the Active worksheet when I run the line, but if I am on any other sheet it throws an "Run=time '1004' Application-defined or object-defined error" error. And if I change the range from cell notation to Column-Row, the issue goes away as well.
Conversion Of Scientific Notation To Text Not Working
Mar 13, 2013I have a large file with account numbers in Number format. Before I can import the data into a database the account numbers need to be converted to Text. I use Text(A1,0) to do this in a large file. The data then gets copied into a new Import file. When the data gets copied the account cell reverts back to scientific notation for the long account strings. The import file account column is formated for Text. The only fix is to then double click on each account that is in scientific notation to convert it back to text.
Is there another text formula I can use?
Stop Auto Format To Scientific Notation
Dec 29, 2008I run a macro that imports a table from web page:
[url]
I import only the table with no formatting.
Excel tries to be helpful, and formats some of the cells in scientific notation.
I have tried to format the column to the "Text" format prior to the import with
Columns(2).NumberFormat = "@"
This does not work. If you import the table you can see that rows 1405, 1406, 1407, 1408 all have the same scientific notation value. Their true values should be 0E0, 0E4, 0E8, 0E9 respectively.
There are quite a few other instances in the column when a text value has "E" for the second character that Excel will format on its own.
Excel 2007 :: Macro Switching Formula Into RC Notation
Oct 17, 2011I have a series of macros I have built to automate some report manipulation at my office. One of the macros I built inserts formulas into specific columns. When I run this macro, all the formulas, save one, are populated perfectly into the column they need to be in. This particular formula is swiched over to R1C1 Reference Notation.
In the workbook I built the macro in, it inserts the formula in the correct notation. When I run the macro in a different workbook, this one formula is converted to RC Notation and then is displayed as text (since the workbook is not set to the R1C1 Reference style option).
Is there a bug in my VBA code? If so, how can I correct this?
I use Excel 2007. Macros are saved in my Personal.xlsb workbook. All other forumlas populated by the macro work correctly.
Date Notation: Dates A Represented As A Number On Screen
Jul 14, 2009In a sheet the first collum is defined as a date field. However the dates a represented as a number on screen. How can I change the way it's showed on screen?
Punnet Square
Aug 15, 2006I am doing a cross between two parents, whose possible genotypes are AA, BB, or AB.
I want to use if clauses to look at the parent genotypes, (paternal is listed in column E and maternal is listed in column F) and then give me the possible recombinant genotypes in column G. there are 9 possible combinations (recombinants in parenthesis)
AA x AA (AA)
BB x BB (BB)
AA x AB (AA, AB)
AB x AA (AA, AB)
BB x AB (BB, AB)
AB x BB (BB, AB)
AB x AB (AA, AB, BB)
AA x BB (AB)
BB x AA (AB)
I tried using if clauses, but i don't think i can nest more than 7 cases, and I have 9 different combos.
Index - All Up All Square Feet
Mar 13, 2014I have 6 rows A1-A6(sheetsize), they can be 1 of 8 numbers. (16,14,12,10, 5/8, 1/2, 3/8, 3/16) these are all metal gauges. The cell next to them B1-B6(sheetgauge) has a number that refers to how much sq ft each of these sheet metal sizes has. I would like a cell below A30 to add up all the sq ft in B1-B6 if it is 16 gauge. Then A31 will add up all the 14 gauge, etc.
How is this possible without having a huge formula.
Here is my standard formula, but it wont add the B cells together if more than one of the same sheet sizes.
=index(sheetsize,match(A31,sheetgauge,0))
Too Many Brackets At The End
Jul 16, 2007=IF(AND(A1>=1,A11001,A1=10001,A1=25001,A1=1500001),(A1*0.333)+25000)))))
What is missing? (too many brackets at the end?) absolutely I've just mucked around with the formula given so have done something incorrect I think
A1 will contain Sales and I need to work out how much commission they'll get (ie, sales of between 25,000 & 150000 will get 50% plus $2500
Forcing Excel To Show Numbers In Scientific Notation To A Constant Power?
Mar 8, 2013I need to have all of my number be "x10^-6", but Excel wants to make them, for instance, 7.66x10^-3, when I need it to read "7660x10^-6". How can I force excel to do this?
View 3 Replies View RelatedAddress Separated By TWO Square Characters
Feb 15, 2009An address is separated by the square characters, and usually I would use Text to columns>Delimited>Other>Alt+0010 to separate the lines. When I get to the Delimited screen where you check Other and enter Alt+0010, the keyboard just makes beeping noises and the text preview only shows the part of the address prior to the soft return, as if there is no char(10) delimitation.
Sheet1 I11299 S Taylor St. #12
Shakopee, MN 553792880 Tuva Rd.
Cokato, MN 5532131381 360th Ave
Siren, WI 5487245824 Dupoun Ave. N
Brooklyn Center, MN 554305718 Garden Lane #2
Shakopee, MN 553796583 Hall Ave
St. Paul, MN 5510771615 South 4th St. #2003
Minneapolis, MN 5545481525 6th Ave S #8
St. Cloud, MN 563019715 S Glen DR
Bloomington, MN 5542010340 2nd Ave. S. #344
Waite Park, MN 56387 Excel tables to the web >> Excel Jeanie HTML 4
Square Root With Userform Textbox's
Oct 2, 2006i am trying to use the square root formula to work out the top length of the picture on the userform from the numbers i have in the textbox's.
It is basically a 10x5 rectangle and i need to find the diagonal length
which should be 11.18 but it is returning 10
{} Brackets Disappear
Apr 28, 2009I have a formula
{=IF(OR(D9=M4:S99),"m")}
For instance
This formula works
But when i click in the cell to edit the formula the {} (at the beginning and end) disapear and the formula no longer works.
If i add them back manually the formula still does not work.
How can i edit these formulas without stopping them working.
Deleting Brackets
Nov 10, 2009I have a long list of names with their locations in brackets. What formula should i use to delete the brackets and their contents only?
View 3 Replies View RelatedHow To Extract A Value From Between Brackets
Jul 31, 2007I have a value that is bracketed and I just want the number extracted to another cell.
I've shown three part lines of the data I need to extract from.
Cd56.5,(53.5),1:34.16,(36.22),
53.0,(53.0),1:36.32,(37.36),6/1,
-2.0,(54.0),1:59.26,(36.52),9/1,
The values I need are 53.5 from line one-53.0 from line two & 54.0 from line three.
As you can see they don't always sit in the same column.
