Unable To Set ColorIndex Font Property?
Feb 3, 2009why this won't work or at least a way to accomplish the same thing. Basically, I'm trying to hide unneeded data by making the font the same color as the cell:
View 2 Replies
ADVERTISEMENT
Unable To Set ColorIndex Property Of Interior Class: Fill Cell Color Macro
Sep 29, 2006I have an error message that says: Run time error '1004': Unable to set the colorIndex property of the interior class. I attached code for your reference.
If (Range("B10").Value = "Gift" Or Range("B10").Value = "Entertainment") And Range("C10").Value = "" Then
Range("C10").Interior.ColorIndex = 6
MsgBox "Please Fill in the Person's Name & Company."
Range("C10").Select
Range("C10").Interior.ColorIndex = 6
End If
Change The Font.colorindex Of The Cell In Column
Dec 9, 2009I have some code that runs through about 5000 rows of data looking at duplicates for column A. instead of deleting the duplicate rows, i change the font.colorindex of the cell in column a so that all cells containing xx are one color, and all cells containing xxx are colorindex+1.
Since i have about 5000 rows, colorindex limits itself to 122 (i msgbox'd each time and this is the last number i got before the subscript out of range).
Is there anyway around this, or is there a better way to pickout duplicates. remember, i can't delete duplicates, i need a way to manipulate the duplicates data once i pick them out.
Specialcells Property Error "Unable To Get The SpecialCells Property Of The Range Class"
Apr 14, 2007Having 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
Unable To Set Formula Property Of Series
May 14, 2006I went through a lot of threads in this forum, although there were a couple relating to the same problem am facing, they didnt solve my error .. so here's my question again ..
When am trying to delink charts , i get an error stating "Unable to set the formula property of series class".
The code is as follows :
Sub DelinkChartData()
Dim iCtr As Integer, iChars As Integer, SH As Shape, iPlotOrder As Integer
Dim nPts As Long, iPts As Long 'Holds the total no of points in the chart
Dim xArray, yArray, sChtName As String, sSrsName As String
Dim xVals, yVals
Dim ChtSeries As Series ' var used to loop thru the series collection
Dim ws As Worksheet
Dim sChartType As String, iCtr1 As Integer
Unable To Set The Orientation Property Of The PivotField
Oct 15, 2006I want the data field to change to a different field when a command button (cmd_view) is clicked. So when the command button has a caption of "Toggle to Feeds", the code changes the field from Sqm to Feeds, and changes the caprion on the command button to "Toggle to Sqm". When this is next clicked, the fields are swithed over, and the command button caption changes relative to it.
The code I have written is below:
The problem I have is when the code changes the view from feeds to Sqm, it works fine, but when the code changes from Sqm to feeds, it crashes out in the true part of the If statement on the code line ".orientation = xlhidden" with the error "Unable to set the Orientation property of the PivotField class".
As the code for the change of view is the same, apart from the field names, I am at a loss on what is the cause.
I have tried to record a macro to see if the field names appear differently, but they do not.
Sub Tog_pivot()
Dim Toggle As String
Application.ScreenUpdating = False
Toggle = Sheet5.cmd_View.Caption
If Toggle = "Toggle to Feeds" Then
'switch view from Sqm to Feeds
Range("F12").Select
' hide existing field
With ActiveSheet.PivotTables("Pivot_forecast_old").PivotFields("Sum of Sqm")
.Orientation = xlHidden
End With
' Create & display new field "Feeds"......................
Unable To Set Hidden Property Of Range Class
Mar 19, 2014I 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.
Unable To Set The Visible Property Of The Worksheet Class
Jan 6, 2009I'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 RelatedUnable To Get The FindNext Property Of The Range Class
Jun 26, 2009I 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.
Unable To Get The VLookup Property Of The WorksheetFunction Class...
Dec 4, 2009I 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
Unable To Set Weight Property Of Border Class
Apr 22, 2010I 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.
Unable To Set AxisGroup Property Of Series Class
Dec 28, 2011I 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.
Unable To Get VLookup Property Of Worksheet Function
Aug 21, 2012I was having an issue with my vlookups not working properly so I took out the error handler to try and find out why.
It came up with the error "Run-time error '1004': unable to get the vlookup property of the worksheetfunction class"
Below is my code. How to rectify this?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim rng As Range, cell As Range
[Code].....
Unable To Set Locked Property Of Range Class
May 1, 2014The 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
Unable To Get CheckBoxes Property Of Worksheet Class
Jul 3, 2014I 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]....
Unable To Get The Match Property Of The Worksheet Function
Mar 24, 2007I'm trying to find the occurence of a date in a range.
Here is the code I'm using:
Windows(todaydate1).Activate
For Each cell In range(Cells(top73, 3), Cells(bot73, 3))
z3 = DateSerial(Year(cell), Month(cell), Day(cell))
Windows("Cash Flow " & todaydate & " PM.xls").Activate
Sheets("Commitments").Activate
However the code stops right on the q3 line where the match function is located. The problem is that I'm getting a Run Time Error 1004:
"Unable to get the match property of the worksheet function class"
This would lead me to believe that the match function is not working. However, in Excel, it does work. Even when using the cell from the other Workbook, it works.
All the dates which appear to be dates are indeed formatted as dates.
The range is good. I've used the immediate window to verify the address and the values of the other variables in play.
?range(Cells(toprw, 2), Cells(botrw, 2)).Address
$B$26:$B$49
Since the correct workbook and sheet is activated, I'm really at a loss here. Below is what the sheet looks like:
Unable To Get The Correl Property Of The Worksheetfunction Class
May 22, 2007I 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
Unable To The Locked Property Of The Range Class
Sep 22, 2008Sheets("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.
Unable To Get The Sum Property Of The Worksheet Function Class
Jan 19, 2009Here 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.
Unable To Set XValues Property Of Series Class
Feb 19, 2009I'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
Unable To Get The Search Property Of The WorksheetFunction Class
Feb 23, 2009I 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: .....
Unable To Get The Find Property Of The Range Class?
Apr 27, 2003When 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
Unable To Set The Formula Property Of The Series Class
Jul 14, 2006I 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")
Unable To Get The Vlookup Property Of The WorksheetFunction Class ..
Jan 4, 2007I have a UserForm with one ComboBox and one TextBox. The ComboBox is populated from another sheet with product codes in Col A and names in Col B. When the user selects a product code from the ComboBox the respective name appears in the adjacent TextBox. Once both boxes have been filled the user can then save the data to a third sheet. Now the problem arises when the user tries to manually enter a code into the ComboBox that does not appear in its rowsource - VBA states a run-time error and my new keyboard goes flying out the window again. Attached is an example sheet
View 4 Replies View RelatedUnable To Set The Locked Property Of The Range Class
Jul 17, 2007I 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
Unable To Set The Xvalues Property Of The Series Class
Sep 19, 2007For the last day I have been getting the following error: "Run-time error '1004': Unable to set the Xvalues property of the series class."
The archetecture of the marco is sound being that I have used this methology in another macro and it works correctly every time. The XValuesand Values range are also correct and I know this because I had the macro select the data cells and it does this correctly as well. The data for simplicity at the moment has no empty cells or abnormal data. The data cells are all filled with some kind of data. For the XValue Range the data would look something like "2070087 4/27/2007" and the Values range is filled with a number between 0 and 100. What I can't figure out is why it crashes with a valid establish range. I highlighted the lines it crashes on in read, they are near the bottom.
Sub Proto1()
'
' Proto1 Macro
' Macro recorded 8/30/2007 by aaron.verellen
'
'
Dim CurrentSheet As String, ChartName As String
Dim RowCount As Integer, ColumnCount As Integer, FirstColumn As Integer, LastColumn, _
FirstRow As Integer, LastRow As Integer, Row As Integer, Column As Integer, _
ProductColumn As Integer, ArrayIndex As Integer, ParameterCount As Integer, _
ItemCount As Integer, MyIndex As Integer
Dim UserProductFlag As Boolean, NewProductFlag As Boolean
Dim Possibilities() As String
Dim ProductXRange As Range, ProductYRange As Range .................
Unable To Get Parent Item Property In Pivot Table Using VBA
May 28, 2014I'm trying to get the parent item name of a clicked cell (e.g. when I double click on field "Commessa 2" I'd like to get "Region 3" and pass it to another sheet)
Here is my code:
[Code] .....
When I run this code, I get the message "Unable to get the ParentItem property of the PivotItem class"
Attached Image : Capture.PNG
Runtime Error 1004 Unable To Set Locked Property?
Oct 27, 2011I have a sub that I have copied out of a previous workbook where it still works fine. In the previous workbook it was assigned to a button. Now I am trying just to do it as an automatic action when the workbook is closed
It will lock any cell within the specified range that is not blank then protect the worksheet, save the workbook then save a dated archive copy
When I pasted it in to a new workbook I changed the range to A1:AP49 and strFileName
Now when I run it now I get an Error 1004 - Unable to set the Locked property of the Range class.
On Debug this line is highlighted:-
c.Locked = c.Value ""
The full code is:-
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' Lock_cells Macro
' Macro compiled 10/26/2011 by G CARNCROSS
' Locks used cells, saves the workbook then saves a password protected copy with the days date in the file name then closes the workbook
'
'
ActiveSheet.Unprotect Password:="SHES"[code].....
Unable To Set Formula Array Property Of The Range Class
Jan 18, 2012I'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.
Unable To Get VLookup Property Of Worksheet Function Class
Feb 1, 2012The vlookup worked earlier but now it doesn't and everything looks OK to me. Scroll down a little bit in the code view to see the error line.
HTML Code:
Sub Email_Executed()
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
[Code]...