Code To Select More Than One Range At A Time?

Feb 1, 2012

I thought i would create some code to highlight a batch of cells so i can modify formatting all together rather than cell by cell

I am unable to get the generated code to select all ranges together, it only shows the last range

Here is my code

Code:
Sub SelectRanges()
'
' Macro1 Macro
'
'
Range("D12:H12").Select

[Code]....

View 7 Replies


ADVERTISEMENT

Run-time Error 1004 When Using A Range(rangestring).Select

May 11, 2007

I'm getting a Run-time Error 1004 when using a Range(rangestring).Select.

It only seems to occur when I'm trying to select more than 35 cells. Up until 35 it works just fine.

My code is designed to select individual cells in a worksheet based on user selections from a list, and as I said, works fine unless they select more than 35 items.

Is there a limit to how many cells can be selected using Range...Select? If so, I can't find it documented anywhere.

View 14 Replies View Related

Select Column Or Range Based On Current Time

Nov 18, 2013

setup:

Columns: c)6:00 AM, d)6:15 AM, e)6:30 AM
Rows: 4)tech1 Phone, 5)Tech2 Phone, 6)Tech3 Phone

What I am tying to do is to select the column that matches the time, for instance anything before 6 AM would select Column C, betweeb 6 and 6:14 still selects column C, 6:15 to 6:29 selects column D, Etc

View 2 Replies View Related

Code To Select Actual Range

Feb 25, 2009

I have created the following code by recording a macro. The ActiveCells (A1.A5203) were based upon the actual number of records at that time. I wish for this macro to be used in a template and the actual number of records will vary from time to time. What do I need to add to this code to allow the AutoFill function to operate and fill only the actual number of records that exist.

ActiveCell.FormulaR1C1 = "=LEN(RC[-2])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=8,""0""&RC[-3],RC[-3])"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A5203")
ActiveCell.Range("A1:A5203").Select

View 9 Replies View Related

Select Range Macro Code

Apr 17, 2008

I have made a sheet where the user can insert/delete rows in a certain intervall of rows. The upper row is 17, that is I have named the cell "First" and then the last row "Last" and then I am using integers:

Dim intStartrow As Integer
Dim intLastrow As Integer
intStartrow = ActiveSheet.Range("First").Row + 1
intLastrow = ActiveSheet.Range("Last").Row - 1

So it is only possible to add/delete rows if the markerer is in between the above rows.

Now I would like to select the rows, from first to last. Since the rows are never the same, users add and delete rows all the time, I can not use

Range("A17:Z49").Select

Can I somehow make a range selection using the Integers, somethimg like

With ActiveSheet
Selection."intStartrow:intLastrow"
End With

View 3 Replies View Related

Run-time Error '1004' : Select Method Of Range Class Failed

Jul 28, 2006

I have a userform that allows the user to view, modify or delete individual records (rows) of a worksheet in a workbook. If a particular item in a record is modified, a check occurs to ensure that the values for that item remain unique. All of that works exactly the way I need. The breakdown occurs when the records are then autosorted by a the values in a particular column. After the autosort, another column of values is copied and pasted to a different worksheet within the same workbook. For this to work properly, I must have the userform open along with the worksheet containing the records. If the userform is open with any other worksheet open, I get the following VBA error message "Run-time Error '1004' : Slect method of Range class failed". I need to be able to open the userform with any worksheet active and not experience this error when the autosort, copy/paste occurs. This is the code for the user form, the module for autosort, copy/paste and checking for unue values.

This is the sub in the worksheet with the records

'This checks for unique values
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim LLoop As Integer
Dim LTestLoop As Integer
Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String
Dim smessage As String
'Test first 7 rows in spreadsheet for uniqueness
Lrows = 8
LLoop = 2
'Check first 7 rows in spreadsheet
While LLoop <= Lrows.........................

View 3 Replies View Related

Run-time Error '1004': Select Method Of Range Class Failed

Apr 7, 2007

I get the following error: Run-time error '1004': Select method of Range class failed at the following code line:

