Run-Time Error Setting Worksheet Variable
Aug 30, 2007
The user is asked for one piece of information "Enter the ID Number.
What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.
Set Wks2 = Worksheets(strWks)
7 rows from the bottom.
My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.
Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.
I get a Time Run Error 9.
It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.
Sub Macro01C_Auto_Resign()
Dim Wks1 As Worksheet, Wks2 As Worksheet
Dim strFind As String, rngFound As Range
Dim lngRow As Long, rngUnion As Range, strWks As String ............................
View 9 Replies
ADVERTISEMENT
Sep 13, 2006
Subcript Out Of Range Error Coming Now For The Code Which Works For Me Before
Sub WHideRows()
Dim rRange As Range, rCell As Range
Dim strVal As String
Set rRange = Worksheets("WIED PROBLEM WELLS").Range("A11:A110")
For Each rCell In rRange
strVal = rCell(1, 3) & rCell(1, 4) & rCell(1, 5) & rCell(1, 6) & rCell(1, 7) & rCell(1, 9)
rCell.EntireRow.Hidden = strVal = vbNullString
Next rCell
End Sub
i am using the code above to hide the rows which doesn't have any values in all the following Cells 3,4,5,6,7 & 9 or Unhide the rows if there is value in any 1 of the following cells 3,4,5,6,7 & 9 from row number A11 to A110.
The same code works for me before. But now the code is not working. It says below the error message
Run-time error '9':..................
View 9 Replies
View Related
Aug 8, 2006
i have compiled a multipage using some borrowed code and some code i have written myself. most of it works, but i have a problem populating listbox2.the error is in Private subCmbFindAllJobNo_Click(). i have put h1 tags around the line of code which shows the error when i debug. this code works ok as a stand alone, so i suspect i have done something wrong in the userform initialise.
Option Explicit
Dim rng As Range
Const FirstRow As Long = 2
Dim r As Long
Dim ans As Variant
Dim MyArray(100, 4)
Public MyData As Range, c, d As Range
Private Sub cmbAmend_Click()
Application. ScreenUpdating = False
Set c = Worksheets("ENTRY SHEET").Range("b4").End(xlUp).Offset(3, 0)
c.Value = Me.DTPicker1.Value
c.Offset(1, 0).Value = Me.TextBox1.Value
c.Offset(2, 0).Value = Me.TextBox2.Value ..........
View 9 Replies
View Related
Mar 22, 2007
We have a very long macro that at the end sends an email to each training coordinator. Within the body of the email, we want to autopopulate the completion status from a pivot table using getpivotdata.
We first try to set the variable but get an error: [compile error:invalid qualifier].
Sheets(Summary).Select
Dim BDCompletion As String
BDCompletion = Application.PivotTableSelection.GetPivotData(A3, "Business Dev Plan Found")
And this is how we plan to incorporate the variable into the body of the email:
With OutMail
.To = "name@company.com"
.CC = ""
.BCC = ""
.Subject = CurrentSheet.Name & " Training Plan Status as of " & Format(Now, "dd-mmm-yy")
.Body = "BD is " & BDCompletion & " complete for 2007 Training Plans as of the date of this email."
.Attachments.Add Destwb.FullName
'You can add other files also like this
'.Attachments.Add ("C: est.txt")
.Send 'or use .Display
End With
View 9 Replies
View Related
Aug 22, 2007
code is getting an error on the bold line below. My error message is run time error 424 - object required.....
View 7 Replies
View Related
May 6, 2009
Is there a setting within excel that could cause a user to get a run-time error 1004? I have a workbook with a macro that adds additional sheets to the workbook when a button is clicked. The thing works fine on my machine and 3 or 4 other machines that I have had guys test it out, but I have a user that it will not work for. He is at a location about 3 hours away so I cant see exactly what he is doing, but he says after he clicks the button he gets a run-time error 1004 that says "Unable to set the PrintQuality property of the Page Setup class". I'm thinking that it is a setting within excel?
View 5 Replies
View Related
Feb 20, 2009
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
View 9 Replies
View Related
Oct 7, 2009
I am having a lot of trouble finding out why I am getting error. I believe the error is because it can't find the number. In cells C115:C314 i have the numbers 1 to 200 in order. when someone types in 1 to 9 in the text box it works, but on 10 and over i get the error ???? here is the code I have
shCalculator.Range("C115:C314").Select
Selection.Find(what:=CInt(txtPackageID), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
x = ActiveCell.Row
shCalculator.Range("ProposedMeter").Value = Cells(x, 7).Value
shCalculator.Range("Package").Value = Cells(x, 12).Value
shCalculator.Range("ProposedMeterAmount").Value = Cells(x, 30).Value
shCalculator.Range("Term").Value = Cells(x, 62).Value
shCalculator.Range("Discount").Value = Cells(x, 67).Value
shCalculator.Range("Equipment").Value = Cells(x, 72).Value
View 3 Replies
View Related
Jan 14, 2007
Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range
Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _ ........................
Using the message box I see that sheet 1 opens but then I receive an error message Run Time Error 91, Object Variable or With Block Variable not set. I tried declaring and using set on "project" but got nowhere. I also need to have a message indicating project not found. Once this part is solved I will loop all of my other workbooks
View 5 Replies
View Related
Nov 28, 2006
I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:
Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)
Sub Reservoir()
Dim Inflow As Single
Dim Withdrawal As Single
Dim Evap As Single
Dim Area As Single
Dim Spill As Single
Dim Stor_1 As Single
Dim Stor_2 As Single
Dim count As Integer
Dim max As Integer
max = ActiveSheet.Cells(9, 4)
For count = 1 To max * 12
Stor_1 = ActiveSheet.Cells(count + 40, 11)
Inflow = ActiveSheet.Cells(count + 41, 4)
Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
View 2 Replies
View Related
Nov 28, 2006
I am trying to run a script from a command button on another sheet in the same workbook. all the variables have been defined. I get a "select method of range class failed" message when I run the code. I believe that i am not getting "closed trades" to be the active worksheet.
excerpt of code
'Clear out existing data
iRow = 2
Set wks = ThisWorkbook.Worksheets("Closed Trades")
wks.Activate
lrow = Cells(65536, 1).End(xlUp).Row
Set Rng = wks.Range(Cells(iRow, 1), Cells(lrow, 17)) ' this row errors out
Rng.Clear
Set Rng = Nothing
View 5 Replies
View Related
Jun 5, 2007
I have a form & in a command button that form I have code that is supposed to copy a worksheet named template into some position among the other sheets in the workbook. I am trying to set the new worksheet to some variable. The code actually creates the new worksheet where it's supposed to but I still get a run-time error 424. If I don't try to set the new worksheet to any variable the code exits fine.
Works but with Error - next to Last line
Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Sheet_before_name = ""
For Each wSheet In Worksheets
If UCase(wSheet.Name) > UCase(sheet_add_txt) Or _
wSheet.Name = "Template" Then
If Sheet_before_name = "" Then Sheet_before_name = wSheet.Name
End If
Next
Dim new_sheet As Worksheet
Set new_sheet = Worksheets("template").Copy(before:=Worksheets(Sheet_before_name))
End Sub
Works Fine
Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
Sheet_before_name = ""
For Each wSheet In Worksheets
If UCase(wSheet.Name) > UCase(sheet_add_txt) Or _
wSheet.Name = "Template" Then
If Sheet_before_name = "" Then Sheet_before_name = wSheet.Name
End If
Next
Worksheets("template").Copy before:=Worksheets(Sheet_before_name)
End Sub
I have read through post after post on this forum about this error but nothing seems to apply. I can use Dave's suggestion in another post of just using the template sheet as an actual worksheet template, but Then I would have to modify other code that deals with formatting. I would have to get it to apply any formatting changes to this workbook & to the worksheet template.
View 6 Replies
View Related
May 13, 2009
I have a function which i am using as part of a macro. The macro itself works fine and locates a search string i type into an input box across ALL worksheets in an excel doc. However, i have a function which takes the results and prints it to the front page, but when i try to use the worksheet name which has been passed to the function, i get the error: Run-time error '42': object require. this happens when i get to a line of code which says
View 2 Replies
View Related
Jan 28, 2009
I am using the following code to enable users of a form to made make selections by adding a tick in one of the boxes by clicking on it. It works fine with thesheet unpotected but when I protect the sheet with cells E33:k33 unlocked i get Run Time Error '1004'
Unable to set the Name property of the font class.When I debug, Target.Font.Name = "Marlett" is highlighted.
View 4 Replies
View Related
Nov 26, 2008
I have a pretty simple macro that I recorded and attached to a button. The macro is:
Sub Paste_Data()
Cells.Select
Selection.ClearContents
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Range("A3").Select
End Sub
The user opens the file that contains this macro, then runs a report from a website that dumps into an excel file. They copy the data from the Book1 output, then click the button to paste it into the template. If done this way, it works fine.
However, if they run the report and get Book1 THEN open the file containing the macro, they get a run-time error 'PasteSpecial method of Worksheet class failed' on the 'ActiveSheet.PasteSpecial... line
View 9 Replies
View Related
May 27, 2008
If I want to set a global variable when I open my workbook which will be used in code on the individual spreadsheets, how would I do this? I want to set the time the workbook is opened to a variable (constant) and then compare that time to current time on each calculation in the worksheets.
I'm using Excel 2000.
View 10 Replies
View Related
Sep 3, 2013
I am trying to do the following
Code:
If Range(DataType) = 1 Then
For SubSet = 1 To 3
ElseIf Range(DataType) = 2 Then
[Code]....
Now I realise the code is wrong but what I can do in order to get a variable For statement to work. Or am I just missing the obvious.
View 3 Replies
View Related
Nov 6, 2013
Trying to Figure out the syntax for setting range properties with a variable. Is this possible?
Code:
Sub Testing()
SomeVariable=7
.Range("B7").Formula="=Average(A & SomeVariable:C & SomeVariable)"
End Sub
View 2 Replies
View Related
Apr 27, 2007
How do I set a variable to the active sheet name in VB.
Hypothetical example here:
Dim Sht as Name
Sht = Activesheet.Name
View 9 Replies
View Related
Aug 10, 2009
I am working on a macro for excel which has a userform where the user puts in the number of samples they have, then takes them to a new userform to input the weights of the samples. I want to be able to set the sample mass variable dynamically based on the number of samples they have…
i.e.
spl1
spl2
spl3
.
.
.
splx
x=number of samples
This number will be different each time so I am stumped at how to do this since I have just started in vba and do not know too much yet.
View 9 Replies
View Related
Dec 28, 2009
i am getting a run time error 1004 Delete method of worksheet class failed,
ws.Delete
Sub Save()
Dim myName As String, myFolder As String, e
Dim fso As Object, temp As String
ThisWorkbook.Save
Set fso = CreateObject("Scripting.FileSystemObject")
myFolder = "C:UsersRecsDocumentsTestVBA & ExcelEmail_Files_Temp" & Year(Date) & "" & Format$(Date, "mmm")
For Each e In Split(myFolder, "")
temp = temp & IIf(temp = "", "", "") & e
If fso.FolderExists(temp) = False Then fso.CreateFolder (temp)
View 9 Replies
View Related
Jul 31, 2009
I have a UserForm that runs when my excel project starts that prompts the user for two pieces of data: a username and a password (these are not for logging into the file itself, I need to use them to call web queries later). I want the UserForm to store both of these data in global variables, so that macros that are run in the future can refer to them and read their values. Unfortunately, I am very new to VBA and I cannot figure out what code I need and where exactly I need to put it. Here is my current code, where "authentication" is the name of the UserForm object, and the textboxes I use for entry are named "user" and "pass": In "This Workbook"
View 4 Replies
View Related
Dec 12, 2011
I am attempting to set a variable as a range of data that sits on a non-activated worksheet. However, I am getting a runtime error of 13 (data mismatch).
Is there something in my code that may be incorrect? Here is my code:
Dim rRange As Range
Dim sRange As String
Dim tblName As String
[Code]......
View 4 Replies
View Related
Nov 26, 2013
I'm not sure where my syntax is wrong, and I can't think of what to google to learn more about the possible options I have when creating and declaring the value of variables.
I am simply trying to define the variable aWB as a workbook, whose name is found in cell B6 on the worksheet named "Start" in the workbook named "DW1". This seems to make sense to me, but I am not typing it correctly.
Code:
Dim aWB As Workbook
Set aWB = Workbooks("DW1.xlsm").Worksheets("Start").Cells("B6").Value
View 9 Replies
View Related
Jan 8, 2014
I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.
The value is returned to CurrentRange, but it's the last line that's giving me the problem.
Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String
CurrentRange = Range("AG3").Value MsgBox (CurrentRange)
'This returns the value [N7:N75], which is what I want
Set UtilizationRange = Range("CurrentRange")End Sub
View 1 Replies
View Related
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
Apr 1, 2009
Here is my code
Dim LastCell As Range
Set LastCell = Cells(EndRow, LastCol).Address
I get a "Object Required" when this I try to set the address. I know this is easy.
View 9 Replies
View Related
Mar 10, 2014
I have some code to plot a column chart of data but it isn't working as expecting at the moment. The code is below. The variable binCounter is a count of how many cells in a range that I want to plot on the chart.
However, what I am finding is that the first couple of cells in the range appear as the series name with the rest appearing as the data in the chart. Secondly, the chart appears with the axis labels 1,2,3 etc when I have some custom ones I would prefer to use. How do I go about setting this property, as I can only find options on setting the axis title There is a lot of stuff on XY charts on Google but I can't find much on column charts unfortunately .
VB:
'activate sheet and chart
Worksheets("Home Page").Activate
ActiveSheet.ChartObjects("Histogram").Activate
'set variables for chart
With ActiveChart
[Code] .....
View 1 Replies
View Related
May 29, 2009
I need to write a macro to set the print area to the first two columns (A & B), and the last 12 columns (the last column may change). In both cases I need to print all rows (start is row 1, last row is variable). I am trying to achieve something like the "Freeze Pane" effect with the printer. The first two rows contain column headers (dates). The first two columns contain information that needs to be included on the printout, whilst the last 12 columns contain the most recent data.
Various cells withing the selected ranges may be blank, but no row or column will be entirely blank.
View 2 Replies
View Related
Jul 18, 2006
I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;
1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively
2)define range to be searched by how many contiguous values there are in column D
3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on
4)define new range by next set of contiguous values in column C
5) step 3
6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter
7)finish when all combinations are searched for
Sub CountComboOccurence()
Dim ListCounter As String
Dim ProgCounter As String
Dim Counter As Integer
Dim ComboValue1 As String
Dim ComboValue2 As String
Dim Rng As Range
Application. ScreenUpdating = False
ListCounter = 0
ProgCounter = 0
Counter = 0
ComboValue1 = 0.......................
View 2 Replies
View Related
