Set VBA Range As Cells Selected By Cursor

Jun 5, 2009

The VBA code (in the code window) runs nicely on the range B10:B1000, but I'd prefer that it only run on a range I define by the cells that are currently highlighted/selected on the active sheet. How should the line of

Set SHOPS = Range("B10:B1000")

ADVERTISEMENT

Cursor Movement :: Move To Selected Cells Only

May 16, 2007

to make cursor to move only on the selected cells.

E7, O21, O22, O23, O24, Z23, Z24, Z26, Z27, Z28, C26, C27, C28 ...

View 4 Replies View Related

Fill Range Of Cells With Text When Listbox Option Selected - Clear When Not Selected

Jul 25, 2014

I am using this code to hide or unhide rows of text on another sheet:

VB:
Sub ProcessSheet1ChangeOnCellJ7(ByVal Target As Range)

Dim sAddress As String
Dim sValue As String

'Get the address of the cell that changed without '$' signs
sAddress = Target.Address(False, False)

[Code]....

When the "Not Pursuing" list box option is selected (in cell "J7" or "J8" in Sheet 1) I need to add (or over-write) "Not Pursuing" to the range of cells in column "B" (in the "Tasks" sheet), but only for that particular Goal, meaning a limited range of cells in column "B". If the "Pursuing - Show All Tasks" option is selected for a Goal then these same cells need to be blank so that the appropriate person can enter their name into the cell.

The purpose for adding "Not Pursuing" automatically to these yellow highlighted cells is that it will facilitate filtering of tasks by individual in the "Tasks" sheet..

