In VB when I select a worksheet, I can amend the (Name) of the worksheet in the properties window. There is also the .Name property which is the same as the sheet tag name.
I can use the (Name) in vb code to identify the sheet e.g.
ControlSheet.cells(1,1)
without having to define ControlSheet as a worksheet first.
How do I access the (Name) which appears at the top of the list of properties in the properties window.
I would like to know if it's possible to access a worksheet in an Excel spreadsheet using a pre-prepared string or character-set? Usage is for years consecutive, so worksheets are '1990', '1991', '1992', etc.
In which case I'd like to be able access these with a prepared string '1990', instead of using the hard-coded method for hundreds of data accesses.
work around the sum error?
Runtime Error '1004'
unable to locate the sum property of the worksheetfunction class
Public Function CalcSheet()
Dim NoSales(0 To 11) As Range
Dim DriveOffs(0 To 11) As Range
Dim Voids(0 To 11) As Range
Dim Shortages(0 To 11) As Range
Dim tNoSales As Integer
Dim tDriveOffs As Currency
Dim tVoids As Currency
Dim tShortages As Currency
Dim X As Integer
Dim NSc As Integer
Dim DOc As Integer
Dim VOc As Integer
Dim SHc As Integer
Dim aNoSales As Integer
Dim aDriveOffs As Currency
Dim aVoids As Currency
Dim aShortages As Currency
I have a worksheet with property numbers in colum A and first names in column B. Another worksheet has property numbers in A, first names in B, and last names in C (different order, all mixed up)
I want to put a formula in the first worksheet that will compare the property numbers and when it finds a match put the last name from the second worksheet into the first. Does that make any sense? I have tried vlookup but i'm obviously doing something wrong. example below....
# first last
123 x
124 y
125 z
another sheet looks similar but has info in the "last" column. the numbers between the two sheets are not in the same order so i need to search by number and put the info from the second sheet into the first.
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 RelatedI 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].....
I have some code that works just fine until I add another For statement to cycle through a list of part numbers.
The line error is:
Code:
WsStock.Cells(StartRow + Avg, j + 8) = Application.WorksheetFunction.Average(WsStock.Range("H" & StartRow & ":H" & EndRow).SpecialCells(xlCellTypeVisible))
With a message code of "Unable to get the Average property of the WorksheetFunction class".
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]....
I'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:
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.
The 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]...
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.
I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.
I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.
The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.
how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?
I am having problems with some vba codes when I protect my workbook; 'I get an run-time error 1004 Unable to set visible property of the worksheet class'
The code I am trying is:
Sub Stats1_Return_TextBox1_Click()
ActiveSheet.Unprotect "meme"
Application.Goto Worksheets("BCM Database").Range("L15")
Sheets("Stats 1").Visible = False
ActiveSheet.Protect "meme"
End Sub
How to sort this. One more question is there away of protecting you vba code? stop users being able to view or edit them for instance?
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.
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 RelatedI have a form with several combo boxes, and they function just the way I like as far as being able to pick from the list, or typing in them and having it show you the next available item in the list as you add letters. Whats happening that I would like to know how to deal with is... as soon as you type a letter that is not in my lookup range it generates an error. "Could not get the list property - Invalid property array index". I don't want people to be able to add to the list, but I would like a msgbox to pop up. Then allow them to go back to the box and try again.
View 2 Replies View RelatedI used the "record macro" and went through my data manipulation steps. But its not transferable to other worksheets because it imbedding the name of the worksheet in the some functions (sort, pivottable,...). How can I change the name of the worksheet to "activeworksheet"?
View 2 Replies View RelatedI have this macro created with macro recorder that makes a copy of my worksheet.
View 2 Replies View RelatedI am attempting to use the following line:
View 4 Replies View Related I've been a somewhat casual Excel user and now need to do some VBA with a workbook that has several worksheets in it. In particular, the workbook has sheets that have been added "out of order", that is, the leftmost sheet is not the first one added to the book.
I need to write a VBA routine that looks "backward" from the current worksheet to "previous" sheets, i.e., sheets to the left of the current worksheet, but not to the right, so I can't refer to them as Sheet1, Sheet2, etc. and they actual display names that are not of that form anyway. I am aware of the "Worksheets" collection and the fact that I can "subscript" that to refer to the sheets in a left-to-right form.
I know that I can refer to the current sheet as "ActiveSheet" and I can determine properties like its name with "ActiveSheet.Name", but is it possible to find its index in the Worksheets collection? I'm looking for a function or property such that "ActiveSheet.Index" will allow me to refer to it as "Worksheets(Index)" and particularly to allow me to refer to preceding sheets as, e.g., "Worksheets(Index-1)".
If worse comes to worst, I can always cycle through the worksheets and check the name of each worksheet like this:
Sub Button1_Click()
Dim index As Integer
Dim strASName As String
strASName = ActiveSheet.Name
index = 0
Do
index = index + 1
Loop Until Worksheets(index).Name = strASName
MsgBox "Index of current worksheet is " & index
End Sub
And while I'm at it, is it possible in Excel to rename a control like "Button1" to something more sensible and mnemonic, like "btnRecalculate" or whatever?
I'm looking for a macro to automatically rename the active worksheet to Sheet1.
View 3 Replies View RelatedI have a chart that gets updated to show the desired reporting period by clicking a button to run some code which applies filtering to the source worksheet using a value selected from a drop down validation list on the chart worksheet. When the code runs, the source worksheet displays temporarily. How do I prevent this switching back and forth between the data and chart worksheets during code execution? Undoubtedly, there is a better way to code this.
Sub Chart_FilterPPM()
Application.DisplayAlerts = False
wk = Worksheets("Charts"). Range("D63")
Worksheets("Leak Data").Activate
With Worksheets("Leak Data")
.AutoFilterMode = False
.Range("Headings_LeakData").AutoFilter
End With
With Range("Headings_LeakData").AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=wk
Selection.AutoFilter Field:=11, Criteria1:=">5000", Operator:=xlAnd
End With
Worksheets("Charts").Activate
Application.DisplayAlerts = False
End Sub
Using VBA, I am trying (without success) to copy the active worksheet of my workbook and save it in the current folder using a filename shown in cell A1. I only need to save values and formats. Any existing code (auto fit) contained withing the sheet would no longer be required. I get a VB project message relating to macros. I would anticipate saving as xlsx would deal with this but again, am at a loss.
View 4 Replies View RelatedI am having an error in the following line of my code:
Set MyRange = Sheets("BackData").Range("rsJobTypes").Range(Cells(2, 1), Cells(cnt, 1))
If there is another sheet that is active, besides for the "BackData" sheet, I get an application error. I would like to know how I can reference this range without having to activate the sheet.
I tried adding "thisworkbook" before "sheets", but it did not seem to work.
Say i have a Worksheet named "gateway" or sometimes it will be "gateway (2)" (3) and so on. Is there a macro that i can call that in some ways calls the active worksheet and renames it to just "gateway" everytime?
View 2 Replies View RelatedI have a code that I intend to use to retrieve 2 ranges from an active workbook (csv) and place those ranges in another named workbook. Both files are open during this procedure. The code I have is:
Code:
Dim rngA As Range
Dim rngB As Range
With ActiveWorkbook.ActiveSheet
[Code].....
The problem is nothing is displaying in Workbooks("data recorder template UTD Nodata").Sheets(".") I think that the code is not picking up the active workbook correctly but not sure.
I would like to be able to Click a Cell or Button to enable me to go from the 'VIN ENTRY' worksheet to the 'Date Completed' worksheet and then be able to enter the Date in the Correct cell cooresponding to the last 6 digits of the VIN. I have included the File
WorkSheet 'Date Completed
ENTER Last 6 Digits of VIN:K43070Click
Here to Enter Date Completed
WorkSheet 'Date Completed
Colum A Column B...................
Basically trying to make change the active worksheet using a macro code. I want the user to be able to click a button and it will automatically switch the worksheet to another within the workbook.
View 5 Replies View RelatedReplace specific text within a defined range without having to select the sheet. I tried the following but this is obviously not the way to go.
Sub Open_Calls_Rename_Organizations()
With Sheets("Open Calls").Range("Organizations")
Cells.Replace What:="Institute Technology Code", Replacement:="ITC", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With
End Sub