Function Works But Get Runtime Error 13?

May 20, 2014

This is a function to add the ascii values of a string to give a single value. Initially I wrote this as a Sub routine and it worked fine, but when making it into a function, I get this error. From a little research it appears that you can not use a string as an argument for a function call, yet that defeats the object of this particular function.

The intention of this function is that it works on a name in a given cell and the value (an integer) that is produced is then displayed in another cell, or if using it as a formula, the cell the formula is in.

I simple wrote the work "Hello" in cell A1 the ascii values of these added ignoring any spaces give 500 which is correct.

View 3 Replies


Runtime Error '91" Using Find Function

Apr 27, 2009

I have a spreadsheet with a large amount of data, and one thing I need to do is Find the maximum value in a column and then select it. I was originally using an If loop to find it, but I would like to make the code more efficient by using this method. My code is as follows:

Function FindCells()

Dim FindData As Long
Dim Row_Number As Integer


FindData = ActiveSheet.Cells(6, 15).Value
Range("L5:L2000").Find(What:=FindData, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Row_Number = ActiveCell.Row
ActiveSheet.Cells(2, 14).Value = Row_Number

End Function

This string of code works fine, but it doesn't find the right value. Cell (6, 15) contains the maximum value but excel treats it as a whole number. If I try to change the data type of FindData to double or to string I get a Runtime 91 error. If I change LookAt to xlWhole I get the same error.

View 9 Replies View Related

Macro Works In Debug Mode But Not In Runtime

Apr 7, 2008

I am trying to open a xls file and convert into csv. My macro works when I'm in debug mode. but If i run the macro (Not in debug) mode then After opening a file control is not going to next function. What is problem? Even I am not getting any error too

sub open file (FileName as string)

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim RowNo As Integer
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
'Set xlw = xlx.Workbooks.Open(SourceFolder + "" + FileName)

Workbooks.Open FileName:=SourceFolder + "" + FileName
' Columns("F:G").Select
' Selection.Delete Shift:=xlToLeft
Save_in_WDrive (FileName)
Set xlx = Nothing
end sub

View 9 Replies View Related

Excel 2010 :: VBA Sheets Copy Function Works But Then Jumps To Beginning Of Function

Apr 8, 2014

I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:

[Code] ........

When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.

View 14 Replies View Related

Getting Runtime Error: Syntax Error Or Access Violation

May 3, 2006

I'm getting the error for the following piece of code.

Sub itconfandscratch()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Server_Name = "sturecord"
Database_Name = "Scratch" ' Enter your database name here
SQLStr = "SELECT stuname FROM dbo.sturec" ' Enter your SQL here
Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ""..............

View 2 Replies View Related

VB Works On One PC, Not On Another (Run-time Error 9 )

Apr 15, 2009

I run PCs at work and at home. Both machines run Windows XP and Office 2007

Home PC: Windows XP Home edition Version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO

Work PC:Windows XP Professional version 5.1 (Service Pack 3)
Excel 2007 SP1 MSO

The VB was created on the home PC. It works. On the Work PC it crashes on the line with bold. Run-time error 9 - Subscript out of range. This have never happened before (or with any other VB routine). I also changed fileNew to include the full path, but same error. What has caused this? I suspect it’s the 'fileNew'.

View 4 Replies View Related

Error Handling - Works First Time But Not The Second?

Feb 26, 2014

I am trying to handle an error. It works the first time but when it runs in the same error again it does not go to the error handling.


View 6 Replies View Related

Macro No Longer Works - Getting Compile Error?

Jul 30, 2014

I created three macros to work with reporting for my company.

One sorts a field, another deletes unwanted columns, and the third double checks columns for answers--I work for a telecom polling firm.

It was working last week on my computer, and it still works on another computer at the office. However, I keep getting an error message when I try to run it.

The message is "Compile Error: Expected Function or Variable". The code is below, and bolded where it breaks down.


[Code] .......

What could be the rationale for the sudden breakdown? I was testing it on a file that I have tested with before without issue. I also tried a second file, and even restarted my computer. I am currently trying to write a fourth macro for counting responses and giving percentages, so while I don't need to have the perfectly cleaned data to do so it would be nice to have.

View 4 Replies View Related

Rounding Function Works On 1 PC But Not Another

Dec 1, 2008

I have a very simple spreadsheet that I use for preparing quotations. It was created on my PC at home and includes a simple roundup function (=ROUNDUP((F199*G199)+F199 2) ).

I now use this spreadsheet on my PC at the office and the function works perfectly well but if I try to edit it, or create a similar rounding fx, on my office PC I keep getting the error message "You've entered too few arguments for this function".

I've tried using the Help Menu in Excel but even if I copy one of the formulas from the Help Menu into the spreadsheet I still get the error message. The 2 versions of Excel are the same (2003). I tried inserting a ',' and a ';' in front of the '2' but this has also not helped.

I have attached a sample of the offending spreadsheet. The rounding function is in column "B"

View 10 Replies View Related

IF Function Only Works With 7 Different Selections

Aug 26, 2002

I’m trying to do the following IF function but as far I can tell IF only works with 7 different selections.

Cell B2 contains the numbers I through 8
Cell D2 contains a pre-calculated number (e.g. 53.012)
Cell F2 is the cell that I need an IF function or something similar – the function would be as follows

View 9 Replies View Related

Mod Function Works Wrong

Dec 28, 2006

I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.

And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.

So here is how it work out in excel:

But in VBA you get

View 9 Replies View Related

Int Function Only Works With Double

Aug 15, 2006

If you run the Int function on a product of two variables, it will return a different (wrong) result if the variables are both defined as single:

Sub roundingtest()
Dim a As single
Dim b As single
Dim cases As Integer
a = 18200
b = 0.01
cases = Int(a * b)
Debug.Print cases;
End Sub

this returns 181 instead of 182. If you define one of the variables as double, then it works fine. Is it just me?

Anyway, I found it to be quite useful, as i was trying to get a function to round to the first higher integer - as opposed to the first lower. so
182.1 -> 183
182.9 ->183
182 ->182

So weirdly enough, int(a*b)+1 does the trick! Of course if a or b is defined as a double, then it all goes to 182. So without any IFs, this works real nice!

View 4 Replies View Related

Error In Runtime First Go, No Error In Debug Or Second Go

Jul 6, 2006

The code thats generating the error is the following:

For Y = 8 To 131
TmpDate = Empty
Range("A1").Value = "=" + Path + Sheet + "B" + CStr(Y)
TmpDate = Range("A1").Value

For Langd = 1 To Len(TmpDate)
TmpChar = Mid(TmpDate, Langd, 1)
If Not TmpChar = " " Then
TmpComp = TmpComp + TmpChar
End If
Next Langd

TmpDate = TmpComp
TmpComp = Empty

If Len(Dag) = 1 Then
Dag = "0" + CStr(Dag)
End If

Macro continues before the Next-statement...

(it might not be good programming, but I think it should work).
A little explanation to the code.
I have a spreadsheet located on the intranet that has values I need in my spreadsheet. These figures are sorted by date, so I search for the date to find the right figures.
The line Range("A1").Value = "=" + Path + Sheet + "B" + CStr(Y)
works prefectly. I can see the value in my spreadsheet in the cell A1. its the next line that causes the error "Type mismatch".
The value I get from the intranet spreadsheet looks similar to this:
"1 Jul 2006 " (note all the spaces).
What I do is just run it through a loop and cut of all spaces so it will look like this: "1Jul2006" to be sure I dont miss a space or anything.

Now to the funny part.
The error only happens at runtime on the first go.
If I chose to END the macro then and there, and then run it again from the top it works perfectly.
If I set a stopsign to debug the code, it works perfectly on the first go too.
Can anyone explain to me why that happens??

I know enough about programming to handle my syntax errors and so on, but I cant see anything wrong in this one...

Please note that this isnt the the entire code.
I'm building a report on 12 diffrent spreadsheet. The main macro is almost 2000 rows of code, and it call other Subs too (because of limitations in the VBA-editor. A macro cant to be too big), so its impossible for me to post the entire macro...

View 9 Replies View Related

Today Function Works Strange

Aug 24, 2009

I have a column, where i want to calculate the difference between today and another date/or viceversa. The problem is, that in the first two cells, it calculates it well, but then, it shows #VALUE and when I press to see the "calculations steps" , it shows "29/09/2009"-40049. It turns the today function into a number. Why?

View 11 Replies View Related

INDEX Function Works In Cell But Not In VBA

Mar 25, 2013

I've been working on a payroll program for my small biz. I'm close to done BUT...

When I use the Index Match function in a cell it works great. However I need to copy it down a column to work for pay periods going forward. When I copy it down of course it puts zeros or N/A.


Gross Fed FICA Medicare State Net

Gross is user input. FICA Medicare State and Net are simply calculated on the sheet. But to get Fed Income tax I use the Index Match function and it works perfectly. The rows increment properly with each new user input.



This is the code in Column F. You can see where it uses the result in Column G to look for a value in sheets("SingleWH").

My question: Fed Income tax is dependent on what the Gross is in order to look up the tables with the Index Match function. How do I get the result of the function into column G? Can I increment up Column G and ignore the zeros that dragging the function down G creates?

View 8 Replies View Related

Excel Code Works Fine But Error When Called From Access?

Apr 11, 2012

This code works fine when I run it in Excel.


Sub RemoveCodeAndSave()
'Remove all code from ThisWorkbook code module
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _


I get the error "Run-time error 440: The specified dimension is not valid for the current chart type" why I am getting this. The macro does everything I want it to, except for throwing the error at the end.

View 3 Replies View Related

Function Works In VBA But Does Not Work Inside Worksheets

Jun 21, 2008

i have written a code in VBA to interpolate the value of Y0 corresponding to X0 using a set of (X,Y) points. (I have written this in module1) . This function works when i call it through a Sub or another Function in VBA. but it does not work when I try to use it as a function in my excel worksheets (when I type : =interpolate(A1:A10,B1:B10,30) .... 30 is an arbitrary value). in this case I get #value! error

The function is known in within the worksheets because when I start typing its name, the Auto Name Complete feature of excel, finds this function.

I am new to VAB for excel. Please give me a hint to see my mistakes of if something is missing inside my code.

Public Function Interpolate(ByRef X() As Double, ByRef Y() As Double, ByRef X0 As Double) As Double    Dim I As Integer, Slope As Double, NData As Integer        NData = UBound(X)    For I = 1 To UBound(X) - 1        If (X(I) = X0) Then            Interpolate = Y(I)            Exit Function        ElseIf (X0 < ListMax(X(I), X(I + 1)) And X0 > ListMin(X(I), X(I + 1))) Then            Slope = (Y(I) - Y(I + 1)) / (X(I) - X(I + 1))            Interpolate = Y(I + 1) + Slope * (X0 - X(I + 1))            Exit Function        End If    Next I        End FunctionPublic Function ListMax(ParamArray ListItems() As Variant)        Dim I As Integer        ListMax = ListItems(0)        For I = 0 To UBound(ListItems())            If ListItems(I) > ListMax Then ListMax = ListItems(I)        Next IEnd FunctionPublic Function ListMin(ParamArray ListItems() As Variant)        Dim I As Integer        ListMin = ListItems(0)        For I = 0 To UBound(ListItems())            If ListItems(I) < ListMin Then ListMin = ListItems(I)        Next IEnd Function 

View 10 Replies View Related

Countif Function In A Macro: SUM And COUNTA Works

Jan 27, 2009

I'm using the SUM, COUNTA and COUNTIF functions in a macro. The SUM and COUNTA works but the COUNTIF function does not return results.

Sub B_Test()
Dim myRange
Dim Results
Dim Run As Long
myRange = Workbooks(1).Worksheets("Master").Range("S6", Range("S6").End(xlDown))
Range("M3") = Application.WorksheetFunction.Sum(myRange)
myRange = Workbooks(1).Worksheets("Master").Range("D6", Range("D6").End(xlDown))
Range("D3") = Application.WorksheetFunction.CountA(myRange)
End Sub

I have tried countless ways to rewrite the COUNTIF line with no results or compiler errors returned. Originally had problems with the SUM and COUNTIF function and found that column formating was the problem. After clearing all column formats, the SUM function promptly began working but the COUNTIF keeps eluding all my efforts. The column which the COUNTIF is pointed to contains values of 0 to 500. Only values greater than 0 are to be counted.

View 2 Replies View Related

Runtime Error 424

Mar 5, 2007

if I leave the inputbox blank I get an Excel error. I.e. it says that the formula I tried to type contains an error..... Is the Type:=8 not working properly here?

Dim Addr As Range
On Error GoTo Problem
Set Addr = Application.InputBox( _
Prompt:="Enter or Select a cell in the last record", _
Title:="Select Last Record:", Default:=Selection.Address, Type:=8)
If Addr Is Nothing Then Exit Sub
Edit: I guess I should mention that I am trying to retrieve a cell reference from the user via an input box. This cell reference will help me determine the last record in the spreadsheet.

View 9 Replies View Related

Runtime Error 13 -

Sep 18, 2007

In Excel 2003 I have made a model for evaluating co-workers. The model consist of a number of different questions regarding performance. At the top of the sheet, the user have to select som data from a drop down list, and depending on the answer (upward or downward), som of the questions in the sheets is hidden or shown. For this purpose I have created this string of VB-code in the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$5" And Target.Value = "Upward" Then
Rows("20:41").EntireRow.Hidden = True
ElseIf Target.Address = "$E$5" And Target.Value = "Downward" Then
Rows("20:41").EntireRow.Hidden = False
ElseIf Target.Address = "$E$5" And Target.Value = "" Then
Rows("20:41").EntireRow.Hidden = False

End If...........

View 3 Replies View Related

Runtime Error '9' ..

Oct 30, 2008

He's the issue i'm getting with the code...

Public Sub Auto_Open()
wrkbkName = Application.ActiveWorkbook.Name

'this little snippet of code determine which branch the user is at
'based on the ip address

'if ip is like then we on welthsrvr
ipadrr = GetIPAddresses(True)

If Left(ipadrr, 9) = "192.168.1" Then

View 9 Replies View Related

Runtime Error 13

Dec 14, 2008

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("G9:BF94")) Is Nothing) Then
With Target
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
End If
End Sub

