I need a cell to read $0.00 always even if the cell is deleted. so if blank = $0.00.
Here is what I thought would work but doesn't.
Sub Worksheet_Change(ByVal Target As Range)
Range("b5").Value = 0
End Sub
Has anyone come accross any smart and time saving alternatives to enter =NA() in any empty cells.
View 9 Replies View RelatedIn some of my formulas, I need to re-enter the contents of the cell in order for a formula to compute.
For example, I am doing some math on a cell whose original contents are "10:21:00 AM" The formula returns a #NUM!. If I retype 10:21 in the cell and hit enter, the formula will now calculate properly. I have reformatted the entire column several times so that it is a custom hh:mm format. The original content also shows as time so I don't think that it is a formatting issue.
My worksheet has about 20,000 rows and this only happens for about 100 of the rows. It is pretty time consuming to manually go fix each one. Any ideas what is going on here?
I'm trying to get a formula that will enter a particular value when 2 specific criteria are met. I've been trying to use an =IF(AND( statement, but so far I keep getting a "the formula you typed contains an error" message.
I need to have cell V9 return a value of 8 if the following conditions are met:
Cell D9 = "SG"
Cell H8 of a different workbook (Called Work Schedule) = 3
I would like the value of V9 to change to 12 if:
Cell D9 = "SG"
Cell H8 of 'Work Schedule' = 19
My formula now looks like this:
=IF(AND(D9="SG",'WORK SCHEDULE'!H8=3,8,IF(AND(D9="SG",'WORK SCHEDULE'!H8=19,12,""))))
If I use format cells and choose time... do I have to enter the time in decimals to get it to show up like 1:30 PM? Or is there a way for me to type something quicker like 130 p?
View 9 Replies View RelatedI'm trying to copy about 10,000 barcode over to a new spreadsheet, the prob is that when I copy them over they appear as XXXX+12 and then when I try and use them it doesn't like it. I can solve it if I format the column to text - and then double click it. This is ok for a hundred or so but fingers start to hurt after that!
View 14 Replies View RelatedI would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month:
Select Creditor:
Enter Payment:
New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
I really DO have a problem. I need excel to create me a list of whenever someone enters their name. Now, this may sound simple, but, the text in the list CANNOT be allowed to be overwritten.Also the text needs to be entered into the NEXT empty cell between C30 and C50. This is basically so I can keep a record of whenever anybody does anything/modifies the cells that include text between S7 andS21
So basically if text is ever entered into S7 all the way down to S21, I need an automatic copy created into the next available cell between C30 and 390. It is also ok if there are duplicates values(names).
I would give a print screen, but I don't know where the attach button is on this forum.
I'm building a database which I intend to upload to an ecommerce website. I am retrieving information on some of our products from the manufacturer's website. The information appears on the manufacturer's website as follows:
Part No : DP9970
* Description : Disc Pads
* Manufacturer : Trupart
* Unit Of Sale : Boxed Set
* Width (mm): 130
* Height (mm): 62
* Thickness (mm): 19
* Note: 4 pads with top clip
When I copy and paste this info into excel, it gives each individual line of information a separate cell. I need the information to appear in one cell in order for it to display properly on my website.
I need to find a way to enter times into excel without having to use a colon.
The data I need in the cell is the hour, the minute, and either AM or PM. No seconds, which I believe is why Chip Pearson’s time VBA procedure doesn’t work for me, and I don’t know enough about VBA to change it for my needs.
So for example, I need to enter in times like 10:09 PM or 12:40 AM or 2:33 AM.
All my times will be entered into Column D.
I have done a ton of searching and tried several different solutions, but none of them allow me to put in AM or PM. If I just put 1009 into a cell with Chip Pearson’s macro, it will display as 10:09 AM, which is great if I need it to be AM. But if I need it to be PM, and I enter in “1009 p”, it will display as “1009 p”, neglecting the colon and not displaying the time correctly.
Does anyone have a solution where I can type in a 3 or 4 digit number (233 or 1240) and either “a” or “p” after it, and have it display as 2:33 PM or 12:40 AM?
I use Excel 2007, if it matters.
In simplicity: We want to be able to enter a value in cell A1 (and hit enter), but we want the value in A1 to be increased by the value saved in H1.
So: if H1 had 250 saved in it, if we enter 150 in A1 (and hit enter), we want the value in A1 to become 400.
I work in a fruit and veg business and they are using parchment and quill still, well it certainly feels that way. I have been asked by customers to provide an online order form which i will email to them and they can fill in and email back, i scanned an invoice and used OCR to upload it to excel and it has come out quite well.
Basically there are three columns of product lines and then directly to the right of each product is a column i have entitled Box & KG respectively, if the customer types a number into the box column i want it to automatically add 'Bx' to the end so this if they typed 5 in a cell in the box column it would look like this '5 Bx' (obviously without the quotes), and the same for the KG column but it would denote 5 KG instead, also i would want it to add in the space as well after the value.
I have a column of values, and I want a column next to it that shows the value in every second cell in the first column, but right underneath. See below for an example of what I am trying to achieve. The actual column is 17000 cells so I cant do it manually.
12
24
36
48
510
612
7
8
9
10
I have a list of number I want VBA to add to a sheet. right now i have a VBA to add it one by one but was wondering if there was an easier way.
EX.Range("A1").Select
ActiveCell.FormulaR1C1 = "78"
Range("A2").Select
ActiveCell.FormulaR1C1 = "275"
Range("A3").Select
ActiveCell.FormulaR1C1 = "280"
Range("A4").Select
ActiveCell.FormulaR1C1 = "765"
I am a beginner in VBA programming and I have to make a Userform for entering data in the specific places(in a table) in the worksheet. But when I enter a number, it changes all the cells to that number. I can't find my error.
View 7 Replies View RelatedI have few sheets in a workbook and last sheet named report, I update each sheet date wise except report, problem is that the report sheet contains formulas of last rows sum, if I update today date report then I should change all formulas in reports. Kindly see attached file.
View 5 Replies View RelatedI have a macro that does the following.
-Every time a cell in Column D contains New or Old, “Customer” is entered into Column G in the same row.
-Every time a cell in Column D contains Old2, “Assets” is entered into Column G in the same row.
-Every time a cell in Column D contains New2, “ShortTerm” is entered into Column G in the same row.
-Every time a cell in Column D contains Old3, “LongTerm” is entered into Column G in the same row.
Here is the macro.
I have two files which are attached.
One is the main report file "ops report.xls" and other is the raw file (Air.xlsx) from which data is to be added to this file. i manually take the data and enter it into the ops report everyday. This i want to automate as there are many such sheets to be completed (i have mentioned just "Air" sheet here in ops report).
Now what exactly i want to do is?
take data from Air.xlsx workbook as per the columns in the Air sheet in Ops report and paste it on that particular date row. this i will have to do everyday so the nest day the data will be pasted on next row against the date. The yellow columns which i have marked have formula so you need not touch that columns.
I nee to paste the average of Air sales and Air service. so we have to take average of row 43 and row 89 for first two columns in ops report. and then average of row 44 and row 90 for column L,N,O,P,Q in the ops report.
Afterwards the average of column J must be pasted in "Interval" sheet in ops report.
This is a bit tough but i know there are many genius people out there who can easily solve this.
If I have 3 text boxes textbox1, textbox2 and textbox3
I want ot be able to enter a time in textbox1 and then a time in textbox2 and textbox3 would give the difference. i.e. 09:00 17:00 then textbox 3 would calculate 08:00
Then all 3 times would automaically be entered in Sheet1 A1,B1 and C1
I will have to insert the below table in the worksheet area whereever I am working. Batch No.Doc. No.DateRemarks22-May-12
Fox example I require the "Batch No." in the ActiveCell and "Doc No." in the same row next column, "Date" in the same row and next column and "Remarks" in the same row next column. I also require the Today's date below the "Date", by using =Today() function of excel.
I will have to repeat the above table over 100's of times.
I need a VBA code for entering dates. The start date cell is A15.When I select cell A15 and hit macro button it inserts todays date and fills in 1-month of dates in column A excluding the weekends...
View 3 Replies View RelatedI have a formula that I'm using which works and gives me the right value.
Code:
=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)
However when I try and enter the exact same formula through VBA using, I get "Run-time error 1004: Application defined or object defined error". I don't see why the formula will work when I enter it directly into Excel but it won't enter the formula through VBA.
Code:
ActiveSheet.Range("H4").formula = "=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)"
I have tried to create a simple formula, but I am unable to make it work correctly. I need a * symbol in a column if the number to the left is greater than zero, and blank if left cell is also blank. My current formula that does not work displays the symbol if there is a blank or number in the left column. If this is needed, the formula A5 to the left reads another worksheet and displays a "" blank if no data.
Current formula that needs correction. B5 formula reads A5 and should enter "*" if >0 and "" blank if A5 is blank.
=IF(A5>0,"*","")
The current formula is incorrectly returning * if A5 is blank or if it has a number greater than zero. Correct formula that will return the correct results.
Earlier this morning I had a telephone call from a customer who uses a spreadsheet I created.
She explained that once she'd entered information into a cell [and clicked elsewhere] she couldn't then add to the former; rather, she had to click on the cell and type it all out again.
I assumed that, as an inexperienced excel user, she was unware of either double-clicking the cell, or selecting and pressing F2. I explained these methods to her.
She later emailed me with this:
"Further to our phone conv this morning I have just tried double-clicking on cells but it deletes everything in the cell rather than lets you edit what is there – F2 does the same thing. This applies to any cell, whether it is text, date or number."
I've been using excel for a while now, and I've never come across such a thing before.
Has anyone else? Could it be to do with her own excel settings? (other customers have had no such problem)
I have the bellow code, I'd like enter the input on the next available row on column "A" instead than on "A2"
Dim strResponse1 As String
strResponse1 = InputBox("First Name", "Hey you !")
If strResponse1 = "" Then
MsgBox "You have chosen not to participate!", vbInformation, "What happened?"
Exit Sub
End If
Range("A2").Value = strResponse1
I am currently working on a button which does the following:
User wants to create a new sheet
Clicks button
Input box prompts user to enter sheet name
User enters the sheet name
The sheet is created and copies over formats and values from another sheet
I then want the user to also enter a date.
The date must then be places (always in "C2") then each second column after that the date entered + one day.
E.g. C2 = 01/08/2008 then C4 will have 02/08/2008
I want this entered for the whole month.
The code is below:
Sub copyformula()
Dim vsheet As String
Dim vdate As Date
vsheet = InputBox("Enter a sheet name:")
My VBA sub contains
DIM sFormula As String
DIM rCell as Range
at some point in the code, the value of sFormula is set to something like
=ROUND(SUM($A$5:$A$10*$B$5:$B$10),2)
(not really this formula but of that flavour)
later in the code I have an instruction
rCell.FormulaArray = sFormula
(rCell is set to a single cell at the time)
When I run the code and then examine the cell, I find that the cell's formula has been correctly set to
=ROUND(SUM($A$5:$A$10*$B$5:$B$10),2)
however there are no {curly brackets} surrounding the formula to suggest that it has been entered as an array.
i am a rowing coach and have to enter a lot of splits every day after practice and am hoping to find a way to do it without having to use the : or . keys.
so a split like 1min. 27.5sec, displayed as 1:27.5 could be entered as 1275.
i see that there are some questions already about this but seem to just work with H:M:S and time of day, i tried but wasn't able to use these for my purpose.
another fly in this ointment is that i also enter heart rates on the same sheet so in A1 it would be splits A2 heart rate, A3 split A4 HR.....
i do this for approximately 40 rowers each day with different workouts each day so each worksheet has splits and HR in different rows and columns.
a colleague recently used my pc and "messed it up". when i enter a value in any cells, it is divided by 100, eg, when i enter 1, it shows as 0.01.
View 2 Replies View RelatedI have a basic script in VBA set up to enter standard information (street address in one block and city in another) on a website that has no fees or password requirements. If I step through the code using F8, this works great and the website returns the data for the address that was input from my excel sheet. However, when I assign this same macro to a control button on the excel sheet to get the code to run automatically, the website comes up to the default screen with nothing entered in either of the blanks. Again, when I just step through this, I get all the way to the output page on the website with the resulting data from my input data. For some reason, this does not happen using the same code connected to a button for automation purposes.
View 9 Replies View Related