Why is it that I recieve a #NUM! error when I enter a number (monthly payment) of 233 or less?
The formular I use is: =NPER(C79/12,-E79,D79)
Error:
C79 - 7%
D79 - 40000
E79 - 233
F79 - #NUM!
This works:
C79 - 7%
D79 - 40000
E79 - 234
F79 - 1008
I must have added a line of code like this in a VBA module a thousand times before and I have never got an error. The main code is run from the Worksheet Change event, which is ...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Enter_Container_Category
End Sub
Code:
Sub Enter_Container_Category()
With ActiveCell
Cur_Row = .Row
Cur_Col = .Column
End With
[Code] ..........
The error message I am getting is...
Run-time error '-2147417848 (80010108)':
Method 'Value' of object 'Range' failed.
The following code works like a dream for me, but if was to enter the wrong letter, not "N" or "R" and i tried to delete it, a debug message appears?
Is there a way to allow any letters to be deleted?
Here is my code
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 17 Then
With Range("C" & Target.Row & ":R" & Target.Row).Interior
Select Case Target.Value
Case "R": .Color = RGB(184, 204, 228)
Case "N": .Color = RGB(120, 120, 120)
Case Else: .Color = RGB(255, 255, 255)
End Select
End With
With Target.Font
Select Case Target.Value
Case "R": .Color = RGB(184, 204, 228)
Case "N": .Color = RGB(120, 120, 120)
Case Else: .Color = RGB(0, 0, 0)
End Select
End With
End If
End Sub
I having a bit of trouble entering numbers in Excel, every time I try to enter a number it seems to divide it by 100. For example : - 1 entered in to a cell becomes 0.01, 100 becomes 1....etc. I've tried formatting the cells and had no luck, tried Tool>Options but not sure where to look.
View 2 Replies View RelatedI have some code I wrote that copies sheets from another sheet in another workbook to my current one, in order. However, depending on activity within these sheets, there might not be a sheet for the month.
I have the on error code to go to the next argument, however I would like it to tell me on my errors sheet that it did not exist.
The error sheet will simply have 2 columns of copied sheets and non-existent sheet.
VB:
On Error Resume Next
Workbooks("GLTemp").Sheets("981715").Copy After:=Workbooks("MHSGMR").Sheets("981715 Budget")
Workbooks("MHSGMR").Sheets("981715").Activate
sheetName = ActiveSheet.Name
Workbooks("MHSGMR").Sheets("981715").Select
ActiveSheet.Name = sheetName & " GL"
[Code] ......
I have been doing vlookups for ages and have never seen this error. The vlookup works in some columns but not in others.
View 9 Replies View RelatedI am facing few problems with excel ever since I updated windows 8 to windows 8.1. Whenever i try to enter a decimal number in excel, say for eg. 3.4, it reformats to date- Apr03. I even changed the date and time formats for eg. short date is formatted to dd/MM/yy and long date to d MMMM yyyy. The format is set to enlgish (uk).. what should i do?
View 1 Replies View RelatedI am working on a spreadsheet and I want to enter a number from a table and
have the associated values from the table transfer with the number into my
spreadsheet. What functions and formulas should I use? (Below is the Table,
the PTM# is the one I would query for.)
X L/R Y PTM#
0.54R0.5820
0.82R0.5021
0.66R0.7322
0.06L0.2723
0.03R0.1324
0.55R0.2925
In my excel spreadsheet I used to be able to enter a number then use my arrow key to move one cell to the right and then enter the next number. Now when I enter a number in a hightlighted cell and use the arrow key to move one cell to the right the cell does not move my page shifts to the right but the highlighted cell stays in the same spot. I must of hit some kind of key combination to cause this. how to fix this?
View 3 Replies View RelatedHow can I enter a number of space in the text file and yhis number is existing in other cell.
Example:
=CONCATENATE(G4," ",I4," ",J4," ",K4," ",L4," ")
the number of spaces is given in other cells.
First space is F1 = 4
Second space is F2 = 8
and so on
I am trying to create an input box where the user can only enter a whole number between 1 and 99. i can create the input box but am having trouble specifiying it can only be a whole number between 1 and 99.
I have a long list of items that I need to add what I call tag numbers too. I need the numbers to reset at the top of every page
page 1
item1 101
item2 102
item3 103
item4 104
page 2
item5 201
item6 202
item7 203
Is there any macro or formula that anyone knows of that will reset my numbers on every page break?
I have some 9digit numbers and i have to enter the number 9 afther the fourth digit. Example : 50200491 - turned into 502900491.
View 5 Replies View RelatedI have a Challenge want to delete the row values in which serial no enter the textbox1.
Conditions:- find the Serial no. and delete the values except serial No.
Attaching file for reference : Example.xls
I'm trying to enter text (the Assessment Number) into a website text box. the site is Solano County - Online Tax Information the relevant section of website code is
My code is
Sub Solano_View()
Dim IE As Object
Dim url As String
Dim tags As Object
Dim tagx As Object
Dim parcel As Object
[Code]...
This is creating Run Time error code 438 I've also seen Run time error code 91 while trying other methods
I have also tried getElementById() without sucess
Does this have something to do with tables or java?
Should I be using $("#id") I don't know how to use this though.
I have numbers in b2 to b5 say
10
22
35
50
I have numbers in c2 to c5 say
1000
1580
3000
45000
I have numbers in cells d1 to kq1 (1 to 300) d1=1, e1=2, f1=3 and so on
What I want to do is:
In D2 through to kq2 it puts the number in to the cell that correspnds to a multiple of the number in b2.
That would put 1000 in cells m2,w2,ag2,aq2, ba2 and so on
In D3 through to kg3 it it puts the number in to the cell that corresponds to a multiple of the number in b3
that would put 1580 in cells y3,au3,bq3,cm3,di3 and so on
I am needing a custom format for the following. I need a cell entry to be 1 letter, three numbers, a dash, 5 numbers, a dash, two numbers, a dash, and then two number. Example: A109-54785-13-00. The first letter will almost always be an "A."
To further complicate the matter, the entry is copied from an email and pasted without the dashes. In the above example, the number in the email would read, "A109547851300." I copy it from the email and paste it in the proper cell in the worksheet. I would like the custom format to automatically enter the dashes at the appropriate spot.
If it was all numbers, this would be easy. I created a custom format for the numbers, but when the A is included, Excel no longer treats it as a number and the custom format did not work.
Is this possible in Excel (without VBA).
I could use the custom number format I created and then later go back and add the "A" at the beginning, but that is as much work as manually adding the dashes.
B20 is the number that the user enters. It can be any number and it will create new sheets below and text of sheet name in B21:F21.
If i change the sheet name below, B21:F21 would also automatically change.
We're trying to use one template to enter data with users being able to start entry from a number of different starting points. this changes they way the look-ups have to work. The attached sample explains it a whole lot better.
View 2 Replies View RelatedI have about 20 files that all have separate passwords. When I enter the password for six of the files, I receive the "Unable to Read File" error message.
View 5 Replies View RelatedI really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
[Code].....
This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.
View 3 Replies View RelatedI have a column with a sum formula at the bottom. One of the entries in the column is an if formula that reads
=IF(and(c17="Yes",c15=32),d16*f12,if(and(c17="Y",c15=44),d17*f12,"Error"))
Even when the answer is Error, the sum formula adds the balance of entries. I do not want it to provide a sum in this case...I want an error message.
Macro Goal - collect specfic raw data on sheet 1 and paste relevant data as Values on sheet 2
Error - I get the ERROR 400 pop up once I include the instructions to paste the raw data to a new sheet. (The code to paste works on its own, just not when I add it to the end of my existing code)
Sub sort()
Range("A1:CH327").sort Key1:=Range("CH2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("CF5").Select
Range("CI2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=R[-1]C[-1],"""",SUMPRODUCT(--(R1C86:R327C86=RC[-1]),(R1C11:R327C11)))"
Range("CI2").Select...................
I looked in the FAQ for this forum and I found something similar to what I am looking for. I tried entering the formulas to sum the nth number, the problem I am running into now is that I would like to have the sum at the end of the column I am summing. I know this formula will not allow this, is there anything else I could try?
I have various sections in my spreadsheet, each section has a macro that would enable the user to add additional lines to each section as needed. The problem I am running into is that once the new lines are inserted, the formula to add every 3rd lines omits the second last cell it needs to add in the formula. I want to add G13+G16+G19+G22+G25+G28+G31+G34
Then I hit the macro button to add 3 more lines and now I want the formula to add G37 ito the list of cells in the previous the formula, instead I get
G13+G16+G19+G22+G25+G28+G31+G37
and if you add 3 more lines you get G13+G16+G19+G22+G25+G28+G31+G40. Why is this happening and how can I correct it?
I get spreadsheets sent to me with hours and minutes in decimal format. Frequently a quarter of an hour or three quarters of an hour are posted as 0.15 or 0.45 instead of 0.25 and 0.75. Other odd fractions can also appear.
I want an error message if the value when divisible by 0.25 is not a whole number.
Is there list anyway of all the error numbers and their descriptions?
View 2 Replies View RelatedI work a lot with PCOMM and I have a macro that I can't get to work for the life of me. I have tried error handling, but unless the code passes the test it just gives me the 1004 error.
HTML Sub Testvlook()
Dim Calc1 As String
Dim Res As Variant
Dim scalc1 As String
'Find the TOPS window
If Loops = 0 Then
Set TOPS = New AutSess
TOPS.SetConnectionByName "A"
End If
Loops = Loops + 1
scalc1 = TOPS.autECLPS.GetText(13, 27, 6)
Calc1 = Application.WorksheetFunction.VLookup("scalc1", Range("p:q"), 2, False)
If Err.Number = 0 Then
MsgBox "Works"
Else
MsgBox "Didn't Work"
End If
I am trying to get the row number of the row containing the value in AO1 in column A.
Code:
with wshgroup
mrow = Application.Match(.Range("AO1"), "A:A", 0)
end with
I'm getting a type mismatch error.
The sub i'm currently using does an instr search for a unique string in an xml file. This works fine but i'd like to speed up the search if possible because I have a large number of xml files to search. My problem is that i have very little experience in parsing xml.
I notice that if i load the xml file and look at the namespaces i see item(1) through about item(20) and the unique string is always contained in one of those items. Note that the number of items can be different for each xml file i load.
The xml file is loaded into an object i define as oInstance and i'd like to do something like:
Do
counter = counter+1
if oInstance.namespaces.item(counter) = uniquestring then[code]....
but now i get Err.Number 91