ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[48])"
Dec 7, 2009I want ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[48])" to be added to the VBA code not an activecell.
Total_Sum = "=SUM(RC[1]:RC[48])"
ADVERTISEMENT
Replace Filename With Variable In "Activecell.FormulaR1C1=" Expression
Feb 27, 2009Replace filename with variable in "Activecell.FormulaR1C1=" expression
I have recorded a macro which contains several lines of code like this one:
Enhancing Conditional Formatting: Write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" Depending On The Value
Jul 6, 2006I would like to write a function that enhances conditional formatting capabilities in this way: suppose that cell(4,5) contains a number that can be 0 to 5
in the adiacent cell(4,6) i want to put a function that:
1) write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" depending on that value
2)Choose color font depending on value
3)Choose color background depending on value
I wrote this piece of
Public Function StatusResponse(AdiacentCell As Range) As String
Dim thisStatus As String
Dim ThisFontColor As Integer
Dim ThisbkColor As Integer
Select Case AdiacentCell.Cells(1, 1)
Case Is = 0
ThisbkColor = 2
ThisFontColor = 1
StatusResponse = "NO DATA"............
Wildcards With Replace ("string","s*r","k") Which Should Give "king")
Mar 22, 2009I can't seem to find out how to use wildcards like "?" and "*" in the VBA- Replace("string","s*r","k") which should give "king"
View 3 Replies View RelatedNested IF Statement (shows Values That If Column A = "build", "nextg" Or "datcen")
Feb 10, 2009In column A, I have the following values:
build
chil rat
datcen
mang
nextg
ost rat
omp
sco rat
In column B, I need the formula so it shows values that if column A = "build", "nextg" or "datcen", then the value in column B is "nextg" and if column A = "chil rat", "ost rat", "sco rat", then the value in column B is "mig". For everything else, it should be blank.
Worksheets, One With "supplier Names", "order Numbers" And "delay In Days" As Columns
Jul 19, 2007I have two worksheets, one with "supplier names", "order numbers" and "delay in days" as columns. The other one with "supplier names" and "average delays" as columns.
It is the column "average delays" that I have issues with, I need Excel to search the "supplier names" column in the "delays" sheet and identify every specific supplier name, connect the delay in days for that order and calculate the average delay in the right cell of the column "average delays" in the sheet "delay statistics".
I have tried the help files and to search this forum but I have found nothing. I have also with my knowledge tried a few different ways using the IF function but nothing so far.
This is something I started doing but it is of course far away from any truth.
=IF(;Delays!B1:B200="Greber")..............
This is the "delays" sheet where I want to find my info. Nothing in the delay column stands for "no delay"...Kinda obvious but you know... So even the "nothing" needs to be included in the calculation..
The Logic Of OR: IF Statement That Will Evaluate Whether A Variable Is Equal To Any Of The Three Punctuation Marks ".", "?", Or "!"
Apr 16, 2009tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:
View 2 Replies View RelatedSpecific Cell When Using "ActiveCell.Row Command"
Mar 14, 2007I have a sheet were i want to insert a new blank line at a spcecific point.
When a cell, ex. A28 is marked, i have made a macro that makes a new line at that point. The only thing is, i cant figure out how i make alterations to a certain cell in that row (to make sure it is the same every time.. there will be many users to the document, and i dont want any to make some big stupid mistakes by altering something, so every new row should be formatted the same way every time..)
I have recorded the following:
Sub ActiveRowTest()
'
Rows("28:28").Select
Selection.Insert Shift:=xlDown
Range("K28").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("H28,F28,E28,K28").Select
Selection.NumberFormat = "#,##0".................
UDF: How To "get To" The Cell That The Function Is Being Called From (NOT Activecell)
Aug 5, 2008Suppose I have this UDF:
Public Function test()
test = ActiveCell.Column
End Function
This is great only when I evaluate the function test() one cell at a time. When I copy this formula to other cells or perform autofill Excel thinks that the active cell is the first cell in the series.
What I really want is for the function to return the column of the CELL that contains the function, and not the cell that's last clicked.
"If ActiveCell Contains "total", Then Do X?
Feb 20, 2009Is there a way to say "If ActiveCell contains "total", then do x?
View 10 Replies View RelatedSales Growth Per Month Formula...</title><script Type="text/javascript" Src="clientscript/vbulletin_post_loader.js?v=384"></script><style Type="text/css" Id="vbulletin_showthread_css">
Jul 10, 2009In my spreadsheet below I want to be able to enter a sales number for January, the value of cell F2.
I want cells F3 thru F12 to automatically calculate according to the "Growth Per Month" value in cell H1.
Example: If January sales are 20,000, then February should calculate to 21,000 (january * 105%).
Excel Jeanie HTMLSheet1
E F G H 1 2010 Per Month Sales XXX 5% 2 XXX XXX 3 February $ 10,000 4 March $ 5,000 XXX 5 April $ 2,500 6 May $ 1,250 7 June $ 625 8 July $ 313 9 XXX $ 156 10 September $ 78 XXX 11 October XXX 12 November $ 20 XXX 13 December $ 10 14 $ 39,990
Spreadsheet Formulas Cell Formula F3 =F2*10*H1 F4 =F3*10*H1 F5 =F4*10*H1 F6 =F5*10*H1 F7 =F6*10*H1 F8 =F7*10*H1 F9 =F8*10*H1 F10 =F9*10*H1 F11 =F10*10*H1 F12 =F11*10*H1 F13 =F12*10*H1 F14 =SUM(F2:F13)
Auto Deletion Of Contents</title> <script Type="text/javascript" Src="clientscript/vbulletin_post_loader.js?v=384"></script><style Type="text/css" Id="vbulletin_showthread_css"> <!-
Dec 20, 2008As shown in the below image. I have some data from A13 to I 13. Currently if I put the cursor on A13 and press DELETE button then all the formatting from A13 to I13 goes off. But the data still remain there.
Is it possible then when I press DELETE the content of A13 then all the data from B13 to I13 should also get deleted ?
Excel Jeanie HTMLSheet2 *ABCDEFGHI13Key FieldEmp 11 2 4 8 5 3 7 Excel tables to the web >> Excel Jeanie HTML 4
Formula Not Working: "CCY Mismatch" And If "#N/A Sec" Then "Security Not Found"
Apr 29, 2009i just tried the below formulae but its not working. if Currency in col A and Currency col B is same it should be "Matched" if not same the "CCY Mismatch" and if "#N/A Sec" then "Security Not Found"
View 3 Replies View RelatedRows Of Data Seperated By A "/" Or A "," Or A Blank Space ""
Feb 24, 2009I need to prevent users from entering several rows of data seperated by a "/" or a "," or a blank space ""
We use unique identifiers (around 500,000 of them) so I cant really use a drop down box to populate and then make the user select.....
If a user populates a cell with "10005486 / 10045446" or "10005486,10045446" I want to highlight a cell red and then count the instances of red cells on another tab so I can track "errors"
I was intending to use conditional formatting when a "/" or "," or " " is used within a cell. If a user makes this error, I should probably include a msgbox saying why the entry they have made is invalid also....
Sumproduct (get The Number Of People "Occupied", "Partly Occupied" And "Available" On A Monday, Tuesday Etc Of The Current Week)
Oct 22, 2009I'm having issues with sumproduct. I can't seem to get the right info that I need. Attached is the file I'm working on. Problem: I need to get the number of people "Occupied", "Partly Occupied" and "Available" on a Monday, Tuesday etc of the current week. "Occupied" means an employee has more than 2 tasks (based on New and Active-To-Date status). "Partly Occupied" means an employee is working on 2 tasks.
"Available" means an employee has NO task at all.
Index And Vlookup: Match The "Vendor" On The "PO" Worksheet With The "Vendor" On The "Cube File" Worksheet
Nov 20, 2009I have attached an example workbook to this message, with the sensitive data removed. I am trying to vlookup the "cube" for the product number, into the PO worksheet, IF the vendor numbers match.
Example:
On row 2 of the "PO" worksheet, part number AC1000110, should have a cube of 2.5 for vendor # 11170. I'm trying to match the "Vendor" on the "PO" worksheet with the "Vendor" on the "Cube File" worksheet, and then return the corresponding "Cube", (in column F of the "Cube File"), in cell U2 of the "PO" sheet. So, what formula needs to be entered in cell U2?
Problem Using "Cells" In "Range" "400" Error
Feb 14, 2009I am having problems referrring to a range using .Cells
If I try to use the remarked code (red text), it crashes with VBA "400" error for which I cannot find any explanation. It also crashes with same "400" message if I use a cells reference to a numeric column instead of "y").
The code works using the black it loops a column in Sheet("Holidays") and loops a row in Sheet("Schedule"), then color fills 2 ranges in columns in Sheet("Schedule") when the values in 2 cells are equal.
I have attached a scaled down version of my Workbook with this code.
"PC" To Show Up If I Type A Sentence Containing "Called Parent", "Called Dad", Or "Called Mon"
Dec 2, 2009I need "PC" to show up if I type a sentence containing "Called Parent", "Called Dad", or "Called Mon". Here is what I'm trying.
View 2 Replies View RelatedMacro: Analyzes Data In 365 Sheets ( Named "1" Through "365") And Creates A Table/report With The Results
Mar 13, 2007I have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:
numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)
Next
Private Function calculateSomething(counter As Integer) As Integer
Dim strCounter As String
strCounter = counter
With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
Next
Next
End With
End Function
The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.
Date Formula; Gets To 2 Years & 11 Months The Cell Will Go "YELLOW" For 1 Month Only Before It Turns "RED"
Jan 24, 2009I have a file that uses dates, when a cell is in date the cell is "GREEN" but when the cell is out of date after 3 years it turns "RED". I want to add another formular so that when it gets to 2 years & 11 months the cell will go "YELLOW" for 1 month only before it turns "RED".
View 3 Replies View RelatedConditional Formatting: Box That If The Cell Populates "Completed" Or "X Required", Then Highlight As Green
May 12, 2009I have the folowing criteria :
1) If “Completed" , then highlight as green
2) If “Pending” , then highlight as red
3) If “In Progress” , then highlight as blue
4) If “X Required” , then highlight as green
I need to include in the third conditional formatting box that if the cell populates "Completed" or "X Required", then highlight as green. Is there a formula I can use in the conditional formatting box ? I am using Excel 2000.
Replace All Defined Range Names In The Sheet That Start With "Street" To "Road"
Jun 14, 2008I want to replace all defined range names in the sheet that start with "Street" to "Road"
For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"
I want to change them all in vba to Road-01", "Road-02" etc.
Change The Default Output Values "FALSE" And "TRUE"
Nov 4, 2008how to change the default output values "FALSE" and "TRUE" for the AND() function ? Let say I want to change them to "FAIL" "PASS".
View 2 Replies View RelatedCustom Format Which Displayed "-"(Dashes Without Quotes) Insted Of "0"
Aug 30, 2009i have a table in which some value is Equal to 0. Now i want a custom format which Displayed "-"(Dashes without quotes) insted of "0" (without quotes)
View 4 Replies View RelatedRemove "TRUE" & "FALSE" Words From A Linked Checkbox
Nov 15, 2008On the sheets where 'present' needs to be checked, after linking the checkbox to the cell it now says "TRUE" or "FALSE". How do I get rid of that? There is a formula that this affects also (just FYI).
View 3 Replies View RelatedPivot Table Showed "All" Instead Of "Multiple Items" When Using Datasource From CSV
Aug 26, 2009I use the following code to get pivot table data source throught ODBC connect to CSV. However, when I chose hide item in "Page" fields, it showed "all" instead of "multiple items".
View 3 Replies View RelatedUsing ChDrive And ChDir: Check If "PPG" Exists In "C:MEASURE-6000"
Jan 14, 2010I can not figure out why the ChDir command doesn't change to "C:" as programmed, instead it defaults to (I'm guessing last active folder) on my "D" drive (Which is "D:Test")? My goal is to check if "PPG" exists in "C:MEASURE-6000", if not use "C:" as a default.
View 2 Replies View RelatedMacro To Remove Rows That Contains The Phrase "Off Peak" And "Weekend"
Feb 27, 2009I have a spreadsheet with about 20,000 lines. (The number of rows fluctuate) I need a macro to remove (delete) rows that contains the phrase "Off Peak" and "Weekend"
I was setting it up in a loop but I don't know how to make the loop end after ALL the "Off Peak" and "Weekend" info is removed.
My macro just does a find then delete the row and then loop to the find again. Is there an easier way and can the "Off Peak" and "Weekend" statements be combined into one search
Do While
Cells.Find(What:=" Off Peak", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.EntireRow.Delete
Loop
Unable To Use "merge And Center","insert" In Unprotected Cells
Jun 2, 2006I can choose some cells in a sheet to protect them and prevent any one to change.
it's supposed to change and edit in the other cells.
the problem which I face when I try to use " merge and center" or to insert picture or autoshapes ..etc. in unprotected cells!
Changing Default Settings Of Autofilter...change From "equals" To "contains"
Jun 13, 2006When i record a macro with an autofilter, the default setting is always "equals". Is there any VBA code or anything to be able to change so that my macro will search in a "contains" setting.
View 9 Replies View Related