So I found a macro that does something similar to what I need.
I tried changing the data range and input column but it doesn't work.
What the macro does is I type whatever name into the msg box and it will copy and paste all the rows that contains the name to a different sheet.
Here is the macro:
VB:
Sub test()Dim vl As String, res, sh, myrange As Range
Application.ScreenUpdating = False: vl = Application.InputBox("Enter value for Column B", Type:=2): If vl = "" Then Exit Sub
I have a spreadsheet that has multiples ranges. Some values are displayed as negative values. I would like to create a macro that allows me to first select the range/s, (maybe using a input box) I want to change and then display the negative values as positive values.
Would I need to create a separate macro to do the opposite (change the values that are positive to a negative value)?
The other macro I’m trying to create is a similar type. What I would like this macro to do is first select the range I want to change and then divide all the cells in the range by 100000
I currently have this beast of a code running in Excel 2013:
HTML Code: Sub OBTAIN_RAW_DATA_MACRO() Dim NextCol As Long
'Copy data Workbooks.Open Filename:= _
[Code] ........
However the issue lies in that once the data is refreshed, the slicers reading off the above raw data/pivots, automatically select all weeks, however this is not beneficial for the data being presented.
Is there anyway to have, once the Pivots have been updated, for the macro to then check and select current week range (beginning Monday) only?
I am writing a macro that will perform a loop operation to export files. I want the user to specify the path, and the macro will save each exported file to that folder.
I did a number of searches and I can't find exactly what I'm looking for.
I think I want to use ChDir after using CurDir. But I don't know how to code the macro so that it asks the user to specify the location.
I have a workbook that has a saved range. I created it with the Name Manager. See below:
[Code]....
When it looks like that the VBA works great and creates my pivots. But after I run my first macro to mess format my source data the range ends up looking like below:
I’m wondering if it possible to have a variable in a Formula. I was looking around, but non of the other examples I found was working. The code I was trying is:
Sub All() Dim lngDataRows As Long Dim lngDataRowsSum As Long ' add sum lngDataRowsSum = Range("A5").End(xlDown).Row Range("A" & lngDataRowsSum).Offset(1, 0).FormulaR1C1 = "Sum" Range("M" & lngDataRowsSum).Offset(1, 0).FormulaR1C1 = "=sum(M6:M & lngdatarowssum)" End Sub
Another code I tried was from this thread
Sub All() Dim lngRowsBottom As Long Dim lngRowsTop As Long ' add sum lngDataRowsSum = Range("A5").End(xlDown).Row Range("C10").formula = "=sum(R["&lngrowstop&"]C:R["&lngrowsbottom&"]C)" End Sub
The error message is: “Compile error: Expected: enf of statement” and the part “[C:R[“ is highlighted. Tried various ways to solve it but nothing was working. Does anyone of you know how to get it up and running?
I want to create a macro which asks some information like functions do. For example, I would like the macro to ask for some data to be filled by the user. For that purpose I thought about using the inputBox vba function. I wanna fill the field in the inputBox with a reference to a range from an excel sheet. So, I click on a cell and then I try to hold ctrl + down cursor to get all the range starting from that cell on until the last consecutive filled cell on the sheet. Nonetheless, the problem I have is that when I do ctrl+down cursor, I just do not get the reference I want, because it seems that the ctrl+down cursor combination does not work in the inputbox for filling a reference to a range of cells. Does anyone know some workaround to achieve this?
I am trying to create a code so that when the save button is hit, it puts the current date and time into a given cell before it saves. This way I will know the last time something was updated and saved.
If I have a formula that uses cells that are currently blank - and then run a macro that puts data into those cells - the original formula cell ranges change - even if I put $ within the ranges.
i.e.
Cell A1 has formula = sumproduct(e1:e10>10) Currently cells E1:E10 are empty
Run the macro
E1:E10 now has data in.
But formula in cell A1 now reads something like =sumproduct(E13:E23>10) or could read =sumproduct(Ref#>10)
The code below works fine, I can copy selected area into new sheet. However, I wish to custom change the Range("A1:AO164") value, for example, at cells(1,1) I key in A1, then cells(1.2) I key in A56, then this changes will reflect to the value in this macro to Range("A1:A56"). I tried using this way :
Dim A as string cells(1,1).text = a cells(1,2).text = b Range("a:b").Select
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.
Is there a way to change a line of vba code using an input box?
The current macro downloads a file from our intranet. However the filename changes each week. It is only the last portion of the filename that changes since this part is the date it is created. e.g filename010507.xls filename090507.xls
What I want to do is for an input box to pop up, the user then enter the date and this will then replace the existing filename in the code with the new date.
I need to input values rangeing from 0 to 5. When inputing I want to make the background colour change to a differant colour for each value. I can do it but only for data already in the spreadsheet.
Can i put into the cells B38-AF38, that would display the sum of B20:AF20 + B24:AF24 + B28:AF28 + B32:AF32 +B36:AF36 in a h:mm format.
When imputing the data into a cell in a h:mm format, I need it to display in that cell and formula bar the h:mm format without having to put an apostrophe in front of "h" or ":mm" value. Once complete, this form is going to go to other trainers to use on their computers and I dont think they would remember to keep putting an apostrophe before the data.
Example:
B20= 1:15 (1h15m) NOT 1:15:00 AM
B24= :30 (30min)
B28= :45 (45min)
B32= 1:45 (1h45m) NOT 1:45:00 AM
B38= 4:15 (4h15min)
I have attached a copy of the form for better clarification.
It doesnt seem to be complicated, but it's certainly a lot more than I thought.
I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain
Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...
Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...
I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...
EXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
Basically, I'm doing a recorded macro for work where I take an export and manipulate the data to show differences between sales from last year and this year. Also comparing this months projected sales to avg of last 6 months and also against last years this month.
The problem I'm running into is in automating the this month sales for mid-month exports. I can do it individually but I can't find a formula that will do it. Data is in one cell per month, so ex. 130 sales this month so far. I need to have it convert that to projected sales for total month based on what day it currently is.
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
I have been tasked with creating a scheduling system for my work. There are three groups of shifts that can be selected, Opens, Swings, and Closes. I have been asked to make the cells turn red if a user selects a sequence of shifts. We are trying to avoid having a person working three scenarios:
Close>Swing>Open Close>Off>Open Close>Open
If they select the shift that meet this scenario, then I need the cells to turn red. The complete list of shifts are on the "Data" tab and the the three scenarios have been inputted into the "Jan" tab in E13:G15.
I am using the code below to change the background of the cells in my worksheet based on what is in the cell. How can I modify it to accomplish the changes below and meet the criteria above?
I have a blank file and a data file for current year with formulas.
So I am trying to take a couple of tabs from current year data file and pasting them into the blank file.
My question when is do that all the formulas get referenced to the current years file. i want to keep the same formulas but reference the tabs on blank file.
Is there a quicker way to do this rather and going into all the cells and changing reference from current years files data to blank files data.
i have this excel sheet that am using as a monitoring device to track the login and log out patterns of a group of 20 agents. i am able to monitor their current state by using an external link to the local intranet and vlookup.
the status of agent can be either Logged In or Logged Out which populates in E6 to E25. the cells f6 to f25 again running vlookup shows the last login time of the agent.
what i'm looking for is this: every time the agent status changes from Logged In to Logged Out or visaversa, the corresponding blank cell in the row needs to populate the the time by value.
I put all my named ranges in a seperate worksheet in the workbook.
On a different sheet I have all my data with references to the named ranges. But what it gives me is the cell information from the sheet on which the named range is located and not on the worksheet where I actually need the calculations to be done.
How can I make the named range refer to the cells in the worksheet in which it sits?
Worksheet 1 --------------- 1 2 3 4
Worksheet 2 -------------- I name the function Red apples RedApples =CONCATENATE($A1&"Red Apples")
Question #1 How do I make the name reference in worksheet 2 refer to the cells located in the worksheet in which i use it. In other words, when I use RedApples in Worksheet 3, it gives me what is sitting in A1 in worksheet 2 instead of in A1 on Worksheet 1.
Question #2 How do I make the name reference in Worksheet 3 keep stepping down along Column A in Worksheet 1?
