Subcript Out Of Range Error Setting Worksheet Variable

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


ADVERTISEMENT

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 View Related

Error Setting Range Variable

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

Object Required Error Setting Range Variable

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

Error Settting Variable To Range On Another Worksheet

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

Invalid Qualifer Error -setting Variable Using Getpivotdata

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

Setting Variable As Data Range?

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

Setting A Range Using String Variable

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

Setting A Range Object Variable's Address

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

Setting Values In A Variable Range Occurence Query

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

Error Setting Range In Transcribing Macro

Oct 7, 2009

Trying to write a macro to transcribe data from columns in Sheet1 to Rows in Sheet2. Assuming there is a blank between each record. I get an error on the "Set Rng1....." line.

View 4 Replies View Related

Setting Range To Value Fails With 1004 Error

May 9, 2007

I'm trying to find the last row on a sheet and then set the next cell, in column A to a certain value. It fails with Method 'Range' of object '_Worksheet' failed.

'activate the male page
sMalePicks.Activate

'add to last row of male baggage
lastRow = sMalePicks.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

'get next row
lastRow = lastRow + 1

sMalePicks.Range(Cells(lastRow, 1), Cells(lastRow, 1)) = Trim(bagNames(x))

View 5 Replies View Related

Setting A Data Range For A Chart (error 1004).

Feb 1, 2010

I'm trying to do a simple loop which creates charts based on an ID number. I recorded a macro and has tried to modify it but am having trouble defining the correct reange when settign the data source. Here is my

View 2 Replies View Related

Subscript Out Of Range - Error Relating To Setting Workbook

Jul 11, 2014

I'm trying to do the following

Dim wkb As Workbook
Dim wkb2 As Workbook

Set wkb = ActiveWorkbook
Set wkb2 = Workbooks("F:SuppliersBT Monthly Invoice2014BT Macros.xlsm")

but get a subscript out of range error relating to setting wkb2

The file BT Macros is open - this is where the Macro is stored. What have I done wrong when trying to reference it?

View 5 Replies View Related

Excel 2010 :: Setting Range - Runtime Error 1004

May 1, 2012

I am using Excel 2010. Why I do struggle with setting the range below

Code:
Set rng = Sheets("Data").Range(Cells(4, firstcol), Cells(lastrow, lastcol))

I get run time error 1004

Tried simple code from msdn and it return same error

Code:
Range(Cells(2, 3), Cells(10, 4)).Select

View 2 Replies View Related

Sum Variable Range Of Numbers From Worksheet Without Activating Worksheet

Apr 29, 2013

I care about is the line starting wks4.Cells(Di, 2) = I am trying to sum data from a variable length column in worksheet 3 and place the result in column 4 However, when I hit enter after entering the formula into =SUM() I get

Compile Error:

Expected: list separator or )

[code]
Sub Macro1()
Dim wks3 As Worksheet Dim wks4 As Worksheet
Set wks3 = Worksheets("Sheet3")

[Code].....

View 4 Replies View Related

Error 1004 Using Variable Range

Dec 29, 2013

The following five lines of code are inside a loop in which i goes from 1 to 600. When i was 594 a condition allowed these lines to be executed. (The last was in my original code and the others were just added to try to figure out why the last had a problem.) The first three work and the last two trigger error 1004 "Application-defined or object-defined error." All I am trying to do is to fill some cells with a dummy value. It doesn't matter whether I try to fill with 1 or with "1".

Worksheets("Volumep").Cells(5, i+3) = 1
Worksheets("Volumep").Range("vv5:vv104").FillDown
Worksheets("Volumep").Range("vv5:vv104").Value = 1
Worksheets("Volumep").Range(Cells(5, i+3), Cells(104, i+3)).FillDown
Worksheets("Volumep").Range(Cells(5, i+3), Cells(104, i+3)).Value = 1

Whether or not there is a different or better technique for filling cells, the range specification is the real issue I am trying to solve. The referencing issue produces the same error later in the module where more complicated work is being done.

Bonus question: is there a general prohibition forbidding the mixing of range("a1") and cell(1,1) styles of addressing in the same line of code? (Not a factor in the immediate problem but related to previous problems I have encountered)

View 2 Replies View Related

Variable In Range.Formula Causes 1004 Error

Aug 3, 2006

The problem lies in the following line

Range(Cells(1, a), Cells(b, a)).Formula = _
"=(rc[-1]-" & min & ")/(" & max & "-" & min & ") "

When it is executed i get the 1004 error ("application-defined or object-defined error)...
min and max are defined (as worksheetfunction.min and max of a selection), and their values are correct. The problem seems to lie in the max variable, namely if i simplify the formula to just

Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & min & ")"

it works ok, but if it is

Range(Cells(1, a), Cells(b, a)).Formula = "=(rc[-1]-" & max & ")"

I get the error.
I am completely baffled considering both variables are defined in the same way i.e.

min = Application.WorksheetFunction.min(Selection)
max = Application.WorksheetFunction.max(Selection)

View 9 Replies View Related

Select Range In Worksheet Where Last Cell In Range Is Variable?

Jan 27, 2012

I am trying to write code to select a range in a worksheet where the last cell in the range is variable.

Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range

[Code].....

View 8 Replies View Related

Export Variable Range As Txt File. Ommit Error Cells

Oct 30, 2006

In the attached file, I have two columns (A and B) and the column A will vary within the range from A4:A104 and column B will vary according to A. Nevertheless column A has #N/A error which is mandatory for chart. So I am looking for a macro to do the following:

Start with cell B4 and go until value exsists for "B" (let me say for eg. B40) and select the range A4:B40 and ask the user to give name for the export file export only that selection as a tab delimited txt file.

http://www.cpearson.com/excel/imptext.htm

I looked in to the above article. But i have trouble in selecting dynamic range and to avoid #N/A error in the range.

View 4 Replies View Related

Setting A Global Variable

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

Setting Variable For Next Statements?

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

Setting Formula With Variable?

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

Setting A Variable To A Sheet Name In VB

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

Setting Variable Names In Vba

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

Variable Range Copies To Second Worksheet

Apr 4, 2014

Not sure why this Code is not copying data across to the second worksheet.

The attached file 140404 Need Range.xlsm is an extract showing only the relevant elements of a much larger structure.

Drivers upload a stock record each day onto the Data Input sheet, listing what products and volumes they have loaded.

Because the range of products varies every day, this has to be set as a "dynamic" range on the worksheet.

To create a printable form, I need to copy each Item ID and Quantity from the Data Input sheet into the "Van Load" spreadsheet.

So Data Input D10 copies to Van Load E6 and the quantities from C10 to G6, down one row, repeat, etc. until the last row of variable Data Input range.

Option Explicit

Sub VANRECORD()
Dim lRow As Range, oCell As Range, nRD As Range, ws As Worksheet

Set lRow = ActiveSheet.Range("B" & Range("B65536").End(xlUp).Row)

[Code] .....

View 2 Replies View Related

Setting Global Variable From A UserForm

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

Incorrect Syntax For Setting Value Of Variable

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

Select Variable Range In The Middle Of Worksheet Down To The Next Blank Row

Jun 5, 2009

I want to select the variable range somewhere in the middle of the sheet from where the 2nd instance of cell named "real cost" is, down to the next blank row (select the area without the blank row), so that I could copy it to another sheet.....

View 6 Replies View Related

Excel 2010 :: Use Variable Instead Of Range In Worksheet Formula

Dec 3, 2013

Code:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)

I am using the above formula in my code with two Named Ranges

Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)

=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]

I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.

I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved