If a cell contains the number 39448 is there any way for a macro to determine if the user typed "39448" or the user typed "01/01/08"?
In other words, I want the macro to be able to identify that a date with separators was entered rather than a number.
In 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)
As 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
I 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"............
I 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 RelatedI 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 RelatedIf I want a procedure to be executed if a .value meets multiple statements, is this possible to do on one line, or do I have to nest the IF's? for example, I want it to execute if its above 0 but below 100?
View 5 Replies View RelatedIn 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.
I 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..
tell 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 Relatedi 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 RelatedI 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....
I'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.
I 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?
I'm trying to protect cells based on what's input in column E. Here's what I tried, but it doesn't work. It's giving me a "Type Mismatch" error on the word "Annuity" where it appears first.
View 2 Replies View RelatedI have attached a file called Test2 which has a public function called DBLookup. The function looks in the attached access database simulating a Vlookup just like in Excel. The problem I am having is when that data appears on sheet1. I am getting #VALUE in the cell and the erroe states "a used value in the formula is of the wrong data type".
my
Dim adoCN As ADODB.Connection
Dim strSQL As String
Const DatabasePath As String = "C:Test2.mdb"
'Function argument descriptions
'LookupFieldName - the field you wish to search
'LookupValue - the value in LookupFieldName you're searching for
'ReturnField - the matching field containing the value you wish to return...
I am trying to use a match function in VBA to return a value when a number of values in a row match a certain criteria (I have tried the Find approach on the Ozgrid help pages but it is not what I am after). My problem using a match function of course is that when there is no match the code returns a type mismatch error. I have tried to circumvent the error problem using an "On error resume next" but this then delivers a match for all rows. Anyone have any thoughts about how I can get around this problem?
Sub DataSum()
Dim strData As String
Dim i As Integer
i = 0
Sheets("Sheet1").Activate
Range("B4", Range("B4").End(xlDown).Offset(0, 0)).Select
For Each cell In Selection
If cell.Value = 0 Then
i = i + 1
Else
strData = Sheets("Sheet1").Range("$B$4").Offset(i, 0)...........................
I 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.
I'm getting a type mismatch error on a .find method after i added the "After:=" arguement. without the arguement, no error. here is the code
View 10 Replies View RelatedBelow there is code for some inputboxs as well as some if statements on their results. The Exit sub situations work fine but If i do actually put a valid answer in the secod input box then it results in a mismatch error on the OR statement... why the first inputbox works fine but the second one gives me a type mismatch error when a valid input is given? Also if there's a more efficient way to do this please feel free to correct the code.
View 4 Replies View RelatedI'm trying to use the following formula to filter data based on "Service Type" and it seems to work on one of the service types but not the other. here is the formula
=IF(LEFT(A5,1)="0","Both",IF(OR(A5="SERVICE",A5="REVENUE",A5="REVENUE",A5="CIC",A5="RAO",A5="CARRIER NAME",A5="TYPE",A5="RECORDS",A5="INCLUDING TAX",A5="BEFORE TAX",A5="FEDERAL TAX",A5="PROVINCIAL TAX"),"Both",IF(LEFT(D5,3)="REG","Table1",IF(OR(D5="900"),"Table2",IF(A6="",IF(B6="","XXX","TRUE"),"XXX")))))
Notice the highlighted red text, this is where I'm trying based it on "Service Type". I have also attached on example workbook, the items highlighted in green should marked as "Table1" and items highlighted redish orange should be marked as "Table". Wierd thing is that I use a similar formula in another workbbok and it seems to work fine.
I always have issues with this and it's always the same thing I've done wrong.
This is the beginning of my
Dim cell As Range
Application.ScreenUpdating = False
Sheets("regrade pharm_standalone").Select
For Each cell In Range("standaloneState").Cells
If cell.Value = "NSW1" Then
When it gets to the last line I get a "type mismatch" error.
I have a spreadsheet with the following headings:
A
Claims Number
B
Name
C
Scheme
D
Admin
E
Date
I need to create a user form that will "pop up" and ask the administrator to enter the above details.
A - should be created automatically (ie last claim number + 1)
B - user enters manually
C - data retrieved from a list
D - data retrieved from a list
E - date is the date the information is entered
I keep getting a type mismatch when I run this code. The code worked fine until I added the last condition to check if the left most character of the active cell is a number.
Do While ActiveCell.Value vbNullString
If 1 >= Range("L" & ActiveCell.Row).Value / Range("AS" & ActiveCell.Row).Value And Range("L" & ActiveCell.Row).Value / Range("AS" & ActiveCell.Row).Value >= 0.9 And IsNumeric(Left(ActiveCell.Value, 1)) Then
myCountA = myCountA + 1
myCandA = myCandA & " " & Range("B" & ActiveCell.Row).Value
End If
I 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.
I 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 RelatedI 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.
I 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.
the task for the code below is to search through rows in array and check if there's no "text" in it like user inputed in box. But it always shows that there's no such email,i can't figure out the mistake, Maybe the problem is in the type the InputBox returns? But i've tried entering type := 2 (text),dunno what else i can do...I've also tried using p = InputBox("Please,eneter email!").
View 2 Replies View RelatedIf cell K6= "ERROR" I need a message to pop up on screen with the message "Wrong Entry click OKAY to find, Click CANCEL to ignore". Clicking OKAY would simply go to worksheet named "MONITOR". Clicking Cancel would just clear the pop up from on screen.
View 5 Replies View Related