I want to introduce a number in the textbox1, and that number sets which entire row to select for making a chart. i thought of something like this, but i cant put the variable x just like that.
Private Sub CommandButton1_Click()
Dim x As String
If TextBox1.Text <> "" Then
x = TextBox1.Text
Range("Ax:Mx").EntireRow.Select
[Code] .....
I don't know how to concatenate a string within the Rows function. I want to be able to vary the row number with a variable, but I don't know how to construct the string, especially given that one of the delimiters within the function is ".
View 3 Replies View RelatedI'm trying to write a macro that will validate data in variable amount of rows but will always be in a specific column. what i'd like to do is count the amount of row entries in Column A to give me "iRows." Then validate data in column E from E1-E"iRows." Currently my data validation runs for the entire column which means i can not validate blank entries. Once I can limit the validation process from E1 to E "iRows" then I can consider blank fields and mark them as invalid.
Columns("E:E").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlGreater, Formula1:="1"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Award Amount"
.ErrorTitle = "Award Error"
.InputMessage = _
"Please enter the current expected total value or current award amount for this contract."
.ErrorMessage = _
"Award amount may not be set to 0.00. If you do not have an amount awarded simply make the award amount equal to the paid amount."
.ShowInput = True
.ShowError = True
End With
I've tried using different ways of counting the rows and have been able to define "iRows" the problem is defining the range to only column E from E1 to E"irows."
I want the select case list of a ComboBox to be treated as a variable in order to shorten the code size. To clarify the problem, i post the code with what i want to do, but don't know how to do it that way.
Code:
Select Case ComboBox1.ListIndex
Case 0: y = "AT"
For j = 0 To 26
Case "j": y = sheets("name").Range("A(j)") 'Range A(j) is a string, so y as well, as seen in Case 0.
Next j
End Select
Is something like this possible?
I have a spreadsheet with employees and data listed. The drop-down in A1 lets someone select the employee and then it hides the rows for all other employees. I want to add the names of supervisors in the drop-down of A1 and have it select only the employees under that supervisor and hide the rest. The number of employees under each supervisor ranges from 3 to 6. This is what I have to hide the rows when selecting a single employee :
[Code] .........
My workbook has stats data on the "Master" sheet(sheet #1) and analysts and supervisors on "Analysts" sheet(sheet #12). Data validation for cell A1 on "Master" sheet has all the analysts and supervisors in the first column of "Analysts" sheet with title "Select Analyst" in cell A1. I modified the "Analysts" sheet to show supervisors from B1:I1 and listed the analysts in the appropriate columns below them. Not sure how to make it select the analysts when someone selects the supervisor on "Master" sheet .
I have a sheet with entries dated. With values next to them.
From 1/11/08 to Whenever it ends.
I would like to work out a function to select the activerow(depending on cell)
and the 30 rows under it and then print them.
Like a report almost
This could be a button or anything, no pref on it ^^
If i have row 10-20 in column A filled, what vba code would i need to select a range in column C that selects rows 10-20
If that 10-20 rows in column A chnages to say 10-50 the vba code will select 10-50 in column C
I'm trying to use IF with other variable but is not working.
What I have:
Colum G12 - NET Sallary
Colum I12 - 3 possibilities (MRM, ME, AB)
Colum J12 where I will have my formula.
I have another table with 3 datas:
MRM = 1,709 (P3)
ME = 1,544 (P4)
AB = 1,630 (P5)
What I need to do is the following:
J12 = IF I12 = "MRM" THAN G12*P3; IF I12 = "ME" THAN G12*P4; IF I12 = "AB" THAN G12*P5
I need to multiply the sallary to one of the 3 variable depending on the category: MRM, ME and AB.
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.
Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference?
The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
On my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet.
To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field.
The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively.
To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies View RelatedI have a multiselect listbox with values that gets populated from a sql statement, and I would like to get is the first or second index from the selected item. I know how to get the listindex from a combobox by using:
VB:
cbnumber.List(.ListIndex, 0)
How can I loop through and get the 1st index number for the selected items only from the listbox? I want to pass this index number to another sql statement.
I am trying to select rows that contain text using a variable counting number of rows so rNumb = count number of rows
The Problem
Worksheets("Table").Rows("2:rNumb").Copy
rNumb won't work. What is the proper syntax?
I am trying to run some code when a worksheet is selected.
I'm doing this using a macro currently (code below), but this requires that i use a quick menu button and i would like to make it a form button and assign a macro to it (so i can distribute the file without everyone having to create a menu button).
I would like to replace (or modify) the code below to select any sheet other than the ones named "a" and "b".
Code:
Sub ImportAlarms()
Dim thisSheet As Worksheet
Dim targetSheet As Worksheet
On Error GoTo failed
Set thisSheet = Application.ActiveSheet
Set targetSheet = Sheets(TARGET_SHEET)
[Code] .......
Basically I need a bit of VB to find the last cell in column F with text in it (is it End(xlUp)??) and then apply full borders from that cell up to cell Q5. A test macro tells me the border bit is as follows, so just need defining the range.
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
[Code] ......
I know how to do Range("A" & I).Select, but how about if I want columns A thru E instead?
View 7 Replies View RelatedI am doing is setting a variable called eof to the number of rows (with text) and i just want to select columns A1 - G1 and the eof range!
Dim eof As Integer
With Sheets("Coverage Count (%)")
eof = .Range("G65536").End(xlUp).Row
'crashes on the .select
.Range("A1:G1" & eof).Select
Selection.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
I've done this before and I could have sworn this worked...but i must be missing something.
This one should be easy but I keep running into the same wall.
On every page I have single cell defined name range based on the sheet name & "_startcell"
So on sheet MfgReq I have cell A3 defined as name range "MfgReq_startcell".
I would like to the following:
.clearcontent using the "MfgReq_startcell" as the upper most left cell, all the columns to the right, and all the rows down.
on this sheet it might be 5 columns wide by 4000 row & on another sheet it might be 50 columns wide by 50000 rows.
I am trying to create a command button on my userform that will allow me to print all the records in the worksheet....in other words it should only print the rows with data......
*my worksheet is called "complaintData" - this is hidden and not active sheet.
*I have columns A to J with information, with heading from A1:J1.
*I want to be able to print all the rows with values in column A. (Not all the columns except "A", will have values for every record....some may be blank for some records...
*I want all the columns to fit in in one page with headings (A1:J1) being first row on every new page.....
*page setup should be landscape...
I have tried a few codes from this forum, but not sure where I have goofed up....am still trying learn VBA.....would appreciate if anyone can fix this code for me....cheers
Private Sub cmdPrint_Click()
Sheets("ComplaintData").Visible = False
Dim ws As Worksheet
Set ws = Worksheets("complaintdata")
I have tried various syntaxes but nothing is working for me. I am using XL 2000 so it doesn't have the relative address function in the macro! So here is what I have
ActiveCell.Range("A1").Select
ActiveCell.R1C1
Range("R1C1:R1C85").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application. ScreenUpdating = True
Range("A1").Select
I need to be able to select from the originating cell which will always be in "T" Col thru to "CZ" col but will be on different rows.
I have this problem quite often and always have to think of a way around it, however I think there must be a simple method of doing what I want.
If I want to select row three, I would write:
Rows("3:3").Select
However, if I have a variable which holds my row number how do I rewrite the above code? I've tried serveral varations of:
Rows("CurrentRow:CurrentRow").Select
I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.
For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.
If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.
If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.
I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?
View 1 Replies View RelatedI have a Master workbook with 4 sheets named WEEK 1 to WEEK 4. At work we work around 13 4 week periods and need this workbook to be dated accordingly, that is Period 1 Week 1 and date of first day (Sunday) of that period. The ideal solution would be for the user to enter 1 to 13 into a cell and via a button all dates to be populated and a new workbook created for that period. I have attached a sample workbook with the 4 weekly sheets and a date range on another sheet to show the range I need the dates from. I hope the workbook explains what I need better than my explanation here!
View 2 Replies View RelatedI have a spreadsheet where I want to record the VO2max value (a number indicating the maximum volume of oxygen a person can utilise) of a person.
The VO2max is just a number, normally between 30-60.
I then need excel to look up the VO2max value (i.e. “35”) and give that number a predetermined inputted value name in a different cell (i.e. “low” or “fair”, “good”, “very good” etc etc).
However, as VO2max is also affected by age and *** I also have a column for the participants age and *** and I want excel to use that to make the value name.
E.g. in a 24 year old male a VO2max of 32 is “low”
But, in a 42 year old female a VO2max of 32 would need to be classed “”moderate”
Any ideas how this can be done? can it be done?
I could post the table containing the age range, values and names if I knew how to post an excel sheet on here.
I have a workbook, with a worksheet "Summary". In this worksheet "Summary" I would like to delete all columns that have less than 100 rows.
Please see the attached file : Summary.xlsx‎
I have a workbook has many worksheets, I would like to be able to delete worksheets if, for example, the number of rows in the worksheet is less than 100 rows.
View 3 Replies View Relatedi want to sort a column in such a way that it starts with those cells having the highest number of letter. For example:
before:
AA
AAA
A
AAAAAA
AAAA
AAAAAAAAAAA
I want it to look like:
AAAAAAAAAAA
AAAAAA
AAAA
AAA
AA
A
Ofcourse the real list doesnt contain only "A"s. It contains of words and sentences.
How can i sort columns A as mentioned? The order of column A with other columns should not be destroyed be the sorting process.
The number in cell M21 falls into one of the ranges from C4 to C15. A matrix number needs to be printed in each cell from N21 to N30. The selection of the number to be printed in column N (under Matrix Assignment) depends on the range it is next to in array A4:C15. For example (as shown), the correct number for cell N21 is 122 because 0.2626 falls between 0.24 - 0.2699, and Matrix 122 corresponds to that range.
View 3 Replies View RelatedI am looking for a formula which will insert a word into cell A3 depending on the number in cell A2. For example A2 is between:
0 - 10 then insert the word Div 1
11 -17 then insert the word Div 2
18 - 23 then insert the word Div 3
24 28 then insert the word Div 4
