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
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:
I 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
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:
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
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.
I am receiving a run-time error with following code. The error message is "unable to get the VLookup property of the WorksheetFunction class". I only receive the message when the lookup value is not found in the table.
I thought adding the "False" command at the end would return an "N/A" but it didn't. Is there anything I can add to avoid this error?
I am trying to generate a number based on the Month, Year and then three digits. For Example: 1107-001. I would need it to refer to another sheet for the previous used last three digits. Each month it would start over at 001. On the Reference sheet it would always be in the first column.
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.
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?
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.
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:
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.
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: 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
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
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.
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.
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
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
For 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 .................
I have popup calendar in a spread sheet that works fine until I share the film which produces the error in the title. Here is the code for the calender:
Private Sub Calendar1_Click() ActiveCell.Value = CDbl(Calendar1.Value) 'ActiveCell.NumberFormat = "mm/dd/yyyy" ActiveCell.Select End Sub
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.