Unable To Set Range
May 9, 2014
I have a macro that looks for the last empty line( starting from the bottom of a range) the selects this line and clears it. The issue I am having is that it selects more the the range that it is coded for and for the life of me I don't understand why. This is the code,
Sub DelLastRowCols()
Dim sh As Worksheet
Dim yourPassword As String
yourPassword = "pswrd"
Application.ScreenUpdating = False
Sheets("Hrinput").Select
Sheets("Data").Visible = True
Sheets("data").Select
[Code]...
The Sheet "Hrinput" is the data input page, the sheet "data" is a seperate worksheet where all the data is kept. Currently this code will find the first used row from the bottom (row 478) but it will clear columns A- V of that row, not the A:F as defined. This range (A-F) is one of 5 set ranges, for 5 different data areas, on the same "Data" page. So when this macro runs it clears the data for the other 4 areas.
View 2 Replies
ADVERTISEMENT
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
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
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
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
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
Jul 14, 2006
I have tried using the below mentioned macro. However, it only work on one cell. I need to copy and paste a range from A1:Q200.
Sub retrievedata()
Dim wbResult As Workbook, wbSource As Workbook, CopyRng As Range, Dest As Range
Dim FileName As String, Filt As String
Set wbResult = ThisWorkbook
Set Dest = wbResult. Sheets("Data").Range("A1:Q200")
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Filt = "All Files (*.*),*.*"
FileName = Application. GetOpenFilename(Filt)
Set wbSource = Workbooks.Open(FileName)
Set CopyRng = wbSource.Sheets("Summary").Range("A1:Q200")
Dest = CopyRng
Application.ScreenUpdating = True
Application.DisplayAlerts = True
wbResult.Activate
wbSource.Close
End Sub
View 3 Replies
View Related
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
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
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
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
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
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
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
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
View Related
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
Jul 10, 2007
I have a worksheet that is unprotected but I am unable to apply filters to it. The option is greyed out if I try and select autofilters.
Another strange thing is that on one of the worksheets when I select Data, Filters there is a tick to the left of Autofilter but it is also greyed out, so in effect I cannot turn it off even tho I can't see the filters!
I have tried to popst a screen shot but unsure how to do it. I think maybe a screen shot would help to explain my issue better so if anyone knows how to do this......
View 9 Replies
View Related
Dec 23, 2013
I tried running the below VBA codes and received a Run-time error '424': Object required.
Sub 1
Dim i As Long
i = 1
'Do a vlookup between column a in test1 worksheet against column a:d range in test1source worksheet
[Code] .....
View 6 Replies
View Related
Jan 8, 2007
I am unable to insert columns on any worksheets. Even a blank worksheet. The insert column function remains disabled. Work sheets are not protected. What can I do to activate this function?
View 10 Replies
View Related
Oct 21, 2009
I have a workbook with several sheets that I created some time ago. I have hyperlinks on the main sheet which point to other sheets in the same workbook which can be viewed when using the hyperlink, however the linked sheets are hidden. Somehow in this workbook I removed both the horizontal scroll bar and the lsiting of sheet names. I can not figure out how to get them back so I can unhide and or add new sheets.
View 4 Replies
View Related
Mar 20, 2014
I'm trying to send a basic email using example [URL]..... but just cannot get it to work.
Here's my code and the error I get is "Run-time error '-2147220978 (8004020e)': The server rejected the sender address. The server response was: 530 5.7.1 Client was not authenticated."
I found my smtp server by looking in Outlook properties so I know that's right and for all instances of "person@email.com" I am putting my email address and the password is the normal password I use to log on.
View 1 Replies
View Related
Feb 1, 2008
I have inherited a workbook and wish to link certain cells from various worksheets.On one work sheet I am unable to selct any cell (cursor does not change) to link in and on another sheet there are a just a range of cells I cannot access - everywhere else works normally. There are no macros and the workbook is not protected.
View 13 Replies
View Related
Nov 8, 2011
I am currently having a problem with an IF statement. Currently this is my formula,
Code:
=IF(CZ2="TEST1",'DATA'!B241,IF(CZ2="TEST2",'DATA'!B243,IF(CZ2="TEST1(NEW)",'DATA'!B241,IF(CZ2="TEST2(NEW)",'DATA'!B243))))-DATASUBTRACT!CY19
The IF formula works perfectly fine, but when I attempt to include a subtracting to the back of the formula, I will receive a #VALUE! error.
When I create a similar IF formula in a new workbook, the formula allow me to subtract. I do not know why the code above is unable to subtract.
View 6 Replies
View Related
Apr 12, 2012
I am building a very simple time management sheet and ran into a format problem.
I have two rows with cells formatted for Time 1:30 PM
Whenever I add a time(any time) into any cell, it always changes to 12:00:00 AM
The formula bar show the format as 1/8/1900 12:00:00 AM.
View 8 Replies
View Related
Jul 12, 2012
For the following code:
Code:
Sub ShowAdmin()
Sheets("Admin").Visible = True
End Sub
I'm getting the following error message:
Run-time error '1004' Unable to set the Visible property of the Worksheet class
View 4 Replies
View Related
Dec 17, 2012
I'm using the code below to track changes in a Excel worksheet.
Code:
Option Explicit
Public preValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim Rng As Range
Dim Rng2 As Range
Dim Rng3 As Range
Application.EnableCancelKey = xlDisabled
[Code] ........
The code works fine, except for this piece of the script:
Code:
If Target.Column = 50 Then
If Target.Value = "Yes" Then
Set Rng = Application.Union(Cells(ActiveCell.Row, "B").Resize(, 22), Cells(ActiveCell.Row, "W"))
Rng.Interior.ColorIndex = xlNone
Set Rng2 = Application.Union(Cells(ActiveCell.Row, "X").Resize(, 12), Cells(ActiveCell.Row, "AI"))
[Code] .........
If I add the word "Yes" to column "AX (column 50) and press the Return key, the above code i.e. the change of fill colour, doesn't work.
If however I enter the word "Yes" and select another cell by clicking in it, the code works.
View 7 Replies
View Related
Nov 13, 2013
Code:
For Each src In Worksheets
If src.Name Like "EF Spend*" Then
Sheets(src.Name).Select
Else
Next src
End If
This is an extract of a larger code, but this section is producing a Compile Error: Next without For but I cannot workout how to resolve it.
View 9 Replies
View Related
Feb 28, 2014
I've Created Connectionstring for excel which is in database format and trying to get the data by comparing 2 sheets using he below code.. But not working..
Set ws = Sheet1
sSQL = ""
sSQL = "SELECT [Company Code], "
[Code].....
View 1 Replies
View Related
May 11, 2014
I am new to VBA. I have been getting compile error on the below code:
Public Sub hourCalculation()
Dim rcount As Integer
Dim rindex As Integer
Dim logintime As Date
Dim logoutime As Date
[Code] ..........
View 9 Replies
View Related
Jun 16, 2014
why this doesn't work and I get 'Unable to set the FormulaArray property of the range class'?
Code:
.Range("D10").FormulaArray = "=INDEX(A13:A42,MATCH(MIN(IF(F13:F420,F13:F42)),F13:F42,0))"
View 6 Replies
View Related
