Object Required Passing Range To Function

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


ADVERTISEMENT

Using Find Function In VB - Object Required

Aug 6, 2013

This was my attempt, it says 'Object required'

Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, cancel As Boolean)

If Target.Column = 16 Then
Select Case Mid(Target.Text, 1, Worksheet.Function.Find("to", Target.Text, 1) - 2)

Case "CAS"
MsgBox ("Cashiers")

View 2 Replies View Related

Object Required Error Setting Range Variable

Aug 22, 2007

code is getting an error on the bold line below. My error message is run time error 424 - object required.....

View 7 Replies View Related

Passing Range To VBA Function?

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

Passing Named Range Into User Defined Function?

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

Passing A Range To Be Read As An Array To A Custom Function

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

Excel Function Required To Allocate Project Cost Between Two Date Range

Dec 9, 2013

Attached is a spreadsheet wherein I'm trying to extrapolate project costs across various months based on working days in a month subject to start and end dates of the project. Need an accurate formula to spread the cost.

Days & Cost Allocation Example.xlsx‎

View 2 Replies View Related

Object Required

Sep 27, 2007

i am having alittle trouble with this line of coding.

With ActiveWorkbook
.Sheets("Hidden Data").Cells(rngFound.Row, 9) = Sheets("Data").Range("L18")
.Sheets("Hidden Data").Cells(rngFound.Row, 12) = Sheets("Data").Range("l20")
.Sheets("Hidden Data").Cells(rngFound.Row, 15) = Sheets("Data").Range("l22")
.Sheets("Hidden Data").Cells(rngFound.Row, 18) = Sheets("Data").Range("l24")
.Sheets("Hidden Data").Cells(rngFound.Row, 21) = Sheets("Data").Range("l26")
.Sheets("Hidden Data").Cells(rngFound.Row, 24) = Sheets("Data").Range("l28")
End With

with the line "Sheets("Hidden Data").cells(rngfound.row, 9) = sheets("data").range("l18")" the error messages states Object Required, but i am not sure what this is trying to say.

any idea's or thoughts would be greatly appreciated.

at the top of the coding i have listed "Public rngFound"

View 9 Replies View Related

Error '424' Object Required

Jul 28, 2007

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/28/2007 by i8ig
'

