Receiving Error In Code "unable To Get The Find Property Of The Range Class"

Apr 26, 2006

I am receiving the error "unable to get the find property of the range class" in my code. I have attached my code and highlighted red where my error is occuring.

On Error Goto ProductionRptEngineListExport_Error
Dim objWrkTmp As Excel.Workbook
Dim ObjWrkshtActive As Excel.Worksheet
Dim objWrkshtTmp As Excel.Worksheet
Dim RngStart As Excel.Range
Dim CurrentRowNum As Long, RowCnt As Long
Dim GroupRowNum As Long, ColCnt As Long
Dim chtChart As Excel. chart
Dim intwrktmp As Integer, intwrktmpTot As Integer, intLastRow As Long
Dim sngwrk As Single

ctlStatus = "Opening Production Report..."
DoEvents........................................

View 2 Replies


ADVERTISEMENT

Unable To Get The Find Property Of The Range Class?

Apr 27, 2003

When I try and run my macro I keep getting an error message saying Unable to get the Find Property of the Range Class and it refers me to:

TeamRef = Worksheets("Info Reference").Range("A3:A120").Find(What:=strFind1, After:=Worksheets("Info Reference"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Address

View 4 Replies View Related

Excel 2010 :: Runtime Error 1004 / Unable To Set Hidden Property Of Range Class

Jun 25, 2013

I am using this code (below) to hide certain rows or columns depending on what number is entered. Everything is working fine and I am at the point where I would like to share this excel sheet with others but I would like to lock certain cells so that others cannot alter the formulas. As soon as I lock the cells and then enter values into the unlocked cells, I get the "run-time error '1004': Unable to set the Hidden property of the Range class".

I am using excel 2010 x64.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("B3")) Is Nothing Then
If Range("B3").Value = 0 Then
Columns("G:P").EntireColumn.Hidden = True
Else
If Range("B3").Value = 1 Then
Columns("H:P").EntireColumn.Hidden = True

[code]....

View 3 Replies View Related

Specialcells Property Error "Unable To Get The SpecialCells Property Of The Range Class"

Apr 14, 2007

Having problems with trying to get my vba code to access the SpecialCells property. Receiving the following error.... Unable to get the SpecialCells property of the Range class. The section of my code is below that is causing the error. Keeps stopping on the "Selection.SpecialCells(xlsCellTypeVisible).Select" line.

Sheets(" Book Query").Range("A6:I6").Select
Sheets("Book Query").Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlsCellTypeVisible).Select
Selection.Copy
Sheets("Inventories and Variances").Select
Sheets("Inventories and Variances").Range("A7").Select

View 2 Replies View Related

Unable To Get The Find Property Of The Worksheet Function Class

Feb 16, 2009

I am using the "Find" in VBA and wanted to test if the value searched for could not be found.

So I get the
error = "Unable to get the find property of the worksheet function class"
When the text cannot be found.

Dim zz As Variant
zz = Application.WorksheetFunction.Find("xx", "Hello", 1)

I also tried
Dim xx as boolean
xx = Application.WorksheetFunction.IsError(Application.WorksheetFunction.Find("xx", "Hello", 10))

But this produced the same error.

View 9 Replies View Related

Excel 2010 :: Error 1004 - Unable To Set Visible Property Of Pivotitem Class

Jul 10, 2014

I use Excel 2010 andexample.xlsx cannot make my code working because of the so famous errore above.

I have several pivot tables (pt) in different worksheets (ws), and a list of items stored in an array I created. I've written a macro for setting ON all items in pt except those ones in the array (listOffnet within the code). Everything works properly.

Now, I would like to do the complementary action: setting OFF all items that are not in the array. Unfortunately, I get the error at line:

[Code]....

View 1 Replies View Related

Run-time Error '1004': "Unable To Get The Buttons Property Of The Worksheet Class

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

Unable To Set Hidden Property Of Range Class

Mar 19, 2014

I locked the cells on Sheet1 from B4 to B10.

I protect the Sheet1 Review > protect sheet and selected two checkboxes from protect sheet property "Select locked Cell" and "Select unlocked cells".

