I'm inserting a function in a cell via code:
[Code] ........
The problem is that I want the second cell of the NETWORKDAYS function (in this case: R2C11) to be a variable cell. I want it to be cells(2, c - 3).
Another interesting dilemma to solve. Using this formula:
View 2 Replies View RelatedI'm using IF functions to get the difference of two cells. An example:
View 5 Replies View RelatedThe code below pulls information based on what i specify in a userform from another sheet.
I am trying to total the last 3 columns but for some reason the code sticks the sum formula right in middle of all my information. However, when i run the code (the exact same way) again then the code puts the sum underneath the last row as indicated in the code. How can i get the code to run right the first time around?
I have around 700 workbooks in a folder on my PC.
My master file(master database.xlsx) is kept open from which i have to Vlookup the values into these 700 workbooks.
The "agent Id" column is common in both the master database.xlsx as well as those other workbooks also.
The thing i want to do is i need the data from the "reporting supervisor" which is in the master database.xlsx file to be available in the G column of the other 700 books ( i have attached only 4 sample books). The G column can be then named as "Reporting Supervisor". Since "Agent ID" is available in both files we can vlookup taking this as a base. I have attached the 4 sample files out of 700 as well as the master database file in a zip file.
I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?
Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C
Function to count used rows....................
Excel 2010. I need to place picture into one cell or one big merged cell, as a background fill. picture must resize to size of cell. must be fixed in, not in front. i still need write into that cell, so it needs to be really background.
View 2 Replies View RelatedI have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.
View 3 Replies View RelatedI have a VLOOKUP formula in cell X1. I am trying to insert all the below 10 IF statements in it. I have three problems:
A) Excel does not allow inserting that many conditions in one cell.
B) Don't want to use VBA.
C) VLOOKUP doesn't work, since the table array contains formulas. I can't have the table arrays change from 1s to 2s, when copying & pasting X1 to X2.
1. IF(A1=1,C1*D1)
2. IF(A1=2,E1*F1)
3. IF(A1=3,G1*H1)
4. IF(A1=4,I1*J1)
5. IF(A1=5,K1*L1)
6. IF(A1=6,M1*N1)
7. IF(A1=7,O1*P1)
8. IF(A1=8,Q1*R1)
9. IF(A1=9,S1*T1)
10. If all false then "NO".
I have two images that have a name on each sheet for reference. One is a thumbs up image, one is a thumbs down. They are both named tu_image and td_image respectively. I want to show the thumbs up image if a particular cell has a value of 10 and show the thumbs down image if that cell's value is < 10.
This check and display needs to be done about 20 times on different sheets and with different values. (i.e. Thumbs down should only show when the value is < 20, instead of 10.) Ideally this should take place when the workbook updates with new data. Is this possible?
I have a simple formula, =IF(A1=0,"",A1) to replace 0 with blank. However ISBLANK doesn't recognize the result as blank, because it's really testing for an empty / unused cell rather than a blank one.
Is there something I can replace the "" with so that ISBLANK returns true?
Is there a way to insert a pdf into a cell comment? I found how to insert an object but when I do that it covers the entire spreadsheet. I would like to be able to add pdf's to cell comments.
View 3 Replies View RelatedI'm trying to insert a formula into cell "A8" when cell "A1" changes. These formula gives me (into cell "A8") the next week day, depending on the value of cell "A1". If A1="FRIDAY", "A8" will be 08-11-2013.
The problem is that an error ocurred (Run-time error '1004'). If I put the formula directly into cell it works...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
ActiveWorkbook.Worksheets("Sheet1").Range("A8").Formula = "=TODAY()+8-WEEKDAY(TODAY()-MATCH(A1;{""MONDAY"";""TUESDAY"";""WEDNESDAY"";""THURSDAY"";""FRIDAY""};0))"
End If
End Sub
I am currently using excel 13' and am having trouble inserting rows once the value in column b changes. My goal with this procedure is to insert two rows after each change in value in Column b (i.e. b2, b3, and b4, all equal 123 however b5 equals 124) Below is the code.
Dim cell As Range
Set i = Range(Range("A1:I1"), Range("A1:I1").End(xlDown))
Set bsort = Range(Range("B1"), Range("B1").End(xlDown))
Set ISort = Range(Range("I1"), Range("I1").End(xlDown))
[Code]....
'Need to Identify which cells are not equal to the one above in column B (bsort = range)
For Each cell In bsort
If cell.Value cell.Value - 1 Then
ActiveWorkbook.ActiveSheet.EntireRow.Insert
ActiveWorkbook.ActiveSheet.EntireRow.Insert
End If
Next cell
End Sub
Is it possible to insert clip art into a single cell...or is it possible to create a custom symbol?
View 9 Replies View RelatedI am looking for a way to insert a picture into a cell BUT the picture should not float. It should change its size whenever the cells size changes - as if it was inserted into a rectangle drawn from the "PAINT" Bar. An example (but it floats) can be seen in the attached picture/
View 2 Replies View RelatedI want to select any cell on a spreadsheet, then run a macro that inserts a comment to the selected cell with the inserted comment text being the selected cell entry (value/text/ date). Primarily to insert a cell date value to comment before overwriting it with a tick to represent completion. The comment then contains the date for archive info purpose.
View 2 Replies View RelatedThe following code sends the open workbook, as an attachment, to a predefined email address. It then inserts the date it was sent in a cell in another workbook. It works ok in three other modues going to different addresses, it works ok on other networked PCs. Unfortunately on one PC we get the error "Can't Find Project or Library". Initially this pointed to an undeclared variable, when this was declared, it pointed to another. All variables have now been declared but it fails and points to the DATE command".
VB:
Sub To_Design()
''
'=======================================================================================================
'This emails the current open file, which is named after the Log Number, to the Originator of the Doc 34.It opens up
'the Doc 34 Log and enters the date that it was sent in Column L. The email addressee has to be manually added as it
'could have been originated from anywhere in the company.
'=======================================================================================================
[Code] ....
My situation is like this:
-I have two sets of data in two different sheets. The data is similar, but it is taken in two ferent times, so there are some cells, which content has changed.
-I want to make a macro that identifies the changes in the latter (newer) sheet compared two the older one and to:
a) Highlight the cells (this works)
b) Add a comment to each cell that has changed so that the content of the comment is the content of the "older" cell. (so that one can easily see from the newer sheet that highlighted cells have new values and the old values are in the comment) (this is not working)
Here's my code so far. Like I said I can't make the comment work. I tried it with the same text in all comments ("test comment") and it worked but I can't make it to go and get the data from my older sheet.
OLDER = my sheet with old data
NEWER = my sheet with updated data
NEWTABLE = my table on the NEWER sheet where I want to check the changes
[Code].....
I'm using excel 2003 and was wondering if I could insert html around the cell data in a faster way than cutting and pasting it. Here is an example of a column and what I would like to do.
aaaa
bbbb
cccc
dddd
Now I would like every cell to have HTML around it to be easily posted on a website. I would like the cells to now look like this.
<a href="www.url.com/aaaa"><img src="www.url.com/aaaa.jpg"></a>
<a href="www.url.com/bbbb"><img src="www.url.com/bbbb.jpg"></a>
<a href="www.url.com/cccc"><img src="www.url.com/cccc.jpg"></a>
<a href="www.url.com/dddd"><img src="www.url.com/dddd.jpg"></a>
I haven't dealt much with functions so I don't even know if this is possible.
I have a column of about 15,000 numbers that I need to update. They are all 6 digits long and I need to insert characters at 3 different points. The numbers currently look like
234567
123456
456789
and need to be changed to
23-45-67
12-34-56
45-67-89
I have a long row of text cells in a range. Starting with the 1st cell I want to insert 3 empty to the right, then repeat for the next cell that had originally butted up against the first cell until 3 cells have been inserted for each cell in the range.
View 2 Replies View RelatedI go to last used row for column C and then go to next row column A and insert a check box and link that to same row in z column. The code which I am trying is not working.
lastcell is last used cell in column c. Say C8 (c8,c9 are merged). A to column H are merged.
[Code] .....
I dont know why i am unable to go to A10 with the above code.
and for check box i am try with macro recorder.
[Code] .....
Checkboxes are inserted with every new row. I am able to add row and merge the rows and I am struck at adding checkboxes.
I have this code for setting up a sheet to begin a calculation involving the solver add in. I am getting a "Run Time Error 13: Type Mismatch" on the line that inserts a formula into the cell. I have put the first offending line in bold in the code below. Is this a problem with syntax or is there something else that I have made a mistake on?
View 2 Replies View RelatedI need to have a cell (Total) but I need to be able to enter the information which all the subtotals are inputted into that area and will be the subtotals of the total. I have two areas that I will need to keep a running total of for each job #.
I'm not sure how to find out how to do this as I'm not that advanced with excel.
I want to be able to a place a picture on a workbook based on a cell. In column A, I will run a sql query to pull a list of items. In column B, I have the items formated thus they have a the .jpg. next to item of the picture i.e Hammer.jpg. whereas column A lists Hammer and Column B Hammer.jpg. In column C, I would like the pictures to reside in this column.
So far I have this macro but I can only get two pictures to come up even though my list has 4 items on it.
Sub pic()
Dim x, c, d, a As Long
d = 2
c = 40
For a = 2 To 6
b = Cells(a, 2)
I'd like to insert the value of a cell in Excel worksheet A into the cell of another Excel workbook B. The twist is that the Worksheet A begins as a templated file and is copied into new jobs each time. The destination spreadsheet B location and name stays the same but the source file changes location. So the destination file B can't be looking to be updated. What needs to happen is that once the source spreadsheet A is copied into a new job, whenever the spreadsheet is updated and closed, it needs to "export" the new value into the source file B. Not the other way around as the destination file will not know the location of this new source file. Sorry for the confusing syntax but hope this is clear enough for an answer. Here's an example of the question: How do I automatically, on closing this source workbook A, update cell D24 into file C:MyFilesExcelFormerFile.xls Sheet1 cell B27?
View 2 Replies View Relatedhow to insert the value of a single cell into a text box... is it possible to insert a range of cells into a text box, and keep the same formatting, spacing, control boxes etc?
Reason behind this:
I have some information in a range of cells, let's say A10:D20 and I want to have a nice fill colour for that range... my thoughts were to copy this data into a text box, and use the fill properties to make it look pretty. but I've found that I can't do that..
My next attempt was to set the transparency of the text box to 100%, put some fill in it with a nice orange colour, and place the text box over the cells I wanted to look pretty... When I did this, it worked ok for the cells in the lighter portion of the colour, but when the colour became darker it would block the cell data underneath it...
I am trying to create a formula that references information on two different sheets, but i want it to show me the values that are newly created when a new row is inserted. The two current sheets are Master and Data.
I have a formula on Master that reads ='Data'!$K$3 to reference the value on Sheet data in cell K3. When a new row is inserted showing me the current readings, I want the Master sheet to show me the NEW value in cell K3 on the data sheet. The problem is, the insert of a row moves the formula on the Master sheet to now be ='Data'!$K$4. How can i STOP the insertion of a row from changing the formula so i continue to see the value i need?
Imagine I have a cell with validation and I need to add to my list. My list is Cat, Dog, Frog, Parrot and I need to add Elephant. So I tab down to Source, I want to right arrow to after Dog and type Elephant, but as soon as I hit the right arrow key it replaces the full list with an absolute reference to the selected cell. Is there any way I can get this to not happen so I can instead use the arrow keys to edit the text in the same manner as when composing this text.
View 2 Replies View Related