If Target.Column = 1 Then
If Target.Value = "Med" Then
Rows(Target.Row).Interior.ColorIndex = 4
Range("H3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)"
Else
If Target.Value = "Tasc" Then
Rows("4:4").Interior.ColorIndex = 44
Range("H4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
Else
If Target.Range = "NBAR" Then
Range("J5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)"
Range("I5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
Range("H5").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
End If
End If
End If
End If
End Sub
I continue to receive an error '424' object required and I cant find it

View 9 Replies View Related

Error Object Required

Nov 3, 2009

I have two worksheets one called "invoice" and one called "tenants"
"Invoice" has a userform where I want to enter a tenancy number and then lookup the tenants name and address from "tenants"

I have put the following macro in

Sub lookup()
Dim res As Variant
res = Application.VLookup("A1", Tenants.xlsx("Sheet1"), "$A:$H", 2, False)
If IsError(res) Then
MsgBox "not found"
Else
MsgBox "found at pos: " & res
End If
End Sub

but get the following error - "object required"

View 9 Replies View Related

Error 424 Object Required

Sep 4, 2006

I am trying to get the code to search for me specific words in the excel files in my Folder as specified, however when i try to run it, there will be a Error 424 : Object required.

Sub SearchText()

cr = vbCrLf
quot = Chr(34) 'quotes

Dim l As FileSearch
Set l = Application.FileSearch

s = InputBox("Search", " Enter the text you're looking for.")

With l
.NewSearch
.LookIn = "D:FinancialNews"
.SearchSubFolders = True
.FileName = "*.xls"
.MatchTextExactly = True ............................

View 9 Replies View Related

424: Object Required Error

Jan 20, 2007

Private Sub Worksheet_SelectionChange(ByVal target As Range)
If programmatic Then Exit Sub
selectionChange (target) ' 424 occurs on this line
End Sub

Private Sub selectionChange(target As Range)
' sub implementation here

I can't see anything wrong with my sub call that would cause a 424: Object Required.

View 3 Replies View Related

Passing Columns As Paramater To Work On List Object

Apr 21, 2009

I'm trying to write a remove duplicates sub that can be passed the worksheet name and columns on which to check. It's pasted below. Above the key line is a commented out line of code that worked. So it works to pass theh worksheet, but I'm hung up on how to pass varying columns to it.

Sub RemoveDuplicatesSub(wksht, cols)
'Remove duplicates.
'Assumes that the data range is in a table
'Assumes the header row starts at row 7

Dim WS As Worksheet
Dim TableName As String

Set WS = Worksheets(wksht)

TableName = WS.ListObjects(1).Name

'WS.Range(TableName).RemoveDuplicates columns:=Array(3, 4, 5, 6, 7, 8, 9, 10), Header:=xlYes
WS.Range(TableName).RemoveDuplicates columns:=Array(cols), Header:=xlYes

End Sub

View 9 Replies View Related

Runtime Error 424 :: Object Required ..

Jun 9, 2009

I am getting a runtime error 424: Object required on the line "For Each ws In memberLists.Worksheet". The entire code is below. How can I fix this?

View 4 Replies View Related

Runtime Error 424 (object Is Required)

Jun 20, 2009

when using the key word "target" as in Target.Address. I get a runtime 424 error saying that object is required.

View 4 Replies View Related

Getting Run Time Error 424 Object Required

Jan 8, 2010

I keep on getting the "Run Time Error 424 object required" yet the code seems to look fine .....I'm asking for it to look for the date and have stated which cell/range ... I don't get it.

View 14 Replies View Related

Runtime Error 424 - Object Required

Sep 29, 2011

I'm working in a project on my PC with Vista and Office 2003/Windows 2007/Windows 7, the project works perfectly. At work, i have the office 2007/Windows 7 and it appears at the beginning Runtime error 424 - object required.

Code:
Private Sub Workbook_Open()
'ENTRA
'protege

[Code]....

View 4 Replies View Related

Runtime Error 424 Object Required

Jan 31, 2012

I have the following code, but when I try to execute it I get a "Run-time error 424: Object Required" message. The active sheet has just one pivot table on it. Is there some problem with how I'm referencing the pivot table? If there was more than one table on the page, how would I choose a specific table?

Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt = TableRange1.Select

View 1 Replies View Related

Compile Error: Object Required ()

Dec 4, 2007

I found this code in the Mr. Excel archives. It is designed to add a sequential number to a cell if the cell next to it contains text and then stop as soon as it encounters a blank. Problem is that I keep getting a Compile Error: Object Required on the Set nos line. I tried using a qualifier and also expirimented with CreatObject but still received the error.

Sub AddNos()
Set nos = Range("B1", Range("B1").End(xlDown)).Offset(0,-1)
nos.Resize(1,1).Value = 1
nos.Resize(1,1).AutoFill nos, xlFillSeries
nos.NumberFormat = "General""."""
End Sub

View 4 Replies View Related

Runtime Error 424 Object Required

Feb 24, 2009

I'm using Excel 2003 running under Win XP. I'm completely running out of idea how to figure out this error.

here's my vba as follows:

Private Sub cmdConnect_Click()
sckClient.Connect
cmdConnect.Enabled = False
cmdSendData.Enabled = True
cmdDisconnect.Enabled = True
End Sub

Private Sub cmdDisconnect_Click()
sckClient.SendData "close"
sckClient.Close
Application.Quit
End Sub

Private Sub cmdSendData_Click()
sendExcelData
cmdSendData.Enabled = False
End Sub

I've this 2 files msinet.ocx and MSWINSCK.OCX in my system32 folder. But still nothing work.

View 9 Replies View Related

Runtime Error 424 - Object Required

Apr 12, 2006

my code (line highlighted in red) and tell me why i get the runtime error 424, "object required" on that line?

How do i correct this? ..

View 8 Replies View Related

Run-Time Error '424': Object Required

Aug 22, 2006

I have an Excel workbook that I have built in user-level security to log into. If login is successful I have code like this:


If 'successful conditions
Goto Success
'more code here that doesn't matter

Success:
'verfies that it is a new project
lgCreateProject = MsgBox("Would you like to create a new project?", vbYesNo)
'opens a userform if new project
If lgCreateProject = vbYes Then frmQuoteInfo.Show
Unload Me

End Sub

If the user signs on successfully and is not starting a new project then they get the error noted in the title of my thread.

View 9 Replies View Related

Run-time Error '424': Object Required...

Nov 7, 2006

Then the change event in the ComboBox of TB5 stopped working with the above error message.

Here's the

Private Sub TB5_Change()

'Loads TB6 thru TB16 from TB5's RowSource
i = 0
If Not TB5.ListIndex < 0 Then
For i = 1 To 12
If Not i = 1 Then Me.Controls("TB" & i + 4).Text = TB5.List(TB5.ListIndex, i - 1)
Next i
End If

End Sub

The segment that is erroriung out is:

Me.Controls("TB" & i + 4).Text = TB5.List(TB5.ListIndex, i - 1)

The first time it errored debug said there was a type mismatch. That only lasted about 15 min before it changed error message to the one in the title. Debug highlighted this same code segment for both.

TB5's original RowSource is "FoodList_2" which is 12 colums wide and variable number of rows. After picking the item from the first column this code fills in the rest of the data for the chosen row. I'm not sure how wide the rowsource is with the find code.

View 3 Replies View Related

Object Required: Union Method

Nov 9, 2006

columnA contains account numbers sorted in ascending order i need to use the union method to select all rows where the account number changes so that later i can insert rows between different account numbers in one action. i tried to use the following code but an error occurs stating 424 object required ....

View 7 Replies View Related

VBA Object Required Error After Renaming UserForms?

Apr 14, 2014

So I went back and reorganized my userforms in the order that they are typically operated. I went in reassigned all the appropriate new userform names where needed. Now when I hit the item to activate the userform I get an "Object Required" error code....? So I went back recopy/paste each activation macro for the userforms and I still get the same error code

View 7 Replies View Related

Object Required (Error 424) When Trying To Set A New (copy) Worksheet

Aug 14, 2014

I am copying a WorkSheet and I want to set it directly to an Object. I don't want to use the activeWorksheet. I does work like this with .add so my idea was it should work with copy too.

[Code] .....

It does Copy the Worksheet, but afterwards VBA gets error 424

SO how do I set a Workbook.copy ?

View 2 Replies View Related

Object Required Error When Running Macro

Apr 28, 2010

I am trying to run the following macro to copy a data range(A1:HX1) range from one sheet(sheet 6) and past it into the next available blank row in another sheet called New_Overall_Input_File but get the following error when I try and run it......."Object Required"?

Sub ALLCARS()
Sheet6.Range("A1:HX1").Copy
New_Overall_Input_File.Range("D" & Rows.Count).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

View 9 Replies View Related

Error Object Required When Copying Sheet

Dec 1, 2011

I have a script that creates a bunch of sheets. It was working fine when testing because I was just adding a sheet using worksheets.add, but now I've found out they all need to be created from a template sheet. I set up the template sheet and changed to the worksheets.copy method, but I'm receiving a runtime error "Object Required"

All I want to do is copy the sheet and rename it to the next name in the array (I have an array of sheet names).

Here is the code snippet in question (error line in red):

Code:

If lngX = LBound(arrSheets) Then strAfter = Sheet1.Name Else strAfter = CStr(arrSheets(lngX - 1))
Set WS = Worksheets("TmpSht_Checks").Copy(after:=Sheets(strAfter))
WS.Name = CStr(arrSheets(lngX))

As written, the duplicate sheet IS created...

I also tried the method below, but I get the same result (sheet created (but not named) and the same object required error)

Code:

If lngX = LBound(arrSheets) Then strAfter = Sheet1.Name Else strAfter = CStr(arrSheets(lngX - 1))
Worksheets("TmpSht_Checks").Copy(after:=Sheets(strAfter)).Name = CStr(arrSheets(lngX))

View 1 Replies View Related

Run-time Error 424: Object Required In For Each Loop

Oct 3, 2006

I am running regression statistics for several different columns of data. In order to help me analyze the data, I am organizing the output on a specified worksheet called "Best Subset." I have stored the column names of the data used in different regression calls in a range array called Reg_Labels. The Reg_Labels array may contain one, two, or sixteen column labels in one specific location (where each column label is stored in a separate cell). All possible column names are stored in the Labels array (each name is stored in a separate location). I basically need to check to see which column names were present in a particular array location in Reg_Labels. When a column name from a cell in Reg_Labels(I-1) matches a column name from Labels(K), I am placing an "X" on the designated spreadsheet to indicate which variable(s) has been used.

My For..Each Loop will not work for some odd reason, and I need to figure out why. I keep getting an error that an object is required. I need each cell in Reg_Labels(I-1) compared with Labels(K), and I thought the For..each loop would be the easiest way to do it.

I just don't understand what's going on if C is a range object and Reg_Labels(I-1) is a range object. Wouldn't this loop supposedly cycle through every cell in Reg_Labels(I-1)?

Have I messed up some sort of object reference? ....

View 6 Replies View Related

Excel 2010 :: VBA Runtime Error 424 Object Required

Dec 1, 2011

I am writing macros for a pop up calender in excel 2010. I followed instructions in the link below but at the testing step # 7 it returned; 'run time eror 424 object required'. It's my first time writing macros.

[URL]

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved