Number Selection

Oct 30, 2009

To select cells of a certain value I would use something like this:

If Range("E" & n).value = 90 Then
Range("E" & n).Select
End If

But I need to select cells with numbers starting with a certain number although they may not neccesarally be the same number of digets, ie i would equally want to select 789, 78910, and 700000000

How would you do this in VBA?
______________________________________________
Also, is it possible to use the above script to select multiple values ie

If Range("E" & n).value = 90 or 165 Then
Range("E" & n).Select
End If

View 9 Replies


ADVERTISEMENT

Random Number Selection...

Apr 19, 2006

random number selection...

i have a list of 200 items (rows) and i would like to "randomly" select 30 of them...(next month i will get another 200...)

View 3 Replies View Related

Count Number Of Characters In A Selection?

Jun 12, 2014

I am using the macro below to count the number of specific character in a cell. The only problem it only counts one cell. How do I modify to display multiple cell counts if more than one cell is selected?

[Code].....

View 4 Replies View Related

Set Selection - Address Of Smallest Number?

Mar 21, 2012

very simple and short code.

Code:
Dim selection As Range
Dim minimum As Integer
Sub Sequence()
Set selection = Range(Cells(4, 3), Cells(6, 5))
minimum = Application.WorksheetFunction.Min(selection)
' and now I need an address of that smallest number for this:
' IF (column number + row number) > value of that smallest number
' do
End Sub

It's not exactly the program I need, but it's simple.

View 9 Replies View Related

Count The Number Of Rows Of Selection

Jun 7, 2006

First - What I need is an idea of how to just get the number of rows on a selection. For instance -

select worksheet
select paticular area (easy like a1)
get the number of rows that comes from that selection (there could be upto 5 rows that are merged by the front end, what i need is the number of rows that have been merged)

and Second -

once i have that number of rows - the next portion that I need selected could be a different colum - how do you add to columuns without knowing where you are at -

for instance - knowing that you are at a1 (a) but then having togoto columum number 4 row 1 (d1) - so i guess the question is is how I convert a number to the correct character then add the number at the end then use that compilation to select. something like

delare temp
get updated colum letter from corresponding column number
combine column letter and the row number you already know
select that in the worksheet

View 2 Replies View Related

Cell Offset Based On Number Of Cells In Selection

Apr 15, 2014

I am working on a macro and this section has me stumped. The worksheet columns are A:AE. Occasionally, the procedure needs to select a varying number of cells in a row which don't always fall in the same column, count the number of cells in the selection and then paste the data on the row above it, in column ("AE" minus the Selection Count).

Here is what I have so far:

[Code] .....

The red-bold bracketed code is the syntax I'm not able to figure out how to phrase.

View 11 Replies View Related

Displaying Input Box Based On User Selection Of Dropdown Selection?

Jun 3, 2013

If a user selects certain options from a drop down list created by cell verification (from list), is it possible to display an input box and have the resulting input populate on another worksheet in the workbook? I have an attendance template I'm working with and if a user selects OT (Over Time) then a input box is displayed prompting for how many hours. The overtime is then tracked on another worksheet.

View 1 Replies View Related

Increment Cell On Selection And Lock/Protect From Re-Selection

Oct 3, 2006

I tried very hard to design a leave roster for user to mark their leave application. The criteria are as follows:

1. 4 applications per date
2. a region of worksheet (i.e. "A2 to H20) will be defined for users to "click" on the cells (within the defined region) to mark their application.
3. once a cell is clicked (i.e. marked), it cannot be altered.
4. each click will automatically increase the total by 1.

I got the following code to deal with the situation but user can still click on any cell outside the region (in fact I fail to define the region).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim m
ad = Mid(ActiveCell.Address, 2, 1)
m = Range(ad & 24).Value 'here a formula "CountA(A2,A20)" will be place in the cell (24, c).......................

View 2 Replies View Related

Copy & Paste Selection Based On Combo Box Selection

Jan 30, 2007

I have attached a portion of an excel file I am using at the moment. What I require is a piece of VBA code which will allow me to copy selected data based on a combo box selection. What needs to happen is this :-

If the selection in the "Index" worksheet combo box related to "Month" in cell G19 is for instance "December", I would like the macro to compare this cell value to the cell values in cells C96, C124, C152, C180, C209, C236, C263 and C290 in the "Tech Services" worksheet and where the values match.......copy the commentary (Range C126:Z147 in December's case) to the range C34:Z55.

View 9 Replies View Related

Disable Selection In Listbox On Selection Of Item

Apr 4, 2013

I am working on a project where user has to select an item from a listbox, however there is a condition that if user select an item, other items of listbox should be disabled so that he/she can not select any other item.

Is there any way to do that as I tried

VB : Listbox.enabled = False
and
VB : Listbox.locket = True

in Listbox - Click/Change but no desirable results got.

View 1 Replies View Related

Restrict Drop-Down Selection Based On Selection From Another

Jul 20, 2007

I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.

e.g. In cell C115 I want to have the value BDS05.

I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.

I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).

View 6 Replies View Related

Run Selection Change Event Only If The Row Selection Changes

Mar 26, 2007

I'm using the following code in a worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
col = ActiveCell.Column
Range("output") = ActiveCell.Offset(0, -(col - 4)).Value
End Sub

In case it's not obvious, the macro places the value in the active row and 4th column of the worksheet into the range "output".
The problem is, the worksheet is large and somewhat slow to recalculate. This macro forces a recalc on any selection change, but i only need it to run when the row selection changes, not the column.
I'm sure there's a straightforward way to reprogram this.

View 7 Replies View Related

Investment Selection With Cell Selection

May 4, 2007

I'm trying to build an investment calculator. Tried searching "calculator" as keyword but return nothing similiar.

There are eight cell : [ A ][ B ][ C ][ D ][ E ][ F ][ G ][h]

[ H ] is price of the leverage

[A] is X balance
[b] is X lot size
[C] is X amount of money use to get x lot
[D] is X percentage of money use to get x lot
[E] is X point gain
[f] is X money gain
[G] is balance + money gain

Ignore the formulae for leverage to determine 1 lot price. Take 1 lot = 250.

What I'm looking for is when I enter any value at cell B, C or D, it will automatically calculate the value for either B, C or D cell.

Let say, the balance is 10000.

If I enter 2 at cell B. Then, it will calculate the value for cell C & D.

If I enter 750 at cell C, then it will calculate value for cell B & D.

If I enter 25 at cell D, it will calculate value for cell B & C.

how to use OR Selection in excel.

View 9 Replies View Related

If Selection Or Any Part Of The Selection

May 24, 2007

If the selection (or any part of the selection) = bla bla bla

View 9 Replies View Related

VBA - Selection Box

Jul 13, 2007

I have a code (pasted below) that copys a line of data from one workbook into another. Each time i run the code it enters the data on the next available line. This is all working ok however what I would really like to do is when i run the code have it pop up a selection box asking me which sheet i would like to copy from. The reason i need this is that the sheet that gives the data isn't always named the same although the format is exactly the same every time. In this example the data sheet is "copy of NewProductForm), this name may change and i may also have other different active workbooks open at the same time and I don't want the macro to get data from them.

Code :

Sub GetData()
Windows("Copy of NewProductForm.xls").Activate
Rows("48:48").Select
Selection.Copy
Windows("Book2.xls").Activate
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Copy of NewProductForm.xls").Activate
Application.CutCopyMode = False
Range("A41:AH41").Select
Windows("Book2.xls").Activate
End Sub

View 9 Replies View Related

First & Last Row Of Selection

Dec 22, 2006

I am trying to format a range of cells based on a user selection. The user may select 8 rows (rows 20 - 27) or the might just select A20 to A27 or the may select A20 to C27 on all of the occasions I would like to find the 1st row and the last row of a selection. I have found the command

x = ActiveWindow.RangeSelection.Address

which I could try and extract the row numbers, but is there an easier way?

View 7 Replies View Related

Min Row And Max Row Of A Selection

Jan 24, 2007

I want to program a macro that reads out the min row number and max row number of a selection.

Lets say a user selected the Range A1:D4.
After executing the macro I want the following output:
The min row number of the selection is: 1
The max row number of the selction is: 4

View 9 Replies View Related

Read The Number And Check For The First Number With The 2nd Number

Dec 16, 2008

I have this set of number..

987654.

What im trying to do is this..I read this number and check for the first number with the 2nd number. If its greater then i will swap it. Im trying to do this so that my number can be rearranged as 456789.

View 8 Replies View Related

Move Selection One To The Right?

Mar 18, 2014

For a specific equation to work I need to know the distance whenever the acceleration is maximum.

The last part is a piece of cake, using MAX I get the desired results.

However, returning the distance is difficult.

What I'd like to do is return the value of the cell to the right of the maximum acceleration.

Using

=ADDRESS(MATCH(MAX(K2:K754);K2:K754;0);COLUMN(K2:K754))

I got the address of MAX, but that's about as far as I get.

View 9 Replies View Related

Sum Only Value Of List Selection?

Jul 17, 2009

I have a list of 4 customers and their individual revenue, which is summed up in cell E12.

What needs to be done to make Excel only sum revenue of say customer A and D in cell 12 upon selection of these through standard filter (see attached sample)?

View 2 Replies View Related

How To Print A Selection To PDF

Apr 9, 2013

I've attached a sheet. In this sheet I would like to print the selection....C9:17 and BL9:BW17. I would like to print it onto a PDF and I would like to expand it so it is very visible. Actually, it doesn't need to be PDF. If I could just print this selection and make it visible that would work. I had a problem using print selection because the columns are separated. I also tried fit to page but that makes it too small.Mod's note: file attachment removed at OP's request

View 2 Replies View Related

Summing With #DIV/0 In Selection

Oct 22, 2008

I'm having a problem summing after running arrays to analyse data. I have a list of four possible outcomes and I need sum up the totals, but sometimes there are no results, so there is #DIV/0 in the list I need to find the total for.

is there some code or some kind of formatting that would give me a total even if #DIV/0 is in the list?

View 3 Replies View Related

Testing A Selection

Nov 2, 2008

I've currently written the following code to make sure exactly 2 single cell selections are made. But now, I want to extend this code to make sure only cells from rows 5 & 7 are selected and the cells are in the same column.

View 2 Replies View Related

Using Trim On A Selection

Dec 18, 2008

I want to create a macro that will allow me to highlight a column and have the macro Trim every cell with text in the column, preferably putting the results over the original text. The column in question has text in every cell, until the column ends.

(That is, there are no numbers and no blanks until the data ends altogether.) I do not want to remove internal spaces in the text, just the leading and trailing ones.

I am sure this is fairly simple, but I'm not sure how to get it to look at every cell and then terminate properly.

View 8 Replies View Related

Not Allow Cell Selection ...

Jun 16, 2009

It is actually a form - much like Access. I have protected it so it can't be changed. They can click on a particular cell, but as soon as they try to enter anything it gives them a message and entry is not allowed.

Is it possible to even prevent them from selecting an individual cell, i.e., restricting them to only clicking the control buttons ? I realize this is a spreadsheet, other products would probably have worked better, etc., however it's kind of like the last minor hurdle before releasing it for general use. The world certainly will not stop if I can't - just thought I would check with the expers.

View 4 Replies View Related

About Range Selection

Jul 22, 2009

A question regarding "AboutRangeSelection" procedure on Chapter 11 in the **, Excel 2003 Power Programming with VBA by John Walkenbach.

View 3 Replies View Related

Check If Selection Changes To Anywhere Row 1

Jan 31, 2012

This works...

If Not Intersect(Target, Range("E1")) Is Nothing Then

However..

How do I change Range("E1") to any cell on row 1?

View 3 Replies View Related

Selection From Two Columns

Aug 8, 2013

I have two strings in two cells, both are dynamic values. I need to plot the values of any of the column that changed in the third cell either the two cells change its values.

View 5 Replies View Related

Crop To Selection

Apr 17, 2007

I want an easy way to crop to my selected cells, can this be done in Excel?

View 9 Replies View Related

Inverting A Selection

Sep 5, 2007

Is it possible to invert a selection? I have a heap of data that is scattered all over the place and I can to a Search and Find All to locate the data I want but I want to get rid of everything else, so can I invert the selection some how?

View 9 Replies View Related







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