Worksheets("Sheet1").Range("A6").Select

View 4 Replies View Related

VBA Code To Allow User To Select Range In Another Open Workbook?

Jul 26, 2013

I need the code to allow a user to select a column of their choice in an open workbook of their choice so that data can be lookuped up and changed. I am happy to create the code to do the lookup/changing, however the nearest I have found is the use of an inputbox which is limited to the active spreadsheet only. Some people have said the input box could be used over to another spreadsheet but I have not found that to work.

What I will need returned is the workbook name, sheet name and column. That will allow me to point to that and do the task.

This code could be used by 5 people and those 5 people could be running it on 20 different spreadsheets.

I thought this would be easy to find but it has eluded me. When using Excel normally, if you do a simple lookup formula, or create pivot function, excel prompts the user to select the workbook/sheet.

View 4 Replies View Related

Allow User To Select Range To Copy Macro Code

Apr 30, 2008

I cant seem to get to work together in the same macro but that work great seperated. I need them to be in the same macro. The first just simply copy's text from one workbook to another:

Sub Test2()
ActiveSheet. Range("a1").Copy _
Destination:=Workbooks("punchlist.xls"). Sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Offset(1,0)
End Sub

That worked ok but I needed to change it to the "active cell" instead of cell "a1". So then this line of code was made:

Dim userInputCell As Range

On Error Resume Next
Set userInputCell = Application.InputBox("Use the mouse to select a cell on any sheet", type:=8)
On Error Goto 0
If userInputCell Is Nothing Then
MsgBox "Cancel pressed"
Else
Msgbox "You selected " & userInputCell.Address(,,,True)
End If

The second code works just the way I want it but it doesnt copy over the text to the other workbook. I assume the 2 codes need to be together but I cant get it to work without errors. I also need the text to copy over without changing the borders on either workbook.

View 3 Replies View Related

"Run-time Error '1004' (Select Method Of Range Class Failed)

Aug 19, 2009

Newcomer to Excel 2007 but long time programmer for Excel 2003. I am having problems with code that I feel should work. Code fails at the Select command with a "Run-time error '1004': Select method of Range class failed:

View 3 Replies View Related

VBA Code To Select Cell Range And Save As Text / XML File

Jul 19, 2013

I am trying to write the code to;

1) change to a different sheet
2) select a specific cell range
3) save that range as a text / xml file with a filename derived from a cell outside the given range

Here is where I have got to so far, but it fails

Private Sub Export_Click()
Sheets("Parsed Data").Select
ThisFile = Range("B1").Value
ActiveWindow.SmallScroll Down:=-15
Range("A1:A41").Copy
SaveAs Filename:=ThisFile, _
FileFormat:=xlTextMSDOS
Application.WindowState = xlMinimized

End Sub

View 2 Replies View Related

Excel VBA Code To Select A Range (rows) Based On Values And Delete

Aug 8, 2013

I am trying out with a code which checks for cell value as "Select" in column IU and then checks for corresponding column IV for value as "0". Please note that "Select" and "0" are populated by formulas. I need the select "Select" and "0" till the next "Select" occurs in column IU and delete the selected range and continue the process until last non empty cell based on column C.

I have written the below code but it doesn't work.

Code:
Public Sub Test()
Dim nRow As Long
Dim nStart As Long

[Code]....

I could have uploaded the excel file that I am working on but did not find any upload attachment option.

View 1 Replies View Related

Run VBA Code Any Time A Particular Range Of Cells Is Changed

Aug 23, 2002

Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?

I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.

1. Is there a way to call VB (say, sheet1.myFunction())?

2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)

The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.

View 9 Replies View Related

Range.select Error (SELECT METHOD OR RANGE CLASS FAILED)

Jul 23, 2008

I have this:

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial
End Sub
it errors to: SELECT METHOD OR RANGE CLASS FAILED

View 9 Replies View Related

Forumla To Give A Starting Number And Take One Away Each Time A Code Or Codes Are Dislayed In A Cell Range

Mar 22, 2009

In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this