It is working fine and it does not allow the user to view the locked cells B17:B24 while protection is on.

I have a RadioButton1 on the same Sheet1 and i put the following code behind the button to hide the 4 up to 10 rows. When i will press the button while password protection is on; it is giving me the following error:

Unable to set the Hidden property of the Range class.

View 7 Replies View Related

Unable To Get The FindNext Property Of The Range Class

Jun 26, 2009

I keep getting that error when I'm running my macro. When I debug, it points me to the bolded line in the code below. The larger macro I'm running this function in runs this function some 101 times without error before this happens.

The values of the parameters are as follows when it gives me the error:
Find_Exact("hchen", ws1, "B:B"). The first parameter is the only one that changes in the previously mentioned running of this function.

View 4 Replies View Related

Unable To Set Locked Property Of Range Class

May 1, 2014

The problem is coming from the Else statement.

Code:
Sub LockByColor()
ActiveSheet.Unprotect "pc"
Dim cl As Range
For Each cl In Range("$BO$1:$BZ$45")
If cl.Interior.Color = 6299648 Then
cl.Locked = True
Else: cl.Locked = False
End If
Next cl
ActiveSheet.Protect "pc"
End Sub

View 5 Replies View Related

Unable To The Locked Property Of The Range Class

Sep 22, 2008

Sheets("PRODUCTIVITY").Select
Range("A20").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection
.Locked = False (***debug screen is highlighting this line***)
.FormulaHidden = False
.ClearContents
End With
Range("A20").Select
ActiveCell.FormulaR1C1 = "PASTE NEW DATA HERE"
Range("A20").Select

This worksheet was built for use of many users so this sheet is PROTECTED.
The user is instructed to copy/paste information from our internal web into the selected areas in the worksheet.
When i UNPROTECT the worksheet the VBA works fine.

