I have a couple of hundred string values in column A. I am trying to create the same number of rectangle shapes in column B, each one taking its name from the corresponding cell in column A. I keep hitting the dreaded 400 error code .
Attached File : test add shapes.xlsm
I have the list below and would like to create a new list which contains only names with corresponding values and lists them.
+ A B
1 James 3
2 Derek
3 Brett 6
4 Allan
5 Jess 7
6 Sam 10
7 frank 10
The solution should look like:
+ A B
1 James 3
2 Brett 6
3 Jess 7
4 Sam 10
5 frank 10
I am trying to allow for a cell selection to initiate a macro that would take the top cell in the current column and most left cell in the current row and placing those values into the names of the workbook and sheet that I want to move to.
I am tyring to allow for my users to drill down into data via selecting the cell in question that is referenced from another workbook and sheet. I want to place the value of the cell
= most left cell in current row - 1-1 to be placed in Active.sheet"1-1".select The work bood would pull the value from the most top cell in the current column and place that value in Activeworkbook.place here.select.
I am keeping a spreadsheet for an auction
We have 20 people and each person gets a total bid for 48 items.
if person1 = 55,person2 = 100,person3 = 76,person4 = 67,person5 = 49 etc.
The person's names will be in column C while values in D in cell G3 will hold the total bid for the items each week updating.
What I am trying to do is sort the table (names & values) from cell G3.
So if each week G3 total gets bigger then the table sorts to match.
If the overall total bid after 48 weeks (G3) was 52 then the table sorts with 55 on top (because it's closer) 49 next.
The closer the person's bid to the total bid the further up the table the person will be.
I have uploaded an example with before and after sample.BidExample.xlsx
Is it possible to make tab names dependent on Cell Values?
I have 12 Tabs in Workbook 1
Tab 1 = "COVER PAGE"
I would like Tab 2 ='COVER PAGE'!A1
Tab 3 ='COVER PAGE'!B1
Tab 4 ='COVER PAGE'!C1
etc...
Is this possible so that I can add cells (between B1 and C1) at a later date and have all the tab names change depending on the values I put in the cells?
I would Like to define each row in a spreadsheet to the value of the first cell in each row and I have no idea how to go about this.
Code Customer
_0888 Williams
_0435 Smith
So basiclally I would like to name the first row "_0888" being 2:2. But without doing each row manually via Insert --> Names --> Define..
i am trying to enter the EndX coordinates (The third number: 500) by entering a number in a forms textbox
ActiveSheet.Shapes.AddLine(0, 100, 500, 100).Select
how can i break the code up to enter the coordinates via textbox's
I am trying to convert some text from a number of cells to shape in another workbook. The problem is, if the text in one of the cells is too long (from testing it by too long i pretty much mean roughly 100 characters) then it doesn't pass anything at all to the shape. Is there a way around this so that all text will be converted to the shape regardless of its size?
By the way, the code is:
ActiveWorkbook. Sheets("Sheet1").Activate
ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text = "1. " & Priority1Range.Value & Chr(10) & "2. " & Priority2Range.Value _
& Chr(10) & "3. " & Priority3Range.Value & Chr(10) & "4. " & Priority4Range.Value & Chr(10) & "5. " & _
Priority5Range.Value
The variables 'Priority1Range' represent the cell the text is in that I am trying to pass and it's variable type is Range. I have tried declaring the Priority1Range.Value as a string variable and using this instead but this doesn't work.
I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names:
Sheet2ABCDEFG1Table 1Table 22Domain NameGroup NameDomain NameGroup NameDomain NameGroup Name3NCG-W3K-SAT14-0DOSSNCG-W3K-EEC-SUN16-3EECEXCEEC4SVL-WIN-SAT08-0LABSNCG-W3K-SUN16-0DOSSDCDC5SVL-WIN-SAT06-0LABSNCG-W3K-TUE01-0DOSSNCGDOSS6NCG-W3K-MON04-0DOSSNCG-WIN-EXC-SUN21-0EECDMZETIS7NCG-W3K-EXC-MON04-3EECNCG-WIN-EXC-SAT16-0EECSVLLABS8NCG-W3K-EXC-MON03-0EECNCG-W3K-X64-DC-SAT14-0DC9NCG-W3K-EXC-TUE01-0EECDMZ-NCG-W3K-X64-SUN12-0ETIS10NCG-W3K-EXC-SUN15-0EECDMZ-NCG-W2K-SUN12-0ETIS11NCG-W3K-X64-EXC-SUN16-0EECNCG-W2K-SUN20-0DOSS12DMZ-NCG-W3K-SUN11-0ETISNCG-W3K-MON03-3DOSS13NCG-W2K-DC-SUN11-3DCSVL-DMZ-WIN-SUN12-3LABS14NCG-W3K-X64-DC-SUN10-3DCSVL-DMZ-WIN-SAT10-0LABS15SVL-DMZ-W2K-SUN12-3LABSDMZ-NCG-W3K-SUN12-0ETIS16NCG-W3K-EXC2-MON03-3EECDMZ-NCG-W3K-SUN10-0ETIS17NCG-W2K-SAT17-0DOSSNCG-W8K-X64-DC-SUN10-4DC18NCG-W3K-X64-SUN10-0DOSSNCG-W3K-TUE01-3DOSS19NCG-W3K-TUE00-0DOSSNCG-W3K-DC-TUE02-0DC20NCG-W3K-X64-SAT15-0DOSSSVL-WIN-SAT11-0LABS21NCG-W3K-WED00-0DOSSSVL-WIN-SAT07-0LABS22NCG-W8K-X64-SUN19-0DOSSNCG-W2K-SUN17-0DOSS23NCG-W3K-EXC-SUN20-0EECNCG-W3K-SUN20-0DOSS24NCG-W3K-ULZ-SUN14-0DOSSNCG-W3K-TUE22-0DOSS25NCG-W3K-TUE03-0DOSSSVL-WIN-SAT07-3LABS26NCG-W3K-ULZ-TUE00-0DOSSNCG-W3K-MON05-3DOSS27NCG-W3K-SUN19-0DOSSNCG-W8K-SUN18-3DOSS28NCG-W3K-EEC-SUN19-0EECNCG-W8K-SUN22-0DOSS29NCG-W3K-EEC-SUN20-3EECNCG-W8K-X64-DC-SUN11-0DC30NCG-W3K-EEC-THU00-0EECNCG-W3K-SUN17-0DOSSExcel 2007
My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria.
1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2
2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS.
That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.
Is there a way to make the colours in a chart (pie, column etc) correspond to a value? I'm using Excel 2002, soon to start using 2003.
For example, imagine a normal pie chart showing the population of each country in North, Central and South America. But for each country, colour gradations would be used to show levels of wealth (e.g., GDP per capita) as follows:
saturated red = bottom 20% (poorest)
pink = 21-40th percentile,
white = 41-60%,
gray = 61-80%,
black = 81-100% (richest)
Is this possible? And not to get too greedy, but can you use even finer gradations -- say, with ten categories, as opposed to the five in the example above?
I searched and found that to assign a name to a label based on a cell value requires the following
Label1.Caption = Worksheets("Sheet1").Range("A1").Value
which would assign the value in cell A1 as the caption for Label1. I've got a range of values in cells:
C4:N4
and I'd like to assign them as names to labels 1 through 12. How would I do that using VBA?
when i run the below code i get an error 438 'object doesnt support this property or method'
View 2 Replies View RelatedHere's the data table being referenced
Rank
Week Ending
Name
Value
1
1/1
Apple
100
[Code] ........
Now on another sheet, I want to return the top two 'Name's and their values like below:
Name
Value
There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?
I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..
I have this script (below, Krishnakumar orignally provided this script).
I'm trying to edit it to add cell values from cells C16 and E16 along with the worksheet name it displays in listbox2. So lets say worksheet "rollover" is the sheet being displayed in the listbox2.
I would like for it to look something like this:
rollover TT:'data from cell C16' TA:'data from cell E16'
Public FilePath As String
Public dic As Object
Public oWB As String
Public oWS As String
Public aWS As Worksheet
Private Sub CommandButton1_Click()
Dim i As Long, wb As Workbook, n As Long
With Me.ListBox2
For i = 0 To .ListCount - 1
If .Selected(i) = True Then
oWS = .list(i)
Set wb = Workbooks.Open(FilePath & oWB, UpdateLinks:=0)
wb.Sheets(oWS).Activate
Exit For
End If
Next
End With
End Sub
I have a spreadsheet containing a list of key fob numbers, key numbers, etc. I have 3 userforms with different for different options, i.e. Key fob no. search, key number search, room number search. I have set up the code for each of the userforms to search for the information entered in the textbox and then lists the info in the listbox which I can then click on the one of the listed items to take me directly to the place in the spreadsheet.
However since there are a range of different key numbers per key fob, I would like to know what code I can use to allow a range of information such as the key number and room number, etc to be displayed in the same listbox as the Key fob number i searched for.
Here is my code for one of the Userforms:
Option Explicit
Sub Locate(Name As String, Data As Range)
Dim rngFind As Range
Dim strFirstFind As String
With Sheet1.UsedRange
Set rngFind = .Find(Name, LookIn:=xlValues, lookat:=xlPart)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
I cannot attach a example of my form since the site seems to have a limit on the size of the upload and my part of the file compressed in zip format is still at 168kb.
I have a worksheet with many tables that I use in formulas.
I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.
Also, automatic copy of formulas/formatting is great.
But, what I really like about tables is the ability to use the naming conventions in formulas.
Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.
Example:
Code:
=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"
Is it possilbe to change the shape of a cell to a circle?Or is there a way you can make a shape act the same as one of your cells?
I require a macro to enable a selected shape to be moved from current cell location to relative position but 1 row up. eg topleft address = A4 and shift shape to topleft address = A3
View 5 Replies View RelatedWith the following code I can get shape properties, but I can get it for the linkedcell of the shape.
VB:
Sub GetShapePropertiesSomeWs()
Dim sShapes As Shape, lLoop As Long
Dim WsNew As Worksheet
Dim wsLoop As Worksheet
Set WsNew = Sheets.Add
[Code] ....
I have a shape on one sheet. Need to change the name of a shapes depending on a value in cell from an other sheet.
View 6 Replies View RelatedI am trying to select a shape in a cell and place a cell reference into the shape. I know the cell address, but do not know the shape reference. (The row that the cell and shape reside came from copying the row from another sheet and inserting it into the current sheet). I can not seem to come up with the correct code to accomplish this. Right now I have:
Cells(r + 1, 48).Select
ActiveCell.Shapes.Select
ActiveShapes.Formula = "$AW:" & r + 1
Where r+1 is the row where the line finally resides after insertion and "AW" is the column to the right of the cell where the shape resides.
I have tried to put the cell reference into the shape at the location where it is copied from but the reference is not dynamic and I can not seem to get it to be. Another issue that will arise is that the rows in the new sheet will probably be sorted and I question if the cell reference will follow the shape's new location.
I'm tying to change a shape based on a cell value. I have this but it will not change fron green to red.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A100").Value >= 0 And Range("A100").Value Range("A100").Value Then
ActiveSheet.Shapes("Oval 5").Fill.ForeColor.RGB = vbRed
End If
End Sub
I would like the shape to go red, yellow and green but I can't find anything that works.
I'm trying to make a circles size (diameter) change depending on a value inputted in a cell, preferable I would like to have a limit to the sizes too so if that the circle will not have a diameter larger than 20 or smaller than 2 regardless of the values put in. the sheet will have several circles.
I know this must be documented somewhere but I don’t think I’m using the right terminology in my searches,
It's difficult to explain, the attached shows the situation better. Basically there's a picture that is somehow linked to a cell. But, you can update the picture or the cell and the other one gets updated.
View 2 Replies View Relatedi need to loop through a column of values and get each value
Dim LastRow2 As Long
Windows("SCFOutput.xlsm").Activate
Sheets("Q2SCNeg").Select
Columns("A:B").Select
ActiveSheet. Range("A65536").End(xlUp).Offset(1, 0).Select
LastRow2 = ActiveCell.Row - 1
For Each c In Worksheets("Q2SCNeg").Range("A2:A" & LastRow2).Cells
MsgBox ActiveCell.Value
Next c
i did this but it selects the first blank row (row 15) and gives me an empty message box 14 times (which is right, but i need 14 values) the reason i need the loop is because the number ov values will always change
I want to resize a shape and set the height to a specific cell. How can I achieve that?
View 7 Replies View RelatedI had getting a hyperlink from a Shape to a Cell on seperate worksheets and the macro works fine:
View 4 Replies View RelatedI am trying to get different shapes (previously created) to appear in a certain cell, dependant on the resultof a formula in an adjacent cell.
View 4 Replies View RelatedOn the attached spreadsheet there is two irregular shapes. "Area1" & "Area2". I need to be able to determine which area the "ball" shape is located in. If the ball is in Area1 then "Multiply 8" gets the ball and "Multiply 9" moves to the blue cell "AH39". If the ball is in "AreaB" Multiply 9 gets the ball and "Multiply 8" runs to cell "S37"
I should be able to do the moving of the shapes using all the samples, its determining which area the ball is in is the problem.
In the real spreadsheet i will probably have around 10 different Areas.
Move Two Objects - Select Case.xlsm‎
I have set up a rollover hyperlink so that when I hover over the cell it changes colour and creates a good effect. To do this I have another cell on the sheet that turns to 1 when the cell in question is hovered over.
I need a code so that when that cell displays the number 1. the object (which is rectangle 2) changes from white fill. to a picture I have saved on my desktop.
I cant quite figure out the coding and have been messing about with things like..
if cell range e.g a1 = 1 then rectangle 2 .userpicture " path " else .solid etc.
