I would like to find a way to chose a string and number based on cell location
ex.
_____A_________ B _______C
1___Mike,1____Mike,2____Mike,3
2___John,1____John, 2___John, 3
3___Steve, 1__Steve, 2__Steve, 3
I Want to run this in a subroutine, but I am not entirely clear on the abilities of datachecking in ranges.
I have data in columns A to Z. Column A has the name of Bonds and columns B to Z have different returns for those funds. I would like to create a scatter plot chart that allows me to switch which column to pull the data from in addition to column A which will always have the name of the Bonds.
For example in addition to the name in column A I would like the chart to plot data from Columns C and F on the x and y axis respectively.
I'm thinking of copying column A to a new tab and then having a drop down box in columns B and C to copy over values from the 2 columns I select. How can I get a drop-down box to copy over values from another column?
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
im trying to write a macro that will switch two selected values for example if a select a1 and run then a1 & a2 values would switch places.
so far ive tryed and got nothing
also is there like a wait for user input or mouse click function.
so when i run a macro there is a point where it would wait for me to say, select another cell before continuing?
I have a list of 20,000 addresses and because of the way the sheet gets populated by different departments, the COUNTRY CODE and POSTAL CODE columns get their data reversed.
Instead of 48193 US, it gets put in as US 48193.
Since i have some many, and they are not in order at all (I could probably sort by COUNTRY CODE and get 95% of the in order (except for those pesky Canadian codes), is there a macro way out of this?
Even if I select two adjacent cells and run the macro and have the contents switch. Or if there is a way to do the 95% all at once.
I work in the field of education and the spreadsheet I'm working will require pupils to enter answers into the spreasheet. When a command button (New Questions) is pressed I want the VBA code to switch the cell protection (locked) on or off depending on a random value in cell c2. If c2 = 1 I want f3 to be locked, if c2 = 2 then d3 needs to be locked, but not f3. I've attached a copy of the spreadsheet to this email
View 5 Replies View RelatedI have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:
Number of variable entered: 5
q9001
q9002
q9003
q9004
q9005
q9006
etc.
Should give me: "q9001 q9002 q9003 q9004 q9005"
I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.
(Using Excel 2010)
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
and also t = time()
I need to create a dynamic range based on cell between a header and footer cell. The header cell ( A8 ) will remain static, however, the footer cell starts at A10 and then will move down as rows are added.
View 9 Replies View RelatedI have a spreadsheet (book1.xls) that has a column with 13 names down the side and 31 other rows (dates of the month)
Mike1 1 2 3 4 5 6 7 8 9
Mike2 1 2 3 4 5 6 7 8 9
Mike3 1 2 3 4 5 6 7 8 9
Mike4 1 2 3 4 5 6 7 8 9 etc
I have selected all 13 rows and 7 days and named them 'week1', and the next 7 days 'week2', the next 7 days 'week3' etc/What i want is to display the named range based on a single cell selection based on a cell value in a different sheet.
The basics of it should be "If cell C1 = 1 then show me 'week1' or if cell C1 = 2 then show me 'week2' etc.
Let's assume Cell A2, A3, A4, etc, only contain names/text.
Then let's assume I have set up cell B2, B3, B4, etc, to contain a percentage value based on a formula and some other entries I have made.
It should be simple, but I'm having trouble - how do I set up A2, A3, A4, etc, to highlight into different colours based on Column B's data/percentage falling into a certain range?
ie: - if B2 is between 0% - 25%, then highlight A2 green
- if B2 is between 26% - 50%, then highlight A2 orange
- if B2 is between 51% - 100%, then highlight A2 red
I am trying to create a table, based on what people predict in sports games. I have most of it in place, but I have hit a brick wall where one part of it is concerned. For example, cells M43:M56 have the scores for each week of what one person predicted., so it could be 2,5,7,2 and so on. What I then need the table to do is determine which round or week it is and display the relevant score. So based on the above let's say it's round 3, and his score would be 7 for the week, and the cell to the right of this shows the week's score plus his total score so far.
View 2 Replies View RelatedI am looking fo a simple way to carry out the following..
My Spreadsheet has a range of data from column D to O
I have a Total Column Q
I have a Cell, eg A2, where I enter a value from 1 to 12
Depending upon the value entered into A2 will dictate the range of cells to sum, ie
A2 = 4 - Column Q would Total Range D?? to G??
A2 = 12 - Column Q would Total Range D?? to 0??
etc.
I'm trying to create a = SUM( RANGE) forumla, but, I need the first cell reference to represent the value of a different cell...
something like...
=SUM("A1"1:H1)
Where A1 holds the value of a cell number. ie, cell A1 may have the value 'e', so I want the result to be the sum of range e1:h1.
I have a user form that has several tab pages on it when the form opens it checks to see if the user has entered there default info if it is empty it needs to go to the default tab so the user can enter there info. How do I do this using vba in excel 2000 I have not been able to find any code that will do this if I use the set focus option for a text box on the tab page it comes up with an error saying the item is not visible! The user forme is names = UserDataInputForm. The tab pages I have are
1st = InputDailyTimesPage
2nd = OverTimePage
3rd = DataPage
4th = DefultDataPage
the form opens using the 1st tab page by default
in the user forms inisilisation code I have this check
I have created a database of names & addresses from a software program called "CLIP" into excel, which in turn will become a label mail merge in MS word consisting of 900-1000 customers (for a mass mailing).
The problem is, the names were created last, first and I would like to change them to first last. Is there a way to do that without manually typing them all in again?
The names are in one column, so I cannot just switch the columns around.
I try to code some vba here's my example
1 A B C D
2 3 5 1 2
3 1 2 0 1
4 1 3 0 1
5 1 0 0 0
I want to sum in line 2 all values below but i dont know how to switch to next collum in vba
have been using the simple formula below to determine between a friend and myself whos turn it is next.
=IF(Q22
I'm a VBA beginner In my Excel worksheet, I have 1 column with data (column B). The data is in rows 5-250. This data is student majors.
Now, I want to assign each major a department and display the department in column U.
I was able to complete this for row 5 by using If-Then statements (see snapshot below):
[Code] ......
However, I would have to copy and paste that code over 200 times and replace the B5 and U5 with the correct row number. How can I get the macro to do this for each cell in a specified range?
I tried the following, but it did not work:
[Code] .....
I have a spreadsheet that has number between 1-20 entered in every cell in column B all the way down to row 15,000
I need to program something in VBA that will look at each number and then lock a certain range in that row based on that value in column B
For example:
In B3 there is the number '5' therefore I would want it to stop the user entering anything between C3 and G3
In B4 there is the number '3' therefore I would want it to stop the user entering anything between C4 and E4
In B12500 there is the number '14' therefore I would want it to stop the user entering anything between C12500 and P12500
I don't want to have to type out programming for each criteria from 1-20 for 15,000 rows!
I want a message box to pop up when a "certain" value(example "guest") is enter in a range(ex- a1:a20).
How can i accomplish this with a vba code?
I have a worksheet with a block of data. The size of this block changes.
I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet.
So far I have everything working fine.
Now I want to create a button to trigger a macro to sort that block.
I can figure out the button and make it trigger a macro.
One of the lines in the macro is
how (in VBA) to select a range within a column where all values are the same?
For example, given the following:
A B
1 ID Value
2 1 A
3 2 A
4 3 A
5 4 B
6 5 B
7 6 C
How would I select range(B2:B4) where all values = A?
I would like to start off in col A, do an offset until a certain value is reached:
View 2 Replies View RelatedI have a sheet that has six sets of three columns on it. The columns are labels Ball #1, Number Times Drawn, Last Date Came Up. The first five "sets" are the same except the ball # changes from 1 to 2 to 3 to 4 to 5. The last set has columns labeled Mega Ball, Number Times Drawn, Date Last Came Up. Yes I am doing stats analysis on lottery numbers for a project.
What I want to find is the last date drawn for each of the numbers, 1-56, and have it go into the third column of the 3 column sets.
I will use the first column set for example. I have numbers down the first column A numbered from 1 to 56 (In series). In the next column B, I have the number of times drawn for each number, which I have working. In the last column of the set I want to have the Last Date Drawn.
I tried the following function (Formula):
=INDEX(Table1[Date],SUMPRODUCT(MAX((Item=A2)*ROW(Item)))-ROW(Table1[Date])+1)
This kind of worked. It returned a date but it was not correct. It actually said the same date for each number, which I know is wrong. I can't figure out where it is actually pulling that date from.
I should clarify that the drawing numbers are on a second sheet labeled "Mega_Millions_Numbers" in a table named Table1 and the Date column is A with a column label of Date. The table has column headings of Date, Ball#1, Ball#2, etc. through to Mega Ball column.
What I need is a function or formula to pull the last date drawn. I am NOT good in any way shape or form with VBA so if I can do this without VBA it would be best for me. I can follow along with formulas and functions well enough, just not VBA.
I will need to have this formula copied down 56 times per set and there are 5 sets so i will have to modify it for the other columns. (If your keeping track that totals 336 formulas).
I would like to search my worksheet and whenever it finds the word "Description" in column "B" then it should select the range "B:E" for that row and delete the cells directly above it.
This is what I tried but nothing happens:
lr = ActiveSheet.UsedRange.Rows.Count
For Z = lr To 1 Step -1
If ("B:" & Z) = "Description" Then GoTo deleteit
GoTo xt
deleteit:
z1 = Z - 1
Range("B:" & z1, "E:" & z1).Delete
xt:
Next
End Sub
I have a spreadsheet broken out into about 30 4-row ranges, for example (A10:L13). All of the ranges use a using a vlookup argument:
=IFERROR(VLOOKUP($A10,Fund_Range,2,FALSE),"") in the second row.
Around 15 of the ranges will be populated based on varying data in the fund_range data table, the remainder will be blank. The first row of each range is a header row, I would like the macro to delete the range when there is no value in the second row (the blank ranges). Every attempt I have made starting with other code from the forum has come up empty. I think part of my problem is the vlookup formula existing in these otherwise "blank" rows.
I would like a macro to run everytime A1's value changes.
The following works for an entire row, however, I would like range A:F highlighted not .entirerow.
I have thought of conditional formatting, but I thought the range I was using was to large. (A3:F40000)
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("a1"), Target) Is Nothing Then Exit Sub
If Range("a1").Value > 0 Then
Call mymacro
Else
Cells.Select
Selection.Interior.ColorIndex = xlNone
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1").Select
End If
End Sub
I have rows of data, and the first number corresponds to the number of subsequent numbers that are relevant.
I want to paste that number of values into a second spreadsheet. How do I do this?
e.g.
A1 B1....
6 21 15 14 32 85 14 16 21 25 26 24
In this case I want to copy the 2nd-7th number, as the 6 tels me I want the next 6.
I'm trying to format a range of cells based on a single cell. Below is the code that
I am using:
Private Sub Worksheet_Change(ByVal Target As Range) ...