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?
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.
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.
I copy and paste large amounts of data using macros. Excel leaves some kind of trace, placeholder or something in the cells that are blank on the original sheet. So all my math calculations are messed up on the destination sheet.
Using GoTo special blanks does not work to select these cells in mass. I know how to loop through all the cells one at a time and check to see if they are "" or have a length of zero and clear them, but these methods take forever.
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
I have a Macro that changes the background colour cells dependant on the value in another cell, all seems ok but when it runs it scrolls the page as it shades the cells and then exits leaving the sheet where it scrolled to.
Is there a way to get it to return to where it started or not to scroll?
In my worksheet there are ranges A3:C37, E3:E37, J3:K37 and P3:P37 that all contain text that I would like to automatically change to proper case once the user leaves any of the referenced cells.
I have tried various codes form this forum and searched for hours on the net for a solution to do this but no matter what I do/try nothing works (for long)
Another forum user did help me out with some code but there was an issue with column C, L & O (which are set as drop down lists) and when the code was put into the workbook these columns stopped working and froze the app.
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.
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")
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
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.
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.
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.
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:
I am copying and pasting from two different " timesheet" spreadsheets into a list. One of the timesheets has blank rows. I am attempting to sort the blank rows to the bottom after I paste the data, but every time I do, it either replaces the top row with "true" or deletes the headers,
Sub SortBlankRows() Dim rngCurrent As Range Dim c As Range Dim inUsedRow As Integer Set rngCurrent = Workbooks("Payroll Summary.xls").Worksheets(1).Range("A1:J1") inUsedRow = Workbooks("Payroll Summary.xls").Worksheets(1).Range("D65536").End(xlUp).Row rngCurrent = rngCurrent.Resize(inUsedRow) rngCurrent.Select Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("F1") _ , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal End Sub
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.
The goal of that formula was to return a Date/Time stamp that is fixed and doesn't change over time. The formula is intended to run in which ever cell that I select to enter it into. I named the formula:
=DS
What I am trying to do is create a macro that will run the formula using a hot key function. I want to be able to select a cell and hit CTRL D and have that above formula run in that cell. I have multiple sheets in my workbook and I need the macro to be able to run on any sheet in my workbook. Below is what I have:
Sub DateStamp() ' ' DateStamp Macro
[Code]....
I was trying to apply the macro to only a certain range of cells on any given sheet.
I recorded a Macro that would take a value of a particular cell and add that value to the selected cell in the same row. However, this macro just runs on one cell and I want it to run on the entire row.
This may seem like a trivial issue but I've been racking my brain for the last couple of days to figure it out.
Here is the VBA code of the Macro I recorded. It adds the cell value to another cell with formulas already in it as you will see below:
Sub SpreadingTest15() ' ' SpreadingTest15 Macro ' ' ActiveCell.FormulaR1C1 = _ "=IF(MarRF!RC9=""Trade"",IFERROR(IF(RC12=""BL"",RC13/12,INDEX('Variable Data'!R4C2:R16C17,MATCH('1410-Rev'!RC14,'Variable Data'!R4C2:R16C2,0),MATCH('1410-Rev'!R14C,'Variable Data'!R4C2:R4C17,0))*RC13),0),IF(RC14=R14C,RC13,0))+(RC35)" ActiveCell.Select End Sub
I've made a push button on the sheet. By selecting a couple of cells and then pressing this button I want to change the background color of the selected cells as well as add some text (same for all selected cells).
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
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..................
I am looking to run two separate macros. I have a project plan and I am looking to be able to select a button whereby on-click, a new row is created within a selected cell. However if the cell clicked is outside of a pre-determined range, then the task is automatically entered at the bottom of the plan. I would like for the copied row to go ABOVE the selected cell and have all of the same formatting as the row below (not the top - as is default in Excel).
The second is going to be very similar but will copy a task category (like a header item) and the first task (row) found below. It will also be copied from below and be inserted above the selected cell.Both macros will clear certain cells, whilst maintaining the contents of others, with formula contained. I.E. Columns C,D,E,H,I,J will be cleared.
I am looking for a macro or function (VBA) that will modifiy the value of the selected cell or cells. the code should support selection of one cell, a range or multiple ranges.
I envision the user making his range selection(s). Activating a function or clicking a button that would pop open a modal window. The user would have the option to either adjust the values by a % change (i.e. up or down 7%), or incremental change (i.e. up olr down 100 units). The function would overite the value in the cells.
Has anyone ever done something similar? Is it hard to program?
i m trying to set up a macro to convert a range of user-highlighted(selected) cells to 3 significant figures: for example, convert 0.135564 to 0.136
the equation i found elsewhere online: ROUND(xx,3-(1+INT(LOG10(ABS(xx))))). but i can't quite figue out how to apply the equation to a selected range of cells via a macro.
The concept is to have a user open a sheet that breaks down that current day into 15 minute blocks of time. Later, I will work to append that to an Access DB or to a master Excel sheet as I will have multiple engineers inputing times for multiple days. There are multiple paths for this....We don't have Access for everyone yet or I would look at that path now.
Right now I am trying to make the initial input screen easy to use so that they actually use it.
I want to allow the user to select multiple 15 minute blocks and then click a button that adds a specific value to each of those cells. For instance, they might choose 8 15 min blocks that are not necessarily blocked together (C5, C7:C10, C15, C20:C22) They would hit the "Project 1" button and "Project 1" would be inserted into each of those cells.
I have about (5) categories so I would simply have (5) buttons with the different inptu strings.
But how can I have a macro set the value for multiple selected cells? Ideally, they would not have to be blocked together but, if there is no other way, if I could have blocks of cells filled in all at once.
I am using Excel 2010.
only a few of us have Access but I will be the only one accessing the collected data. Besides, I would make Excel query the Access DB for more general use. I
How I can deselect the selected cells before ending the macro. Ihave a workbook containing about 40 sheets, and need to clear the same cells on 31 of them. I have attempted to define a name for the range, and actually got it to work once, however it ceased working on the second attempt, and I don’t know why. I have therefore gone back to the original code as posted below.
My questions are a) how can I deselect all the cells and b ) how can I use a defined name for the range so that I can use something like clear contents and not have to select the cells?
(I have also cleared all unlocked cells previously, but I have some unlocked cells in the other sheets I do not wish to clear).
Subnewmonth() ' newmonthMacro
IfMsgBox("This deletes all data, do you wish to continue?", vbYesNo) = vbNo Then Exit Sub Sheets(Array("1","2", "3", "4", "5", "6","7", "8", "9", "10", "11","12", "13", "14", "15", _ "16", "17","18", "19", "20", "21", "22","23", "24", "25")).Select Sheets("1").Activate Sheets(Array("26","27", "28", "29", "30","31")).Select Replace:=False
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
