I am trying to get the address of Current Region through a Function call. The function only returns the address of the cell, not current region. Isn't it possible to use Current Region within a function? It works for a Subroutine. I have attached the examples in a file.
View 5 RepliesHow can I use this code but tell it to select all but the right-most column? I have this piece of code...
View 4 Replies View RelatedI’m attempting designate the cell which will then determine the start of the current region, to be copied and pasted to another sheet. I’m receiving an “ERROR 1004” , Method Range of object_ Worksheet Failed.
View 4 Replies View RelatedI get an error 1004 with this
Sub SelectCurrentRegion()
Cells.Find(What:=InputBox("Enter the sheet name to delete"), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
Range("ActiveCell.Value").CurrentRegion.Select
End Sub
I can put Range("G2").CurrentRegion.Select and it works fine, but I need it to enter the value from the earlier find.
Probably a really simple solution to this one, I'm trying to create a macro that will jump to a location, select the current region and then create a range name for that region. However, the number of rows in the region will change each time the macro is run as the region is a result of an advanced filter from a huge database. This is what I have managed so far but it to no avail
Selection.CurrentRegion.Select
ActiveWorkbook.ActiveSheet.Names.Add Name:="mergea", RefersTo:=Selection.Address
Ultimately the data selected will become a list for a Word mail merge
I have an area in my spreadsheet (the current region surrounding cell G6) in which the height for each row should not exceed a specified limit (say, 150). It's okay if the row height is less than the limit - these rows should be left alone. I only want to resize those that exceed the defined row height limit.
Is there a simple way to reset the row height for rows whose height exceeds a specified maximum row height limit?
I have a sheet with a data in range A1:J9. I have a drop down list in each cell from A2:A100.
I'm trying to store all values in A1:J9 in an array using the property CurrentRegion.value like below
Code:
Arr=Range("A1").CurrentRegion.Value
The issue is that is taking as current region the range A1:J100, even if I don't have selected any value in the drop down list from A10:A100.
Is there a way to force CurrentRegion to select only values from A1:J9 ignoring the blanks dropdown list values or a similar way to load an array with values in a contiguous range?
I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
If not, how to copy, then paste?
I am trying to figure out the best way to plot this data -- For each region, I want to plot December 2006 snowfall against December 2007 snowfall. So starting on row 12 to row 41 (in the attached file). First challenge: the dates are not aligned because it is using the retail calendar where 12/3/2006 is compared against 12/2/2007. How can I plot the snow depth for the Northeast for example? I can't just use a dual axis chart because there are also dual dates...
View 3 Replies View RelatedI have the following
View 2 Replies View RelatedI have a dataset (20,000rows) with Grade, Region and Salary. I need to calculate the 25, 50, 70 and 90 percentiles against each concatenated Grade and Region.
how to count the region by date wise,
For Example: Below 2 columns there are four dates available,
i want to know the count for date 1/1/2010, how many UK?, IND?, US?.
As per the below format....
Date UK IND US
1/1/2010---
1/2/2010---
1/3/2010---
1/4/2010---
Date Region
1/1/2010UK
1/1/2010IND
1/1/2010UK
1/1/2010UK
1/1/2010IND
1/1/2010UK
1/1/2010UK
1/2/2010IND
1/2/2010UK
1/2/2010US
1/2/2010US
I am trying to Write a function that would add all the postal codes that contains a certain value. Overall I want the function to return the number of postsal codes that contains lets say L3T. the problem is that my postal codes are formated like L3T 6X5 - L4V 4X9 , etc. i need excel to only consider the 3 first digits/letters and return me the sum of all the L3T, L4V etc
View 6 Replies View RelatedI have a excel file with two tabs, one with raw data, with data ordered by geographic region.
I have another blank tab, where I've created a dropdown list of the geographic regions.
How do I move all the data from worksheet to the other based on that geographic region?
Retrieving the names of all places in excel sheet as per Google maps or visible in google maps for a selected District / State / Country.
View 9 Replies View RelatedI have series of data values like below. I have to find Maximum, Minimum values for each of these values.
9430
9822
10070
[Code].....
Trying to automate the period part of the impt function
To calculate current value of loan i have the below formula below with the 3 being the current period
=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)
What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.
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".
View 9 Replies View RelatedBasically, 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 want a macro to take the current time and if it is less than 09:00am, I want it to set a variable to be the current time on the previous day.
Basically, an operational day runs from 09:00am to 08:59am the following day.
08:45am on Saturday 13 July (for example) should be recorded as 08:45am on Friday 12 July.
I have the following data:
column a: column B:
1
7
9
25
I need a formula to make column B equal to the current month adding the day in column A. so that column B equal the following:
column a: column B:
1 09/1/2009
7 09/7/2009
9 09/9/2009
25 09/25/2009
All data is located within one book. I have two sheets with material codes in each sheet which include pricing (existing and current)
Sheet1 (has existing material codes plus existing pricing) Has about 1200 lines
Sheet2 (has current material codes plus current pricing), has about 36000 lines
I need to cross check if the material code (taken from sheet1) are still available in sheet2, and if they are, copy the current price back to sheet1. The current price needs to be pasted back into sheet1 (next to the existing price). If the material code doesn't exist (for whatever reason, in sheet2), the program needs to move onto the next line and leave the current price for that material code blank. The program should finish once all the lines in sheet1 are completed. I have attached a sample of what I'm trying to do,
how I can use the NOW() function without the date....just the time?
I need it so that if I format the cell and chage to general, it will show 0.xxxxx
rather than 39230.xxxxx
I am creating a formula that can give me current YTD & the same for Last YTD based on the selected reporting month and product ID.
In the file attached you can see in summary sheet that when I ever I choose the reporting month I got the sales for current month, last month, same month of previous year based on the product type, I use sumifs formula to get those data from the sheet called data. Now I failed to come up with a formula to get me the current YTD & last YTD sales based on product ID, If I say that I choose reporting month March 2013, I want excel to calculate for me the sum of Jan,Feb,Mar 2013 sales and also to give me the same but for year 2012 for each product ID, the data will be taken from the sheet name "Data".
I added a link below which contain the file as I couldn't upload it : [URL] .....
I would like to sum the column at the bottem but I get a circuler ref. error. I think this is because of the formulas but is there a way to sum based on the actual values in the column?
View 13 Replies View RelatedI am looking for 3 outputs from the attached report. Output report also attached.
1.Under the column "Created_date"(Column I), I am trying to count the number of rows which has current month in column L and result in output report file under "Risks Added" column.
2.Find and count the number of rows which has been modified this month by using "Modified_Date"(Column N) column and result in output report file under "Risks Updated" column.
3.Compare the column "Status_Change_Date" which is updated this month and "status". If that row has the Status column as 'Resolved' and the 'modified date' column has current month, please give the count of those rows in the output report file under "Risks Mitigated" column.
Below is the basic vba code for copying the rows which has this month created date
[Code] .....
Attached Files:
Sample Report.xlsx‎
Output Report.xlsx‎
I am trying to determine the currently active row on another sheet; that is, the row that would be active if I switched to that sheet. Is there any way of doing that without using the ActiveWindow object? It seems clumsy to have to change the active window to the one I am interested in, get the information I need and then change it back again; but I haven't been able to find a property that I can just retrieve off the sheet object. Even so, I am using the following code and it isn't fetching the value from the target sheet anyway. It is returning the current row from the sheet that is calling the function.
View 3 Replies View RelatedI'm try to do a sum function on certain a column and the range will vary each time, the column will also vary.
I have the numbers of the rows but I also have the numbers of the columns, how do I get the letter of the column so I can use it in =SUM?
i did cur_col = ActiveCell.Column but I need it to return a number.
I was wondering how you get the current year (yy)?
View 9 Replies View Relatedhow to get the current time with NOW(). The thing is once that is entered I dont want it to change.
current format of worksheet as follows
Enter race number in column A as racer goes past - Column B is time stamped with current time. The problem being time keeps updating to current time whenever another entry is made.