Passing Worksheet Name To Function (Run-time Error '42')
May 13, 2009
I have a function which i am using as part of a macro. The macro itself works fine and locates a search string i type into an input box across ALL worksheets in an excel doc. However, i have a function which takes the results and prints it to the front page, but when i try to use the worksheet name which has been passed to the function, i get the error: Run-time error '42': object require. this happens when i get to a line of code which says
View 2 Replies
ADVERTISEMENT
Nov 28, 2006
I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:
Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)
Sub Reservoir()
Dim Inflow As Single
Dim Withdrawal As Single
Dim Evap As Single
Dim Area As Single
Dim Spill As Single
Dim Stor_1 As Single
Dim Stor_2 As Single
Dim count As Integer
Dim max As Integer
max = ActiveSheet.Cells(9, 4)
For count = 1 To max * 12
Stor_1 = ActiveSheet.Cells(count + 40, 11)
Inflow = ActiveSheet.Cells(count + 41, 4)
Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
View 2 Replies
View Related
Jul 1, 2012
I have a problem here:
Eg.
A B C D E F G H I
1 8 pm 9 pm 10 pm 11 pm 12 am 1 am 2 am
2 8 pm 11 pm
3 8 pm 2 am
I typed a formula : =if(and(c$1>=$a2,d$1
View 4 Replies
View Related
Jun 5, 2007
I have a form & in a command button that form I have code that is supposed to copy a worksheet named template into some position among the other sheets in the workbook. I am trying to set the new worksheet to some variable. The code actually creates the new worksheet where it's supposed to but I still get a run-time error 424. If I don't try to set the new worksheet to any variable the code exits fine.
Works but with Error - next to Last line
Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Sheet_before_name = ""
For Each wSheet In Worksheets
If UCase(wSheet.Name) > UCase(sheet_add_txt) Or _
wSheet.Name = "Template" Then
If Sheet_before_name = "" Then Sheet_before_name = wSheet.Name
End If
Next
Dim new_sheet As Worksheet
Set new_sheet = Worksheets("template").Copy(before:=Worksheets(Sheet_before_name))
End Sub
Works Fine
Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Sheet_before_name = ""
For Each wSheet In Worksheets
If UCase(wSheet.Name) > UCase(sheet_add_txt) Or _
wSheet.Name = "Template" Then
If Sheet_before_name = "" Then Sheet_before_name = wSheet.Name
End If
Next
Worksheets("template").Copy before:=Worksheets(Sheet_before_name)
End Sub
I have read through post after post on this forum about this error but nothing seems to apply. I can use Dave's suggestion in another post of just using the template sheet as an actual worksheet template, but Then I would have to modify other code that deals with formatting. I would have to get it to apply any formatting changes to this workbook & to the worksheet template.
View 6 Replies
View Related
Jan 30, 2007
I am having problems getting the code right for the WorksheetFunction. I have put a snip of the procedure below.
I have data that is sequentially entered into columns, always in rows 4 to 18 with the column ref increasing by 1 each time new data is entered.
After each instance of data entry into a new column I need to loop through that data and, if the a cell value matches a value in range A1:A200, to place x in column B in the same row as the match in column A. All in Sheet2.
Set cStartcell = Sheet2.Range("IV4").End(xlToLeft) ' goes to last entered column
For Each cell In Sheet2.Range("B2:B200")
Application.WorksheetFunction.If(Match(Offset.(0, -1), Sheet2.Range.("cStartCell:cStartCell.Offset(15, 0)"),""x"")
Next
I have put the problem in bold. whatever I try seems to come up as 'end of statement expected' or 'identifier or bracketed expression expected' errors. When I have managed to get rid of errors the fromula does nothing.
View 3 Replies
View Related
Aug 30, 2007
The user is asked for one piece of information "Enter the ID Number.
What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.
Set Wks2 = Worksheets(strWks)
7 rows from the bottom.
My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.
Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.
I get a Time Run Error 9.
It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.
Sub Macro01C_Auto_Resign()
Dim Wks1 As Worksheet, Wks2 As Worksheet
Dim strFind As String, rngFound As Range
Dim lngRow As Long, rngUnion As Range, strWks As String ............................
View 9 Replies
View Related
Jan 28, 2009
I am using the following code to enable users of a form to made make selections by adding a tick in one of the boxes by clicking on it. It works fine with thesheet unpotected but when I protect the sheet with cells E33:k33 unlocked i get Run Time Error '1004'
Unable to set the Name property of the font class.When I debug, Target.Font.Name = "Marlett" is highlighted.
View 4 Replies
View Related
Feb 21, 2010
I’ve created a small user form to convert food measurements.
My problem is the code is bugging out at the Function I made to do the calculations
I receive :
“Runtime Error 1004”
Unable to get the Match Property of the Worksheet Function Class”
This is the function code.
You may want to explore the file to see if I ‘ve it set up incorrectly.
View 8 Replies
View Related
Nov 26, 2008
I have a pretty simple macro that I recorded and attached to a button. The macro is:
Sub Paste_Data()
Cells.Select
Selection.ClearContents
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Range("A3").Select
End Sub
The user opens the file that contains this macro, then runs a report from a website that dumps into an excel file. They copy the data from the Book1 output, then click the button to paste it into the template. If done this way, it works fine.
However, if they run the report and get Book1 THEN open the file containing the macro, they get a run-time error 'PasteSpecial method of Worksheet class failed' on the 'ActiveSheet.PasteSpecial... line
View 9 Replies
View Related
Nov 3, 2006
I am using a script that I found on this site (see below). The problem I am having is that I recieve an error on one of my sheets. The used range streatches from Column A to Column J and from row 1 to past 32767. I have found that if I delet the information below row 32767 that I do not get an error. Is there anything I can do out side of splitting the information up on different sheets?
Dim FirstRow As Integer, FirstCol As Integer, _
LastRow As Integer, LastCol As Integer
FirstRow = Cells. Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
FirstCol = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).Column
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol))
theRng.Select
Selection.Copy
View 2 Replies
View Related
Dec 28, 2009
i am getting a run time error 1004 Delete method of worksheet class failed,
ws.Delete
Sub Save()
Dim myName As String, myFolder As String, e
Dim fso As Object, temp As String
ThisWorkbook.Save
Set fso = CreateObject("Scripting.FileSystemObject")
myFolder = "C:UsersRecsDocumentsTestVBA & ExcelEmail_Files_Temp" & Year(Date) & "" & Format$(Date, "mmm")
For Each e In Split(myFolder, "")
temp = temp & IIf(temp = "", "", "") & e
If fso.FolderExists(temp) = False Then fso.CreateFolder (temp)
View 9 Replies
View Related
Jul 18, 2007
I've noticed error 1004 is very broad and switches its name depending on what you're working with. But I have no idea what it means.
This is the line w/ probs:
Sheeti.Cells(y, 2).Value = WorksheetFunction.DGet(InventoryRange, 3, CriteriaRange)
I looked at protection and Macro security and checked my definitions (that work in other contexts).
Does DGET need to have criteria and database in the same worksheet? My project is working between two workbooks.
View 6 Replies
View Related
Jan 30, 2008
I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4.
But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.
If ComboBox2.Text = "Pounds (Ł)" Then
Sheets("Claims").Range("G16") = TextBox2.Text
Else
Sheets("Claims").Range("F16") = TextBox2.Text
End If
Sheets("Claims").Range("B16") = ComboBox1.Text
Sheets("Claims").Range("C16") = TextBox1.Text
Sheets("Claims").Range("B16") = TextBox3.Text
Sheets("Claims").Range("D16") = TextBox4.Text
Sheets("Claims").Range("E16") = TextBox5.Text
Dim a As Long
Dim b As Long
Dim answer As Long
a = Sheets("Claims").[E16]
b = Sheets("Claims").[F16]
answer = (a * b)
Sheets("Claims").[G16] = answer
View 3 Replies
View Related
Feb 7, 2014
It seems to work. Are there any problems with it? Is there a better way?
Code:
Function myRangePassExample(RangeA As Range) As Double
Dim ArrayA As Variant, ArrayB As Variant
Dim iLo As Integer, iHi As Integer, i As Integer
Dim jLo As Integer, jHi As Integer, j As Integer
Dim Sum As Single
ArrayA = RangeA.Value
iLo = LBound(ArrayA, 1)
iHi = UBound(ArrayA, 1)
[code].....
View 9 Replies
View Related
May 6, 2014
I want to select a cells value from a Data Validation Box ( Achieved ) then based off of that selection ( value ) pass it into a slicercache, as follows :
Select a value from a drop down list in cell A1 Based on selection update slicercache by passing the value of A1 to it
So far i have : The bold is the part where i need to replace with whatever is selected from cell A1. Must be possible ...
With ActiveWorkbook.SlicerCaches("Slicer_Contract_15")
.SlicerItems("BRUTE").Selected = True
End With
Would be best if this code ran on cell A1 click ...
View 1 Replies
View Related
Feb 24, 2010
I am getting the following error when i am clicking a Button on sheet1 which is generating duplicate of sheet1.I am trying to delete the button when duplicate is created..Error is:" run-time error '1004' Unable to get the buttons property of the worksheet class" at this line in code"ActiveSheet.Buttons("CmdHistory").Delete"
View 2 Replies
View Related
Nov 25, 2008
This might be a dumb question but is it possible to pass a boolean variable as a parameter to another function?
I have the following code that produces some compile error ("expected ="):
View 2 Replies
View Related
Jul 15, 2014
I'm trying to pass a multidimensional array to a function that I have defined, but I receive an error about an object mismatch. Here is what I have in regards to the array and function. What should I change?
Code:
Dim diffArray() As Integer
Dim countArray() As Integer
Redim countArray(count,2)
diffArray = getRunningSum(countArray)
End Sub
Public Function getRunningSum(ByRef countArray() As Integer) As Integer()
'Code here......
End Function
View 9 Replies
View Related
Jul 9, 2009
Passing an entire dimension of an array to a function....
View 13 Replies
View Related
Sep 28, 2007
I will post my code and point out where the error occurs.
Sub GetProd()
'Averages Daily Production over the month
Dim WellRange, MonthRange As Range
Dim Month_ As Integer
TotWells = Sheets("R").Cells(1, 2)
TotMonths = Sheets("R").Cells(1, 4)
Month_ = Sheets("R").Cells(2, 4)
For Flag = 1 To TotWells
Set WellRange = DefineWellRange(Flag)
WellRange.Select <-----Used this To check If Range gets passed back properly.
For c = 1 To TotMonths
Set MonthRange = DefineMonthRange(Month_, (WellRange)) <---Error Here
Next c
Next Flag
End Sub ...
So the error occurs in the main "GetProd" Sub at the point when it calls the DefineMonthRange Function, its a 424 "Object Required" Error. Just before this is called I have a "WellRange.Select" which I was using to make sure that WellRange is in fact a range, and it does select the appropriate area.
So My question is of course, why I get an object error even though I am passing a range to a function which is expecting a range?
View 4 Replies
View Related
Feb 13, 2014
see the attached. The array formulas in B2:C3 are operating on the table in E2:F5. These are quite simple formulas as you'll see.
The formula in B3 is identical to that in B2 apart from the fact that the first array is wrapped in an IFERROR (there are actually no errors in the array, but that's not the point).
As you can see, the formula in B2 evaluates E3 as ="", and so the corresponding entry in F3 is ignored in the sum. Quite normal.
However, the identical formula, but with an IFERROR first acting on Array1 now evaluates the same cell as <>"", and so F3 is this time included in the sum.
What action has the IFERROR had on this array? Clearly, E3 is not an error, so a "blank" should still be passed, unchanged, to the function.
I have included identical versions of the formulas, but with Named Ranges replacing the actual range references. The reason is that, with these versions, if you step through the formulas with Evaluate Formula, for example with the formula in C2, you can see that Excel "temporarily interprets" the blank in E3 as a zero (this is nothing new, though I've never fully understood why it can't display a "" here as well) before then equating this "zero" as being a "blank".
Perhaps somehow, after being passed to the IFERROR, this "zero" is processed so that, when passed on, even though it still “displays” as a zero, for some reason it is no longer considered equivalent to "".
View 8 Replies
View Related
Dec 4, 2012
Passing Named Range into User Defined Function
MrExcel.com | Excel Resources | Excel Seminars | Excel Products mcm91201
Depending on time of day and computer I am sitting in front of I am using:
WinXP Pro SP2 with Excel 2003
Win7 Pro SP2 Excel 2007
Win7 Pro SP2 Excel 2010 on PC
Win7 Pro SP2 Excel 2010 on Mac Mini running Boot Camp
OSX Excel for Mac 2011
I have only tried this on Win7 Pro SP2 Excel 2007 but need it to work on all.
I enter the values 0, 1, 2 ... 89, 90 in cells A1 to A91
I select A1:A91 and name the range 'angle'
I create a user defined function:
Public function sindeg(value As Double) as Double
sindeg = sin(worksheetfunction.radians(value))
end
I want 'value' for the function in a cell to be replaced by the corresponding value in the same row (or column) in the named range 'angle'. For example (using commas as column separators). This works for Excel functions like sin, cos, radians, etc.
********** Worksheet Contents **********
A1 = 00, B1 = sin(radians(0)), C1 = sin(radians(A1)), D1 = sin(radians(angle)), E1 = sindeg(0), F1 = sindeg(A1), G1 = sindeg(angle)
A2 = 01, B2 = sin(radians(1)), C2 = sin(radians(A2)), D2 = sin(radians(angle)), E2 = sindeg(1), F2 = sindeg(A2), G2 = sindeg(angle)
A3 = 02, B3 = sin(radians(2)), C3 = sin(radians(A3)), D3 = sin(radians(angle)), E3 = sindeg(2), F3 = sindeg(A3), G3 = sindeg(angle)
......
A91 = 90, B91 = sin(radians(90)), C91 = sin(radians(A91)), D91 = sin(radians(angle)), E91 = sindeg(90), F91 = sindeg(A91), G91 = sindeg(angle)
Column A = input. Columns B, C, D, E and F all calculate the same value by row. Column G fails with a #VALUE. In row 1 the value of angle[1] = 0 therefore column D = C = B = sin(0) = 0. In row 2 angle[2] = 1 therefore B = C = D = 0.017452
How can I get the user defined function sindeg(value) in column G to accept the named range variable 'angle' like the Excel function radians(value) accepted it in column D?
This functionality should work horizontally as well as vertically. For example enter 'angle' A1 to CM1 then have sindeg(angle) filled from A2 to CM2. It should also work in the case where the named range 'angle' is a single cell.
I am sure that this is a simple variable type definition problem in my user defined function: should the input variable be defined as type Range? Or something more exotic?
The brute force approach is to have the function determine the input value by passing in the named range, working out dimensions, calculating offset between the cell the function is in and top (left) of named range, then counting down (right) to pick the correct value. However I cannot see adding all that code to EVERY function. Occam's Razor says there has to be an easier way since Excel built in functions seem to do it readily.
View 8 Replies
View Related
Jul 25, 2006
code to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion
View 4 Replies
View Related
Aug 11, 2006
I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly.
Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.insert Shift:=xlDown
End With
Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
End Sub
View 8 Replies
View Related
Apr 5, 2007
passing variables within procedures on a worksheet. i have:
Private Sub lblGoToMaterials_Click()
'email to:
'----------------
'grab current position
myRow = ActiveCell.Row
mycol = ActiveCell.Column
and i would like to pass the values gotten in myRow and mycol to:
Private Sub cmdGoBackTo_Click()
Application.Goto Reference:="R" & myRow & "C" & mycol
View 3 Replies
View Related
Mar 2, 2009
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
I250 contains: 10, shows: 10
I251 contains: ="I"&I250, shows I10
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
View 9 Replies
View Related
Feb 7, 2007
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
View 6 Replies
View Related
Oct 8, 2008
I have written a Excel (2003) that searches a worksheet for a string in any cell. If the string is not found, it uses the 'On Error GoTo' command to jump to a given label. It works fine on the first string not found. When it searches for the next non-existent string, it fails with:
'Run-time error '91':
Object variable or With block variable not set'
Do I have to clear a buffer after each cells.find search?
My
View 7 Replies
View Related
Jan 23, 2007
TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)
If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)
The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.
View 3 Replies
View Related
Apr 27, 2007
my lookup isnt returning the correct data- already tried sorting it doesnt work im pretty sure ive put in the correct formula
View 6 Replies
View Related