A1 = 23 days or any number days i needed

Cell range B1:C52

every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD

View 4 Replies View Related

Range Select Statement To Select A Cell

Jul 7, 2008

I want to put a range select statement to select a cell and count down 10 cells and copy.

View 9 Replies View Related

If Select Sheet, Select Range Statement

Oct 4, 2007

I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now.

Private Sub Worksheet_Activate()
If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then
Me.ClearPrevious.Visible = True
Else
Me.ClearPrevious.Visible = False
End If
Sheets("MASTER").Select
End Sub

View 9 Replies View Related

'Select Method' Failure 'error 1004 Select Method Of Range Class Failed'

Oct 28, 2008

My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,

View 4 Replies View Related

Select A Time From A Clock?

Jun 12, 2013

I've linked some cells to the date picker function so a date can be selected from a pop-up calendar.

Is it possible to do the same with a clock function?

View 2 Replies View Related

Select A Range That Will Be Changing By Column: Method 'Range' Of Object '_worksheet' Failed

Jan 6, 2010

I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:

View 2 Replies View Related

Average Values If Associated Time Of Occurrence Falls Within Certain Time Range?

May 2, 2014

Refer to the attachment. I am trying to average the data in the Y column, if the times fall into the range between column R and S. I am having trouble with the averageif function. Is there a better way to parse through column W, check if the values fall between the ranges of S and R, and if they do, average the associated values in column Y?

Attached image: Capture.JPG‎

View 5 Replies View Related

Split Time Range Into Hours Distributed By Time Ranges

Mar 5, 2014

What I need is a formular/vba code that can divide hours into different time ranges. The picture below shows an explanation.

In example 3 there time range analyzed goes from saturday till monday. Here Sunday should get 24 hours and the time range 23-08 for weekdays is at this point 00-08.

I've tried everything I could imagine to get this one working,

View 1 Replies View Related

Run Time Error 1004 (select Statement)

Apr 13, 2007

I get an application defined or object defined error at the select statement 1004...I know I know I shouldn't be selecting in code so I'll fix that but why can't I set the range?

NumRows = Sheets("Timelines").Rows.Count
LastColumn = Sheets("Timelines").Range("A1").End(xlToRight).Column
LastRow = Sheets("Timelines").Range("A" & NumRows).End(xlUp).Row

Sheets("Timelines").Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
TIA, Charlie Brown.

View 7 Replies View Related

VBA Code To Select A Value Between N Or S

Nov 9, 2011

VBA Code which would ask me to select a value between N or S.

If I select "N" then the pivot field items "Guj","Del","Mum" should get selected.

If I select "S" then the pivot field items "Bng","Kar","KER" and "TIR" should get selected.

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

Macro Code For Select All

Jul 22, 2011

I record macro and hit select all and that action won't record. what's the macro code for select all in excel. i assumed it was the same as word

selection.wholestory

but it's not

View 7 Replies View Related

Select Next Sheet Tab Code

Aug 18, 2006

Is there a simple bit of code which I can add to a button which will always select the next tab along?

Looking to add a 'next tab' button at the top of each sheet so it can be run through quickly, almost as a presentation.

I could just do

Sheets("TabName").Select

But that would involve doing a new one for each sheet, and would need modifying if I then re-ordered the sheets.

View 4 Replies View Related

Code To Select First Empty Row

Jun 1, 2007

What would be some code to select the 1st empty row in a sheet. I need to select entire row. To be easy it could be first empty cell in A, but would like whole row selected.

View 3 Replies View Related

Code To Select A File Instead Of Folder

Jun 2, 2014

I have a code that gives me path of all files inside a folder. I want to modify it.

I want to select files (individually or in group) inside a folder and then get the path. All files are .xlsx in folder.

[Code] .....

View 1 Replies View Related

VBA Code To Select All Values Except 0 In A Filter

Jun 25, 2013

I have a workbook and have created a filter across range A1 - BU1. I want to filter column BQ. In this column I want to use the filter to show all values except for 0.

View 2 Replies View Related







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