Change Range Selection With A Variable

Feb 19, 2009

How do I change the selection of a range with a variable, and not a hardcoded number in XL2003? I have to update a set of spreadsheets every month, and it's a hassle to have to constantly open my pivot table worksheet, copy, open the summary worksheet, paste ... etc. etc. etc. The code below is my attempt at creating a ComboBox with "January, February, March, etc." and every time I select a particular month it will automatically copy data from my pivot table worksheet into my new summary worksheet in the correct column. The range of data from my pivot table worksheet will never change, so I have no problem hardcoding that in, but based on which month is selected will alter which column the data goes in in the new worksheet.

I want to be able to write code for one month (say January) and then when I want to use a different month (say February) I can just change the column number and call up the originial January code.

Private Sub ComboBox1_Change()
Dim ColNum ' This is the variable I want to change based on which month is called
If ComboBox1.Value = "January" Then
ColNum = 1 ' Column number for Column A- Where my January column is
Elseif ComboBox1.Value = "February" Then
ColNum = 2 ' Column number for Column B- Where my February column is
' etc. etc. for each month
End If
Call January
End Sub

Private Sub January()
' Just a quick msgbox to make sure the previous macro is calling this one
MsgBox "Is this macro running?", vbQuestion + vbYesNo, "Check"...........................

View 9 Replies


ADVERTISEMENT

Range Selection With Variable

Jan 27, 2010

I am using Selection.ClearContents command to clear data from column F and G. I know the last row as 230 but the starting row is the first empty cell which I find using Do While loop in range F31 to F230. The variable that stores the first empty cell number is 'r'.

Range("F &r:G230").Select
Selection.ClearContents

View 3 Replies View Related

Range Selection From Variable Start Point

Dec 31, 2008

I'm trying to write a bit of code that will allow me to clear some sheets. One of the sheets has formula for x number of rows, and these need to be left intact. Under these forumla there is an area where data is pasted, and this needs to be cleared becfore starting the process.

Colum A is blank, apart from cell A1 (the column heading) and another cell (variable row) with the value 'Paste here'. I've used this variable cell in other macros on the sheet as a marker, so need this to stay. I was going to use this code below, but I don't know how to tell it to select from the current active cell to the bottom of the spreadsheet.

View 5 Replies View Related

Determine Maximum / Minimum Value And Additional Count From Variable Range Selection

Mar 25, 2014

I have been trying to determine the maximum/minimum value and additional count from a variable range which then I can use to subtract the first data of the range; I have been able to do that for a fixed range but not a variable one.

I have Column A with random positive numbers.

I have Column B with random negative numbers.

I have Column C with random numbers.

I have Column D with random numbers.

I have Column E with random 0’s and 1’s.

I have a set of 1600 cells of numerical data on each of the columns and there will be times when the random 0’s and 1’s from Column D will have appear repeatedly before changing to the opposite number, fx, I will get 7 nr. 1s before I get a 0 (zero).

I have been able to obtain the values in 2 cells within a fixed range:

If the last 8 cells in Column D have been “1” then I do:
F1=MAX(A1:A8)
G1=INDEX(C1:C8;MATCH(9.99999999999999E+307;C1:C8)) ---(Which provides the first number of the range in Column C after Column D has changed from 0 in D9 to 1 in D8)
H1=(F1-G1)

If the next 3 cells in Column D have been “0” then I do:
F9=MIN(B9:B11)
G9=INDEX(C9:C11;MATCH(9.99999999999999E+307;C9:C11)) ---(Which provides the first number of this range after Column D has changed from 1 in D12 to 0 in D11)
H9=(G1-F1)

Additional to this, the COUNT has also been challenging since I want to obtain in Column I, the COUNT of repeated 1’s from each range of 1’s in Column D; and also in Column J the same but for 0’s.

Fx:I18 (size of the range of 1’s originated from D1:D8)
J93 (size of the range of 0’s which consist to be the next range before 1’s were originated)
I12X (size of the range of 1’s which consist to be the next range before changed to 0’s)

However, that only works for a fixed range and continuous updates are made.

Book1.xlsx

View 2 Replies View Related

Variable Column Range Based On Current Selection For Sorting Columns

May 16, 2014

The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)

View 2 Replies View Related

Save Average Of A Variable Selection To A Variable

Dec 9, 2008

I have looked at many different examples of uses of the average function but I haven't found any examples of what I need it to do. Here is the code I am trying to use, but I am getting some errors.

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

When Change The Worksheet Selection Change Event Nothing Is Happenning

Jul 21, 2009

Attached is book in which, when a choice is selected from Drop Down list in ColumnF the macro has to do the need.

When the macro was written it was working well. But when I tried to change it as a Worksheet_SelectionChange event nothing is happenning even though a choice is selected from drop down list.