The data in the worksheet spans from columns A to P, and the length (# of records) is variable.

View 9 Replies View Related

Unable To Set The Locked Property Of The Range Class

Jul 17, 2007

I am using the below code to unloock certain columns based upon a value in cell a1. The value is the previosu month end date. It was working, but now I am getting " run-time error 1004 Unable to set the locked property of the range class" Can anyone help me to fix the error.

Sub UnlockMe()
Dim cl As Range
ActiveSheet. Unprotect "password"
For Each cl In Range("$B$1:$M$1")
If cl = [a1] Then
cl.EntireColumn.Locked = False
cl.EntireColumn.Interior.ColorIndex = 6
Else
cl.EntireColumn.Locked = True
cl.EntireColumn.Interior.ColorIndex = xlNone
End If
Next cl
ActiveSheet.Protect "password"
End Sub

View 2 Replies View Related

Unable To Set Formula Array Property Of The Range Class

Jan 18, 2012

I'm creating a simple bit of code which needs to include an array formula - but the code below fails;

Selection.FormulaArray = _
"=IF($L3="","",IF(ISERROR(INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1)),0))),0,INDEX(Data!$BG$7:$BP$11,MATCH('Pay Advice'!$A$2,Data!$A$7:$A$11,0),MATCH(1,IF(Data!$BG$6:$BP$6='Pay Advice'!$L3,IF(Data!$BG$5:$BP$5='Pay Advice'!H$11,1))))))"

"Runtime error 1004: Unable to set the FormulaArray property of the range class"

What am I doing wrong? The formula is fine when typed in.

View 7 Replies View Related

Unable To Set Hidden Property Of Range Class ActiveX Combobox

Jul 30, 2013

Here is my code:

Code:
Private Sub ComboBox1_Change()
ActiveSheet.Rows("3:62").Hidden = False
Range("D1").Value = ComboBox1.Text
End Sub

I get a runtime error '1004' when I make my combo box selection. I don't have any security or password protection type stuff. The combo box is part of a more complicated spreadsheet and I don't want to switch to a data validation type drop down and I really want it to be activeX.

View 4 Replies View Related

Hiding Columns: Unable To Set The Hidden Property Of The Range Class

Oct 31, 2006

I have recently tried to implement the password protect from viewing code from this site. It worked brilliant on a basic spreadsheet, but when I tried to implement it on a worksheet with links I keep getting the error:

Run-time error '1004'
Unable to set the hidden property of the Range class

View 9 Replies View Related

Unable To Set Formula Array Property Of Range Class - Using Replace Function

Mar 3, 2014

I'm looking to loop a comparison code. I'm using dynamic referencing (using x and y) to find maximum values for specific time intervals. The code works on a cell to cell basis, meaning if I input the formula and change the cell referencing manually then the equation will give the desired results. However when I attempted to create a VBA code to speed up the process I kept getting a 1004 Unable to set FormulaArray Property of the Range Class error, I later figured out that the Formula Array function is limited to a certain number of characters so I split up my function into 3 different string formulas. I still get the same error.

[Code] ............

View 1 Replies View Related

Pivot Chart Error: Unable To Set The _Default Property Of The Pivot Item Class

Aug 15, 2006

I have created quite a nice little macro that;
drills through a lot of key figures, updates a pivot chart and copies the chart to powerpoint.

However it crashes on one particular data set every time with this error:
Run-time error '1004':
Unable to set the _Default property of the pivot item class

The code where the debugger stops is the last line below here.

lngKpi = Sheets("Helpfile"). Cells(lngRow, 2)
Sheets("Charts").Select
ActiveSheet.ChartObjects("DK").Activate
ActiveChart.PivotLayout.PivotTable.PivotFields("KPI # (overall").CurrentPage = lngKpi

I can manually change the pivot chart to the keyfigure it crashes on and thereby workaround the problem in the macro. Also it works for more than 50 other keyfigures without problems.

View 9 Replies View Related

"Unable To Set The Hidden Property Of The Range Class" When Unhiding Cells

Oct 30, 2009

I'm trying to unhide all the cells in a worksheet, but keep getting the "Unable to set the Hidden property of the Range class" error and I'm not sure what to do.

View 2 Replies View Related

VBA Error - Unble To Set The Locked Property Of The Range Class

Jan 25, 2010

I am having trouble with a macro for unprotecting a sheet keeping specific cells locked then clearing contents of other cells then reproect the sheet. I wrote one for one sheet which works and I have re used this but it keeps saying Unable to set the Locked property of the Range class.

View 3 Replies View Related

Worksheet.function Average Returning Error "Unable To Get The Average Property Of The WorksheetFunction Class"

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

Unable To Set The Visible Property Of The Worksheet Class

Jan 6, 2009

I've been using the following code successfully for years. Today I would get the error 1004:Unable to set the visible property of the worksheet class. All my searches came up with someone not realizing they had protected the workbook. As you can see, the first thing I do before trying to set the worksheet visible is to unprotect the workbook. In frustration and on a lark I tried .Sheets("items").Visible = True and it worked. These kind of intermittent errors in Excel VBA are very frustrating. Can anyone tell me what I am doing wrong?

View 6 Replies View Related

Unable To Get The VLookup Property Of The WorksheetFunction Class...

Dec 4, 2009

I am trying to look at column at of two different workBOOKS and then when a match is found take the value from column D in workbook two and copy it to the corresponding row in the current column of workbook one.

this is the code I'm using and I get that unable to get the vlookup function. I thought maybe it was because not all of them will have matches so it could return an error if it is trying to return empty, so I put int on error resume next, and it ran through but never brought over ANY data, and there ARE matches and data to be brought over.

destRng is the range in the current column(that i just inserted) on the active worksheet that we will be putting the new info into. srcRng is the range in column A on the other workbook that the numbers are being compared to. src is just an integer

View 14 Replies View Related

Unable To Set Weight Property Of Border Class

Apr 22, 2010

I am using the following few lines of code:

Code:

Range("A3:L" & ActiveCell.Offset(1, 0).Row).Select
With Selection

.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin

End With

However am receiving an error: "Unable to set the Weight Property of the Border Class". The odd thing is, I only receive the error on the following 2 lines:

Code:

.Borders(xlEdgeBottom).Weight = xlMedium
'and
.Borders(xlInsideHorizontal).Weight = xlThin

The sheet is not protected, events are disabled, and the range that gets selected changes dynamically based on the number of rows of data in the table, however in this example cells A3:L15 are selected.

View 9 Replies View Related

Unable To Set AxisGroup Property Of Series Class

Dec 28, 2011

I made a macro to plot several line graphs on two axis (up to 5) side by side. I made the macro open up the files i need, and extract the necessary data into a common workbook. I put the common element, time as the x axis and heat as one y axis and pressure as the secondary axis. The source data on the excel worksheet is as follows:

(column A = time; column b = heat1; column c = pressure1, column d= heat2; and so on.

So when i have 3 workbooks i need to compare, it should have 6 lines. 3 sources work fine, but 2 sources gives me the "Unable to set the AxisGroup Property of the series class" error message.

Code:

Range("A1:" & Chr(65 + (2 * lNum)) & CStr(Lastrow + 1)).Select
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:" & Chr(65 + (2 * lNum)) & CStr(Lastrow + 1))

[Code]...

I don't get why it works for 3 but not 2 sources.

View 2 Replies View Related

Unable To Get CheckBoxes Property Of Worksheet Class

Jul 3, 2014

I have been working on this small project and I it works to create checkboxes and check what boxes are checked. However, at the end of checking which boxes are checked there is a pop up error message saying Run-Time error '1004': Unable to get the CheckBoxes property of the Worksheet class. These are the lines of code, where is the error?

Sub IsBoxChecked()
Dim titles(200) As String
Dim wks As Worksheet

[Code]....

View 5 Replies View Related

Unable To Get The Correl Property Of The Worksheetfunction Class

May 22, 2007

I get the "Unable to get the Correl Property of the worksheetfunction class" Error when I try to run the following code

CurrentRow = 2
CurrentColumn = 2
Finalrow = 5
WS2 = Sheet2
Finalrow2 = 5
Num_Records = 4

Correlation1 = Application.WorksheetFunction.Correl(Range(Cells(CurrentRow, CurrentColumn) & ":" & Cells(Finalrow, CurrentColumn)), WS2.Range("B" & Finalrow2 - Num_Records + 1 & ":B" & Finalrow2

View 9 Replies View Related

Unable To Get The Sum Property Of The Worksheet Function Class

Jan 19, 2009

Here is the code that generates the error:

Set rng = Range("I:I")
LR = ActiveSheet.Cells(Rows.count, "I").End(xlUp).Row + 2
Range("I" & LR).Value = Application.WorksheetFunction.Sum(rng)
Range("I" & LR).NumberFormat = "#,##0"

and from what I've read on google the .SUM(rng) need to be set to a range, but isn't it already a range? I tried plugging in there Range("I:I") for rng and that didn't work either.

View 9 Replies View Related

Unable To Set XValues Property Of Series Class

Feb 19, 2009

I'm trying to add the xvalues, but I get an error that says "Unable to set XValues property of Series Class"

Set buildSeries = ws.Cells(6, 2)
For k = 4 To Col Step 2
Set buildSeries = Union(buildSeries, ws.Cells(6, k))
Next k
ActiveChart.SeriesCollection(1).XValues = buildSeries

View 9 Replies View Related

Unable To Get The Search Property Of The WorksheetFunction Class

Feb 23, 2009

I am having trouble with IsError function consistently catching errors in my call to the Search worksheetfunction. For example, here is a snippet of my code that works just fine:

Code: .....

View 9 Replies View Related

Unable To Set The Formula Property Of The Series Class

Jul 14, 2006

I am struggling with one problem. I am trying to change formulae of series in a chart using VBA code given below. But I am getting

'Runtime Error 1004, Unable to set the formula property of the series class'

ActiveChart.SeriesCollection(1).Formula = _
Replace(ActiveChart.SeriesCollection(1).Formula, "Sheet1", "test")

View 4 Replies View Related







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