I am struggling with a macro that is supposed to hide a row if length of cell A =9 and the value of cell C =0. The error I get is "object required".
Code:
Sub hide()
Dim cntA As Long
Dim rng As Range
[Code].....
I've set up a filing system which saves sheets/ workbooks based on the value of a cell - Range("B1") Everything works great apart from when ThisFile String length exceeds 31 characters which you may know is the max useable character length for a sheet name - I had no idea! 8-0
Is there a way i can check if string length exceeds 31 characters then, if it does, shorten it to 31 characters?
[Code] .....
How to create a code formula to calculate the arc length from a given chord length?
If you know the radius of the major circle.
Say the chord is 50mm and major circle dia is 72mm (radius 36mm)
arc from chord.jpg
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet.
The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA.
For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2.
So, in conclusion:
If:
Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1
Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
I want to create a macro that checks whether a specified range of columns is hidden, and
a) hide them if they are not hidden, or
b) unhide them if they are hidden,
i.e. toggle the Hide state.
What method can I use to query the hidden state of a selected range of columns and return a boolean value?
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................
Is there any length limit for the cell names? is there any lenght after wich it makes VBA or the spreadsheet unstable or clumpsy? I´m using Excel 2003
View 6 Replies View RelatedI would like to create a formula that would calculate the length of a person's employment, i.e. hired 8/14/94 till today, for example.
View 10 Replies View RelatedI am rebuiliding my deck on my house and I am laying the boards on a 45 degree angle. Boards come in 12', 16', 20' lengths and are 5.5" wide.
The overall measurement of the deck is 12'x17'6".
What I need to know is how many boards I will need of each length. I would be grateful to see how this is solved w/ formulas, etc...
How do i find the length of an array, which has been declared as
VB : Dim abcarray() As Variant
Calculate Lowest Value By The Length
View 5 Replies View RelatedI'm trying to use an if statement with a LEN function, i want to say if column O2 is 5 characters long then populate with Y if not populate with N.
I have tried.
=IF(LEN(O2,5),"Y","N"
but it doesn't work?
I am creating a table for a depreciation schedule. I am having the user enter the year the asset was purchased and the expected useful life (in years) into the input box and I want the output table to show only the amount of years useful years the assets has. Every year after that should not be shown on the table.
View 1 Replies View RelatedI have a worksheet in Excel 2007. I have "wrap text" turned on in column G and so long as the character count doesn't exceed 255 the text will wrap and display properly. However, when the number of characters in a cell of column G exceed 255 the display in the cell turns to all pound signs (#). The content of the cell is displayed in the URL line but it is all # in the cell. How can I fix this?
View 4 Replies View RelatedI am trying to create a string of text that grabs info from other cells, which is easy so for example
=A1&A2&A3
would grab all the info from the 3 cells and merge them to create a string, but what I want to do is create a prefixed length.
Lets say A1, A2 and A3 would equal 10 characters in total but B1, B2 and B3 would equal 7 characters, these would be different lengths. so for a visual description I would like them to appear like this
At the moment
01MAIN-HELLO
01DESSERT-HELLO WORLD
Would like
01MAINXXX-HELLO
01DESSERT-HELLO WORLD
This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.
A Length B Length Sort & Arrange Data By Length
Required Data
455915.210 10.000455797.536 15455790.0235
455905.557 20.000455790.023 5455915.21010
455898.910 30.000455781.210 25455797.53615
455888.044 40.000455773.414 85455905.55720
455881.357 50.000455768.432 45455757.24424
455870.547 60.000455764.583 77455781.21025
455863.575 70.000455763.990 91455762.80228
455856.131 80.000455763.478 105455898.91030
455850.238 90.000455762.802 28455888.04440
455840.065 100.000455762.218 84455761.73241
455839.316 110.000455761.732 41455768.43245
455833.056 120.000455761.150 66455751.48346
455827.908 130.000455760.505 67455881.35750
455820.185 140.000455759.243 88455870.54760
455814.451 150.000455757.244 24455761.15066
455805.390 160.000455751.483 46455760.50567
Attached File : Required Sheet.xls‎
So I have got an identifier and a corresponding column of values. I need to calculate sum of those corresponding values for each identifier, so if identifier is 1993, I need to have a sum of all the corresponding values. These identifiers repeat, and I do not want to calculate sum for all of them, just the immediate identifiers (i.e. you have 1993, after that you may have 1994 etc etc and then at one point you will have 1993 again, but that "new" 1993 will have a different sum of course). Also, identifiers do not go in sequence sometimes (however, most of the case they do), for example 1993 may jump suddenly to 2004. The trick with all of this is variable "row width" of these identifiers...
222.xlsm
I have a STRING array which is dimensioned with 100 elements possible. What i want to know is how to determine the "length" (not sure if this is the right word) of the array. For example: Suppose i have 10 strings in the array. Is there a command to determine that there are only 10 elements in the 100 possilble array?
View 11 Replies View RelatedIf I have a cell with this info: [6126]BOB SMITH
What formula can I use to get BOB SMITH. The length of the name will vary. The number will change, but will always be 4 digits and will have the brackets. [XXXX]
I have been using this code below to replace any blank cells with the number 0 but recently I have encountered an error if the format of the cell is a string.
Code:
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeBlanks).Value = "0"
Is there a way to change this so it looks at the length of the cell, if the length of the cell is 0 then set the value = 0?
What the max length of an ActivX Control name would be. I mean the name that you would use in coding something, not the actual caption name of the button. IN this case I am using a radio button.
Based on that I would like to create random IDs to assign to the button. I know how to do this in the cell formula but not in code.
Trying to sum up a column that has an unknown length. This is a canned Excel form that is saved within a software system and is used to create customized documents. Usually, I would enter the table name.field name in a cell to pull the data from various parts of the system.
In this case, that data resides in AA234 of my customized Excel sheet. When the user retrieves this doc from the system, the data could run several rows, starting from AA30 to AA255. So, in my "Total" cell, I've entered =SUM(AA1:AA255), so it will just add up whatever is in the column, regardless of how long it runs upon document generation. When I try to pull the document, I get "=SUM(#REF!)" in that field instead.
I have a row of 1's and blanks, where there might be 10 consecutive 1's, followed by four consecutive blanks. I want to calculate the average length of a string of 1's in the row -- how this might be accomplished?
View 7 Replies View RelatedLet's take the following dataset:
View 9 Replies View Relatedi have tried to create a userform thru which data is to be entered. so that the data will be printed on a worksheet. here there are few text boxes, in which the number of digits should be equal to 14. after filling the userform when i click the print button if it is less than or more than 14 a pop up msgbox should be displayed with OK button and the cursor should go back to that particular text box. i have written the following code, but it has a problem. even if the total no of digits are 14 the msgbox is displayed.
say the text box name is Roll no
If txtRollNo.MaxLength 14 Then
MsgBox "Roll No should be of 14 digits", vbInformation + vbOKOnly
txtRollNo.SetFocus
Exit Sub
End If
I have an array "Public SOWcount(1 to 5) as Single
I'm running nested for loop and I want to do something like:
for outerloop = 1 to SOWcount.length
where SOWcount.length is the number of occupied spaces. Basically, my array may only have a few entries, and its important that I only run the loop once for each item inside the array.
I need to expand my columns to different character widths (fill with blanks) for an Import into an other program, I state the No. of characters in that column and Import. Trim does the complete opposite to what I require, does anybody know if the function exists and if so the syntax for it.
View 4 Replies View RelatedBelow is a formula that I use which now excceds the maximum length. =Consumables!S56/('Price list'!S56+'Price list'!S57+'Price list'!S58+'Price list'!S59+'Price list'!S60+'Price list'!S61+'Price list'!S62+'Price list'!S63+'Price list'!S64+'Price list'!S65 "all the way up to" +'Price list'!S118) This formula is also repeated is cells =Consumables!T57/('Price list'!S56+'Price list'!T57...... etc all the way =Consumables!AE56/('Price list'!AE56+'Price list'!AE57.............
Is there any way I can condense this formula? I have tried:
=Consumables!S56/('Price list'!S56:'Price list'!S118)
=Consumables!S56/('Price list'!S56,'Price list'!S118)