View 4 Replies View Related

Variable For Row / Column Selection

Sep 13, 2007

I need to create a macro that clears the contents of a range of cells. However, that range of cells is variable.

I know my starting cell (C9) and I know the selection goes over to column Z every time but the number of rows I want to clear after that may change.

Also, whatever that first cleared range is, I would need to skip the row below it then clear the same number of rows below that. (not entire row of course, just the ones from column C to column Z)

View 9 Replies View Related

Variable Selection Delete

Apr 5, 2007

I have a macro that prints out schedule information for my production lines. I recorded the macro and made a few changes. What it does is print out the page header, delete about a dozen lines and then print the remainder of the schedule. Often the the remainder of the schedule is not all relevant data as production has completed orders. What i want to do is, using an input box, ask the user what worksheet row he wants the body of the schedule to start printing from. Here is what i have now.

Sub auto_open()
Workbooks.Open Filename:="S:BI-K11.xls", UpdateLinks:=3
Application.WindowState = xlMaximized
ActiveWindow.SmallScroll Down:=-3
Rows("7:13").Select
Selection.Delete Shift:=xlUp
Range("A1:O26").Select
Selection.PrintOut Copies:=1, Collate:=True
roww = InputBox("Rows")
Sheet1.Range("A1").Resize(roww + 10, 2).PrintOut

Workbooks.Open Filename:="S:BI-K21.xls"
Rows("7:19").Select...............................

is where i would like the number 13 to represent the input box variable so that the rows to be deleted would be from 7 to "last row to be deleted - 1"

View 8 Replies View Related

Making Variable Selection From Cells

Oct 13, 2011

I have a question about making a variable selection.

In cell X25 is written "B9" and in cell Y25 is written "M39".

Now i want to make a selection by a macro from cell B9 to cell M39.

View 1 Replies View Related

Variable Selection To Return Highest Value

Dec 12, 2011

If the weight selected is between the ranges e.g 1.5kg for England so that it returns the higher value 10.69. At the moment if I put in the vlookup formula it returns the lower value of 9.60.

Weight KGEnglandScotlandWales0.59.109.109.101.09.609.109.702.010.6910.6910.693.012.2812.2812.28

View 4 Replies View Related

Put Print Dialog Box Selection Into A Variable

Jan 16, 2012

Is there a way to have the print dialog box come up, make a selection, and put that selection into a variable?

Ex:

Code:

Application.Dialogs(xlDialogPrint).Show

brings up the print dialog. I need to be able to select the printer and have the selected printer put into a variable in my vba code.

I have tried Ex: w=Application.Dialogs(xlDialogPrint).Show, but all I get is w=true

The reason I need it is because I will be printing via vba code, but the printer may vary for different print jobs.

View 6 Replies View Related

Random Option Selection Based On Variable Input

Jul 23, 2009

I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.

It is probably easier to look at the attached sheet and the previous discussion to see what I mean.

Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html

View 2 Replies View Related

Hide Rows With Variable Text Based On Dropdown Selection

Jun 11, 2014

I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:

A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"

This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"

I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("A2") = "MOT/Service" Then
ActiveSheet.Columns("A:P").EntireColumn.Hidden = False
ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True
ElseIf Range("A2") = "Contact Details" Then

[Code]....

But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.

View 1 Replies View Related

Change Selection With VBA

Nov 27, 2008

My user wants to change it so that, when the word "Triang" is selected from a dropdown box on each line the formatting goes back to none......or ClearFormats.

Can this be done with a Worksheet_selectionChange or similar.
I can't even get my head around this at the moment.
What the user is saying is, if the Formats haven't been cleared on certain lines, the user of the sheet hasn't addressed the needs for that particular line.

For r = lrow To 15 Step -1
If Range("M" & r).Value "" = True Then
Range("N" & r).Value = "Triang"
Range("N" & r).Interior.Color = vbRed
Range("V" & r).Value = "Triang"
Range("V" & r).Interior.Color = vbRed
End If
Next r

View 9 Replies View Related

Selection Change

Jan 16, 2007

I'm trying to write some code where if the user hits the Enter key on a particular cell an input box pops up. At the moment I've got some code that activates the input box whenever the user selects or scrolls over the cell. The problem is that the cell is a protected cell (as it has a formula in it) so double-clicking isn't an option.....

View 8 Replies View Related

String Variable To Call Defined Range Variable

Nov 10, 2006

Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

View 5 Replies View Related

More Than One Worksheet Selection Change

Mar 14, 2007

The following macro will put a checkmark in the range of A2:B100. I would like to use the current macro along with two or more other Worksheet_SelectionChange. One of the SelectionChange to put "Williamson" in the range of L2:L100 and another SelectionChange to put "Michaelson" in the range of M2:M100? Is it possible to use more than one Worksheet_SelectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "A2:B100" '

View 9 Replies View Related

Sheet Selection Change

Nov 13, 2008

I am getting US formatted dates with this procedure, I require UK dates, but I don't understand why its giving me US format

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If UCase(Sh.Name) "ALL BILLS" Then Exit Sub
With Target
If .Row > 5 And .Column = 19 Then
If Cells(.Row, "B") = "" Then
Else
Application.EnableEvents = False
Cells(.Row, "S") = "CLOSED"
Cells(.Row, "E") = Format(Date, "DD/MM/YYYY")
Application.EnableEvents = True
Range("E:E").EntireColumn.AutoFit
Range("S:S").EntireColumn.AutoFit
End If
End If
End With
End Sub

View 9 Replies View Related

Change In Formula With ComboBox Selection

May 8, 2009

In this file, I have a Work Summary worksheet in which I'm extracting some data from the other worksheets to "summarize" all jobs into one nice work summary log On Basis of Average of data from 3 other different sheets A,B,C

The following form works great to if onyl formula calling is applied

=AVERAGE(INDIRECT("'"&$C6&"'!c3:c7"))

C6 = Worksheet name or Worksheet tab name ....

View 8 Replies View Related

Keep Selection In Column But Change To 2 Rows Less

Dec 13, 2009

how to Keep Selection in Column But Change to 2 rows less. I have the following code that works.

View 2 Replies View Related

How To Activate Selection Change Command

Oct 21, 2011

I am having some difficulty working out how to activate the SelectionChange command if the user changes a cell in a particular column. Based on the fact that the column number may change the constant in this column will be that on row 7 the name will be "Fund Size".

Therefore my question is how do I get VBA to run my code if a user changes a cell in the "Fund Size" column...and underneath the "Fund Size" header (i.e. row 8 or greater).

View 2 Replies View Related

Toggles Selection Change Events

Jul 1, 2009

I'm trying to come up with some code that will allow a user to mouse click a cell to cut it, then mouse click another cell to paste it, then it toggles back to mouse click a cell to cut it. It would be a 2 step process for the user - select cut then select paste.

View 9 Replies View Related

Change Cell Selection Color

Jan 11, 2007

I just loaded 2007, and among the things driving me nuts is I can't figure out how to change the color of highlighted cells that I have selected. The default color is so close to the normal background color(White) that it is difficult for me to distinguish between selected cells and unselected cells.

I've tried the built in help but couldn't seem to search for the right phrase to bring up a relevant answer. I've had the same problem when trying google and Microsoft's office 2007 help site. I also searched the forum before posting and came up blank.

View 9 Replies View Related

Autofill With Range That Is Column Variable And Row Variable

Apr 4, 2008

I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this

View 9 Replies View Related

Change Row Color Based On Drop Down Selection?

Apr 2, 2013

How do I change the color of a row, based on the users selection from a drop down box in that row? I have four driving types in my drop down , Towing - Town Driving - All highway - Mixed. I would like each driving type, if selected, to change the color of the entire Row of Data to a different color. I have been able to use conditional formating to change the drop down cell itself, but that is it.

View 1 Replies View Related

Change Combobox To Multi Selection Tickbox

Jun 18, 2014

I have attached a work book.

At the moment when you click the macro botton on the sheets a calender shows and you can select the inspection done tab, this gives you a list to select witch then updates the selected task in the sheet.

How can i get this to allow single or multiple sections so that if more than one task is completed on the same date they could be updated all at once.

final1.xlsm‎

View 4 Replies View Related

Screen Flickers On Selection Change Event?

Mar 9, 2012

My screen flickers when I use the following code.

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim WTH As Long
If Target.Row = 19 Then
WTH = 1
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
If I comment out the code, my screen doesn't flicker.

In the "Tmp = 1" line I was using the special cells method to color constants red (without selecting anything). When I use that code instead of the "Tmp=1" line, it takes about a second to change cells, and I see the "4 Processors Calculating Message" in Excel when the code fires--even if it's not in row 19.

View 6 Replies View Related

Worksheet Selection Change - Multiple Checkboxes

Apr 24, 2013

I have an excel sheet where I can enter a text into A4, when this is valid a checkbox1 will appear if there is no value then the checkbox is invisible.

I have used a macro 'worksheet selection change' to do this but unfortunately I can only use this once in a sheet. I need to do this for upto 10 checkboxs that corrospond sequentially with text starting at A4 for checkbox1, A5 for checkbox2 etc....

The routine I have used for one check box is below. Can this be changed for multiple checkboxes?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A4") = isblank Then
CheckBox1.Visible = False
Else
CheckBox1.Visible = True
End If
End Sub

View 7 Replies View Related







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