However when ever I select more than one cell which already has text in it and press delete I get a runtime error 13 and the debugger highlights this line:

.Value = UCase(.Value)

View 9 Replies View Related

Runtime Error 9

Jan 26, 2009

I get a runtime error 9 on my excel form.

This line is highlighted:

Workbooks("Proposal for XL.xlsm").ActiveSheet.Range("S13:AH13").Copy

View 9 Replies View Related

Runtime Error With 97

Apr 12, 2006

I have created a workbook for external users that allows them to track data on a monthly basis and submit to me. Each spreadsheet has a code to sort names alphabetically. Here is the

Sub Button53_Click()
Dim i
For Each i In [B10:K309]
If i.Value = 0 Then
i.Value = ""
End If
ActiveSheet. Unprotect Password:="xxx"
Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 10
ActiveWindow.SmallScroll ToRight:=-5
ActiveSheet.Protect Password:="xxx", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Everything works great for the majority of users, but those who are using Excel 97 return a runtime error on the following line: If i.Value = 0 Then. This code was created in Excel 2000 since that was the software our office used.

View 5 Replies View Related

Runtime Error 91

Apr 5, 2007

I'm getting Runtime Error 91 on this line:

destRange = Range(.Cells(destRow, 2), .Cells(destRow, 7))

Here is the overall function:

Public Sub sendRow(myRow As Integer, destinationSector As Integer)
'1. Copy Row... Cols 2-7
'2. Find First Empty in new Sector
'3. Paste Row on first new Row

Dim myBounds() As Integer
Dim destRow As Integer
Dim destRange As Range
Dim cutRange As Range
Dim y As Worksheet

myBounds = getSecBounds(destinationSector)

This subroutine is being called from the Worksheet_Change event, and should therefore have an active sheet.

View 5 Replies View Related

Runtime Error 1004 Application-defined Or Object-defined Error

Jul 24, 2007

I am trying to copy the info from one workbook to another workbook.

I keep getting the above referenced error...

Private Sub CommandButton1_Click()
Dim filepath As String
filepath = Range("A100")
MsgBox ("File Appended")
Workbooks.Open (filepath)
Range("DesNo", "LocationPath").Select
End Sub

View 3 Replies View Related

Runtime Error '1004'

Jan 12, 2007

Is there a limit on the amout of pictures you can use with the statement Reason being, I am trying to import 119 pictures into a spreadsheet. When the information is selected form a drop down list, it pulls up the 1 of the 119 pictures. I was able to get 54 pictures input. Everything was going great until the 55th. When it stared giving me an error...
Runtime Error '1004':

Unable to set the Visible property of the Pictures class

When I select Debug, it takes me to the line with Me.Pictures.Visible = False

Everything seems to work fine until I enter the 55th picture. If it is that, is there a viable work around?

View 9 Replies View Related

Runtime Error 1004,

Feb 22, 2007

Getting a error message when opening a workbook "Runtime error 1004" I dont know much about excel and the user explained it to me as best she could . Here is where the script bugs:

Sub auto_open()
fdist = ActiveWorkbook.Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False

View 9 Replies View Related

Runtime Error '6' : Overflow

Oct 19, 2007

I have read the other posts on here and swapped, Dim timeinc As Interger to Dim timeinc As Long and Dim timeinc As Double but neither worked. They both make the calculation 0 and I can not figure out why. This macro has worked before and is failing only on this new dataset which I have attached as well.

I am solving this problem on a PC and the macro was built for a Mac but I do not think that should change much as when I run it on a Mac it fails the same

View 11 Replies View Related

Functions :: Runtime Error '9'

Nov 3, 2008

I've been having some problems with my company's spreadsheet in terms of calculations using Add Ins.

Some of the functions work within the spreadsheets - others give me a runtime '9' error,

View 14 Replies View Related

Copyrights 2005-15, All rights reserved