Again I have tried several times to upload a sample file and am unable to, which I know makes it more difficult to solve. (Is there some common mistake people make? I know it's an allowed format and is very small in file size....)

Code solution can be entered directly beneath:

VB:
If Target.Value = "Not Pursuing" Then
ActiveWorkbook.Sheets("Tasks").Rows("29:29").EntireRow.Hidden = False
ActiveWorkbook.Sheets("Tasks").Rows("30:48").EntireRow.Hidden = True

View 1 Replies View Related

Define Range By Selected Cells

Jul 19, 2014

I am working on some code to copy data from one sheet to another, but I'm not sure if I'm going about defining the copy range correctly. I would like it so that the user can highlight a range of cells on the sheet between A3 and F last row (last row based on col C). However the user should be able to highlight rows in any column between A and F, and they could highly the records with just one column or multiple columns. It should take the row numbers of the highlighted range and use the that as the row number to extract the data from.

[Code] ........

Im currently getting runtime error 13 on 'CopyRange = Selection.Rows'

'CopyRange = Selection.Row' returns the single row number for the first cell in the selection, but i need the range of all the rows in the selection.

View 6 Replies View Related

Paste A Selected Range Of Cells

May 11, 2007

I think I have the comand to select a range of cells, but can not figure how to paste this selection later in the spreadsheet.

This is how the application works.

I have a spreadsheet that I am using as a template. The first 10 rows have to be repeated later in the same spreadsheet after I make a manual page break.

I the spreadsheet I am doing the following commands:
With xlApplication.ActiveSheet
xlPageBreakManual
.Rows(istartrow).Pagebreak = xlPageBreakManual
End With

View 9 Replies View Related

Change Color Cells In Range Selected?

Aug 28, 2013

[URL] and how I could modify the conditional formatting/vba to return the same effect but for a selected range, not just a cell?

View 2 Replies View Related

How Do I Total Selected Data From A Range Of Cells?

Nov 22, 2009

On worksheet 1 I have Column A with a list of names, which we shall call John, Paul, George and Ringo, listed randomly within 100 cells (A1 - A100).
I have Cells B1 - J100 with 3 options in each cell; Blank, C and NYC.

I need a list on worksheet 2 for each individual person showing;

(1) total number of cells marked C
(2) total number of cells marked C & NYC combined.

View 14 Replies View Related

Macro Leaves All The Cells In The Range Selected

Jan 20, 2006

I have a worksheet with ever expanding data - rows at the bottom of the data
are continually added. I have a simple macro that sorts all of the data
according to preset parameters and selects the next blank cell in column A,
ready for more data:

Sub Macro5()
Range("SortRange").Select
Selection.Sort Key1:=Range("SortRange"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Do Until ActiveCell.Value = IsEmpty(True)
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

When running the Macro, this leaves all of the cells in the range 'selected'
(ie; coloured-over). What do I need to add to the Macro to just select the
cell in Column A and remove the highlighting from all the other cells?

View 9 Replies View Related

VBA Code For Summing Of Selected Range Of Cells

May 12, 2011

I often find myself in the following situation - I will highlight a range of numbers to see what the sum is in the bottom right hand corner (the cells which I select areoften not all within the same column or are not consecutively listed under each other) .Depending on the circumstances I will then type out a SUM formula with the range to put that value somewhere in the spreadsheet - the problem being that I have to deselect the cells and then re-select these within the formula. When I have selected many numbers, it is not always easy to remember every number which I included.

As a way to remember the numbers, I format those cells in a certain colour before I do the SUM formula and would then include only those coloured cells in the formula. I would like to avoid this extra bit of work.

I am new to VBA but would like some form of code that allows me sum the contents of a selected range and paste that formula beneath the last value in the range - the range would have been selected before running the maco.

View 7 Replies View Related

Use Range Of Selected Cells As Relative Reference

Mar 8, 2013

I have a data that is split into multiple cells and needs to concatenated. Unfortunately, the number of columns wherein lies the data varies throughout the workbook. As such, I wanted to select a range of cells, define this range as the reference point for the macro, run the macro, then move on (selecting a different number of columns on the next try).

For example, I have this:

A
B
C
D
E

1

No
Not Very Far

[Code] .....

I want to select cells A1:B1, run a macro concatenated the two columns, then select cells C1:E and run the same macro to get this:

A
B

1
No
Not Very Far

[Code] ........

So far, I have this:

Code:
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(5, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],RC[2])" '

View 9 Replies View Related

Macro To Add Formula In Selected Range Of Cells

Apr 26, 2013

I have a spread sheet with large number of data, problem is all are in various currency so rather than typing =#####.##/a1 in every cell to get the GBP amount (a1 where my exchange rate is linked) I thought if there is macro can do this job for rme.

So what i need is macro which once run enter the formula after the numbers already in the cell in selected or given range.

View 1 Replies View Related

Matching Cell Content Against Selected Cells - Not Range?

May 2, 2013

I'm trying to use the below formula to to return "true" when text in cell (M2) matches text in selected cells on same row (A2, E2, and G2)

However, all formulas I found online that serve this purpose deal with cell range only, not predetermined cells with exclusions.

Formula:

=IF(ISNUMBER(MATCH(M2,A2:G2,0),TRUE,FALSE) (This will match all cells between A2 and G2 against M2)

reforming this formula to match selected cells only in-between that range (A2, E2 and G2)?

View 7 Replies View Related

VBA For Fill Zero For Empty Cells In Selected Range Using Mouse

Feb 13, 2013

I require a VBA code to fill Zero for empty cell in seceleted Range. Range will be select by Mouse.

I try following Code.

But It will not sucessed.

HTML Code:
Sub Zero()
Dim ws As Worksheet
Dim Specifiedrange As Range
Dim Rng As Range

[Code]....

View 5 Replies View Related

Changing Color Of Selected Range Of Cells Using Macro

Jun 17, 2013

I'm trying to create a macro that will change the color of the cells I've selected to green. My selection will vary depending on what cells I'm trying to color green (not a fixed range). My current code only changes one cell of my selected range:

Sub IN_PCA()
'
' IN_PCA Macro
'
'
ActiveCell.Select
Range("M243").Activate
With Selection.Interior

[Code] .......

I've tried using "ActiveRange" in lieu of "ActiveCell" as well as other commands that would seem to be correct but have failed.

View 3 Replies View Related

Set Range From Selected Cells/Range

Sep 16, 2009

I have a macro which I manually have to change the range in order to run the macro, e.g. Set rngData = Range("B4:I12")

rather than having to change the range for each macro, I was wondering if I could run the macro for the highlighted area. I have tried this, but doesn't seem to work.

Set rngData = Range. CurrentRegion.Select

View 2 Replies View Related

VBA Find Function - Search Through Selected Range Of Cells For Key Letters

Jan 6, 2014

I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.

Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0

[Code] ........

View 9 Replies View Related

Fill Userform Textboxes From Select Cells In Selected Range

Mar 7, 2008

Arised from my earlier posting in Populate ComboBox With Specific Sheet Column Range. I have the following working code below, but am having trouble finding coding examples to select specific cells from the selected row (that was found by selecting a ComboBox value)and update TextBoxes with those individual values after the UserForm has been initialized (the bold "GREEN" comment in the code below). I have been able to find plenty of references to update TextBox values to Cells, but that doesn't do me much good in this application since the User needs to verify the old data in these cells before updating them using the UserForm TextBoxes.

I was toying around with several different variations of code (none of which worked properly), so I left it out for clarity of my working code. I'll post up this non-working code as needed, because I really wanted a fresh answer...not what I was trying to do. The attached file should be sufficient to see what's going on

Private Sub UserForm_Initialize()
With Sheets("SR Information")
.Range("A2", .Cells(Rows.Count, "A").End(xlUp)).Name = "MyRange"
End With
SRnumber.RowSource = "MyRange"
End Sub

Private Sub SRnumber_Change()
Dim ServiceRequestNumber As String
Dim c As Range
Dim rngG As Range
Sheets("SR Information").Select
With Selection
ServiceRequestNumber = SRnumber.Value
For Each c In Intersect(ActiveSheet.UsedRange, Columns("a"))
If c = ServiceRequestNumber Then..................

View 4 Replies View Related

Set Active Cursor In TextBox (blinking Cursor)

Sep 17, 2009

I am working with a VBA userform and several textbox's, setting SetFocus and or TabIndex doesn't leave the box ready to accept input and there is no cursor shown to indicate it is ready to accept input.

View 2 Replies View Related

Excel 2010 :: Auto Lock Selected Range Of Cells After Saving?

Jul 17, 2014

I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.

While all of the above activity is going on, new customers are continuously being in added in G4, G5....

Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010

[URL]

View 1 Replies View Related

Excel 2010 :: Auto Lock Selected Range Of Cells After Saving

Jul 17, 2014

I have a log that is used to keep when items are due. I do not want my coworkers to delete items once they have been entered and saved. So the excel sheet I am trying to make has certain cells in a range that I want to autolock after saving. For example: the cell range is G3:J402. I enter Customer Name in Cell G3 and the Date the job was received in H3. Once i enter that information I want it locked once i save it. Then later on, my coworker completes the job and enters the date in I2. And again once she saves this she shouldn't be able to edit this information. Then again another day she comes and enters the date on I3 when the product has been shipped out.

While all of the above activity is going on, new customers are continuously being in added in G4, G5....

Also, if possible the date in the H range should be the date items are entered in G (this would be a nice added feature). I've included the file for review. I'm running Microsoft Excel 2010.

I have tried hard to find other solutions with links at the following places but not exactly what I'm desiring:Auto lock cells after data entry when file saved...

Auto lock cells after data entered and SAVED.

Auto Lock Selected Range of Cells After Saving

I should also mention that most solutions either provide for locking/unlocking of all cells but not a selected range. Or they provide for locking/unlocking of a selected range but not for a specific sheet or not after you have saved the workbook.

This is unique in that I'd like it to autolock after i press the save button for a SPECIFIC number of cells. I just wanted to clarify as to not make others think that I haven't used the search function.

View 1 Replies View Related

Excel 2010 :: Copy Range Of Cells From One Sheet To Another Depending On Option Being Selected From Dropdown Box

Aug 13, 2012

Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.

Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names

E.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown

The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.

View 3 Replies View Related

Copy A Selected Range Of Cells On Sheet One To A Range On Sheet Three

Aug 7, 2008

Hi. Does anyone know a formula to copy a selected range of cells on sheet one to a range on sheet three when a check box in checked. Ex. copy range a4:j4 on sheet one into a4:j4 on sheet three once the check box for on sheet one is checked?

View 9 Replies View Related

Cursor Brings Up Multiple Cells

Apr 17, 2009

when i position the cursor over a cell, i get muliple cells highlighted. i enter data and it is entered in the cell i want but the others being highlighted is confusing. i checked and i am not in extended mode.

View 9 Replies View Related

Cursor Movement :: To Defined Cells Only

May 10, 2007

In sheet1, The cursom must move only following cells

A2,A5, C10 C15, D15, E50

Besides this the cursor should not move to any cell.

It must remain between the cells that I described above.

View 14 Replies View Related

Cursor Movement :: Move Multiple Cells

Mar 27, 2008

Is there a way to make it so when i hit enter my cursor moves 7 cells instead of 1?

View 9 Replies View Related

Change Mouse Cursor When Passing Over Specific Cells

Jun 12, 2009

I would like to change the mouse cursor when the pointer passes over (without clicking) some cells which have double-click-event script attached.

I know how to change the cursor with a custom one. The pb is for me to identifie that the pointer is over the cell so as to launch the cursor change macro (and reverse when going over another cell).
Excel does that, for ex, with commented cells but can VBA do it also?

View 8 Replies View Related

Calculate Only Selected Range

Oct 5, 2009

I'm currently on Manual Calculation mode. I have formulas in cells A1 to A10 which has not been refreshed. Can I select only range A3 to A6 and recalculate only those cells?

View 5 Replies View Related

Expand Selected Range

Nov 15, 2009

I need help please. Using a macro, I have selected the range C7:C12. Now I need to keep this selection and expand it 18 columns to the right. This would give me a selection of C7:U12.

The original range changes at various times. Some times it might be C7: C452 etc.

I know it's easy, but I can get the code to keep the selection.

View 6 Replies View Related

Inserting Range Above Selected Row

Mar 8, 2012

I have a range (of 2 rows) that are set up as a header row and sub row. I want a macro to insert these above the active row (ie. where the user places the cursor), but when I select and copy the range in the macro, I don't know how to refer back to the 'active row' because that's not active anymore.

I'd also like the cursor then to be placed into one of the cells in the new row, ready for the user to start editing.

View 6 Replies View Related

Looping Through Selected Range

Jul 2, 2013

I am looping through a selected range. The code searches through column B and finds any data. If found, it returns the value to a different workbook to column B. When there is no more data, it goes to column C and does the same thing. My problem is, when there is no data in a column it returns a blank. How do i get rid of this blank???

View 1 Replies View Related

Count & Sum Of The Selected Range

Mar 16, 2007

Count & Sum is one of the most usefull function in the Excel,
I always used this, Is it possible in VBA to create such code that if I Select a range and click on Commandbutton1 automatically one msgbox display with the Count & Sum of the Range Item

eg. If Range A2 to A5 has a number like 50,10,20,10

here if the command button is click automatically one msgbox is display
which showing the

-Total Sum of the Seletced Range is 100 and Total Count is 4

View 9 Replies View Related

If Statement With Selected Range

Aug 10, 2007

I'm trying to create an if statement that have 2 criterias. Here's what I've created:

=IF(COUNT(F2:F4)=('2007Open'!H2:H4),COUNT('2007Open'!B2:B4)=8,'2007Open'!H2:H4)

This statement is returning the #VALUE error.

I guess what I'm trying to do with this statement is to count how many values are not equal to 2007Open!H2:H4 and meets another criteria of 8.

View 9 Replies View Related

Autofill Using A Selected Range

Jan 4, 2008

I am using the following code to Autofill a formula through a variable range:

Selection.AutoFill Destination:=Range(MyFormulaCell, TerminalCell), Type:=xlFillDefault

MyFormulaCell is the cell with the formula, and TermnialCell is a variable set on a count of rows. This is working great, and I can loop through this for the columns I need to copy just fine by incrementing values, but as I apply this to several sections, it's taking me up to a minute to run the macro. In this "I want it now" day and age, I'm sure my coworkers for whom I am making this will not be happy to wait that long.

Is there a way that I can incorporate a selected range into this method?

I'd like to set MyFormulaCell to:
Range(Selection, Selection.End(xlToRight)).Select
and then autofill the formula down the number of rows contained in TerminalCell.

View 9 Replies View Related

User Selected Range

Dec 3, 2008

I have a form that asks at what row the user wants to start with a selected range then how many rows to select. There will always be 21 columns selected with this range. I am still learning but it has to do with something on how I am setting the Start object. It is not being recognized.

Dim Row As Long
Dim Selection As Long
Dim Start As Range

Row = txtStartRow
Selection = txtHowMany
Set Start = Range("A" & txtStartRow).Select

Range(Start & ActiveCell.Offset(Selection, 20)).Select

View 9 Replies View Related

Delete Selected Range

Mar 17, 2009

My data is in one column as follows:
ABC
(BLANK)DEF (BLANK)(BLANK)
What I want to do is to delete the blank cells and count how many not-empty cells.

The following sub works fine if I put the active cell manually in A1. However, the first range select always give me the "1004" error. Why? I don't have any clue.

Range("a1:a5").Select
Do Until ActiveCell.Value = "stop"
Do Until IsEmpty(ActiveCell)

Selection.Offset(1, 0).Select
Loop
ActiveCell.Delete
Loop

View 9 Replies View Related

Chart Selected Range

Nov 29, 2006

I am trying to link data that has been entered into a inputbox with a range of cells.

My goal is to allow the user to answer: how many people sat the exam?

Therefore when a chart is automatically produced only the cells with the required data are selected.

So, my range is A2:C*

* being the user input.

View 4 Replies View Related

Naming A Selected Range

Mar 15, 2007

I am trying to name a selected range in VBA in order to use it in a vlookup table. Code I have so far is as follows:

ActiveSheet.Name = TextN
Range("c9"). CurrentRegion.Select
Call formatting
ActiveWorkbook.Names.Add Name = "formTextN", RefersToR1C1 = Worksheets(TextN) & "!" & "r9c2:r45c13"

The formatting works, but the selection does not get named.

View 3 Replies View Related

VBA ListBox - If Range Is Equal To One Of The Selected

Dec 19, 2012

I have a spreadsheet with names of students, year groups, subjects and others. My VBA code will analyse student's results, but before I get there I would like the user to be able to select groups of students using listboxes.

Now I have a tonne of variables that I won't bore you with but the main thing I would like to do is to scan through each cell in a range on my spreadsheet and determine if the value of that cell is equal to a selected option in the listbox. This will let me, for example, filter by only Year 10 students, or select to display only Year 9 and 11 students.

I already have the code to scan through each cell:

VB:
'Run through each student and check they match the criteria
For N = 1 To StudentList.ListCount

'Split the first and second names
StudentName = Split(StudentList.List(N - 1))

[Code] .....

Essentially, I would like to take the cell "A" & N2 and check to see if it matches one of the selected options in a listbox called "YearList"

View 1 Replies View Related

Highlight Range Of Row Then Cell Selected

Apr 2, 2014

i have data in range c10:h100

i want if select a cell e.g = c15 then

highlight the range c10:c100 with color

now highlight the all row only highlight defined range

View 5 Replies View Related

Applying Macro To Selected Range

Apr 3, 2014

I have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.

View 3 Replies View Related

Calculating Percentiles Of A Selected Range

Apr 12, 2014

What I need is the following: I select a range of numbers and for this range I want to calculate the 1/3th and 2/3th percentile, and these should come in two cells that I can easily change in the vba code.

Some formula in which I can give two inputs: first the range of which it has to calculate the 1/3th and 2/3th percentile and a second input which is just a number and than the vba should say whether that second input is in the first range (between 0 and the 1/3th percentile value) or in the second range (between 1/3th and 2/3th percentile values) or third range (between2/3th percentile value and highest value).

View 1 Replies View Related

ADVERTISEMENT