Use Current Cell And Column As Range Without Entering It Manually?

Jan 6, 2013

I have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:

VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range

[Code]......

View 2 Replies


ADVERTISEMENT

Move Data From Multiple Worksheets To Single Sheet Without Manually Entering Each Cell Id

Feb 5, 2014

I have a number of worksheets and need to move data from the same cells of each work sheet to a summary worksheet. I manually entered the cell id's for the first sheet.

Example =Jan!M6, =Jan!N7, =Jan!O9 etc.

I know I should be able to automatically enter these same cells for the remainder of my worksheets with out manually going in and typing each one (there are a lot of entries.) I just can not remember how to do it. The next sheet is named "Feb" and "Mar" etc.

View 5 Replies View Related

Entering Current Folder Into A Cell

Oct 29, 2013

I have been looking for a way to enter the folder where the excel file is located into a cell.

I have seen you can add the whole path (=CELL("filename") ), however I am only wanting the 'last' folder it is in. For Example:

C:docsfolderdataClass D4John WayneProfile.xlsx

Would enter "John Wayne" into cell. The cell is namely the "name" cell.

I work in a school and a teacher is wanting a couple of different excel files for each student (600 students) I am hoping that I can put the files in a folder with the students name as the folder. That way at least its only 600 names and not 600x7! The other details are similar on a lot of the files such as class (this is also one of the folders in the path).

View 3 Replies View Related

Entering Finance Equations Into Excel Manually?

Dec 26, 2013

I am about to take a corporate finance course. The textbook uses a ton of basic (complex to me) equations to calculate interest rates, bond premiums, dividend reinvestment scenarios, etc.

I know that Excel has a bunch of specific functions to calculate interest, NPV, etc. but I am NOT interested in using those because I would like to have Excel "do the math" once I enter the formulas (or parts of formulas) correctly.

how to enter the equations below into Excel without using built-in formulas (I'm also not looking to use the Equation editor because I actually to calculate the answers, not just have the visual look/feel of the equation).

1R2 = [(1 + .0194)(1 + .03)]1/2 - 1

If done correctly, 1R2 in the above should equal 2.47%. I am totally fine doing parts of this in separate cells if necessary.

And something more complicated like the below. I wrote out the word "delta" because I don't know how to create the "triangle" symbol for delta on this forum post. According to the text, for securities with semiannual receipt (compounding) of interest, it is represented as:

-D = (delta P / P) / ((delta r sub b) / (1 + (r sub b)/2)))

Or in English, I believe that can be expressed as "negative D equals the quotient of delta P divided by P (in the numerator) divided by the following: delta r sub b divided by the quantity 1 plus r sub b divided by 2"

And finally

V sub b = 1000 / [1 + (.10/2)]2(4) + 50 times the following compound bracketed expression [1 - (1 / [1 + (.10/2)]2(4)] divided by (.10/2)]

View 3 Replies View Related

Column Of Dates Not Recognized Unless Manually Select Each Cell And Press Enter

Apr 12, 2013

I have a column of dates that are not being recognized as dates unless if I manually select each cell and press enter. For example the cell value is "Jul/13' and isn't recognized as "01/06/2013" until I select it and hit enter. How can I get around this?

View 8 Replies View Related

Select Range Of Current Column To Last Column In Sheet

Jan 30, 2012

I'm trying to make the below two column selections into a selected range to clear out formulas/values in the range.

Current code segments:

ActiveCell.EntireColumn.Select
Columns(ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column).EntireColumn.Select

Is there a way to combine the two code segments into one selected range?

View 6 Replies View Related

Select Column Or Range Based On Current Time

Nov 18, 2013

setup:

Columns: c)6:00 AM, d)6:15 AM, e)6:30 AM
Rows: 4)tech1 Phone, 5)Tech2 Phone, 6)Tech3 Phone

What I am tying to do is to select the column that matches the time, for instance anything before 6 AM would select Column C, betweeb 6 and 6:14 still selects column C, 6:15 to 6:29 selects column D, Etc

View 2 Replies View Related

VBA Code To Set Unknown Range Based On Current Date In 1 Column

Jul 21, 2009

i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).

View 4 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

How To Delete Data Particular Range Manually

Mar 26, 2014

I have excel sheet it contains 200000 records.I want to delete records from 50000 to 200000.Is there any shortcut to delete data.

View 2 Replies View Related

For Each Cell In Range Put Current Row Into String

Sep 15, 2014

In the code below how do I get the Current ROW number the "For Each Cell in Rng" is processing

I tried "cRow = rng.row" but it's only showing the starting row not the current row of CELL

VB:

Sub Process Range()
Dim startCol As String
Dim startRow As Long
Dim lastRow As Long
Dim lastCol As Long
Dim myCol As String

[Code]...

View 2 Replies View Related

Manually Select A Range During A Running Userform

Sep 10, 2009

I am trying to sum a dynamic range of values, clear the range (ie. clear the numbers on the spreadsheet), then place the sum of all the numbers in the last cell in the user selected range. (essentially adding all values selected, placing final sum in the last row of the range, and leaving no trace of performing the routine)

I want the range to be selected during the userform running (using showmodal = false). I can sum the selection and write the value to a cell, but I can NOT write the cell to the last value in the selection.

View 7 Replies View Related

Adjust Data Range Without Manually Filling Down

Dec 17, 2008

The below formula is in Col F6, how do i get this formula to adjust to data range without manually filling down, my data changes everyday therefore i need the formula to auto fill according to data range.

=IF(ISBLANK(E6),"",Summary!$I$2-E6)

View 9 Replies View Related

Populate Cell With Current Time When Specified Range Changes

Jun 2, 2007

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.

View 9 Replies View Related

Macro To Find Sum After Manually Filtering Column?

Jun 6, 2014

I am currently writing a macro that does a variety of things, one of these being that it automatically sums up columns I,J and K. The dilemma I have is that I need to be able to see the sum of the data in a cell at the bottom of the column, after manually filtering it according to what I want to see.

View 4 Replies View Related

Display Column Number Without Actually Manually Counting Across?

Sep 17, 2012

Is there any way i can display the columb number without actually manually counting across.The columbs are A to Z,AA to AZ,BA to BZ,CA to CZ & DA to DN....about 120 columbs.Is there a way to have the columb number diplayed by just placing the mouse cursor on the columb letter (not press though as it will select the whole columb..going to use for VLookup function

View 3 Replies View Related

A Column Of Cells Where Manually Enter In A Date

Jan 4, 2010

I have a sheet where I have a column of cells where I manually enter in a date. the format is d-mmm-yy. Before the end of the year rolled around I was able to enter in say only 12-31 and it would automatically change that to 12-dec-09, now when I do that it spits out 12-dec-10. It's not a big deal to type in the extra -09 to get it right but was wondering if there was a quick/easy fix to get it to know that I don't want a date in the future?

All dates I type in will be in the recent past...never more than 3 months or so old and never later than today's date.

View 9 Replies View Related

Find Last Number In Row / Add 1 To Current Cell (if Another Cell In That Column Has Text)

May 23, 2014

file storage

1. Look at the "Days so far" section

2. Ignore cell C1

3. All the other cells in that row are shaded blue. Look at these.

4. Look at the "Volunteer" row - all the cells in that row are shaded blue. Look at these.

5. Wherever a name - any name - appears in the "Volunteer" row, 1 is added to the previous number in the "Days so far" section and the result is displayed in cell from the "Days so far" row above that Volunteer's name.

6. For example, we begin in C1 with a count of 12 days so far - this was manually entered

7. In cell H5, we see Henry has volunteered 1 day. The total no. of days so far should now be 12+1. Therefore, the number in H1 should be 13.

8. In cell G47, we see Joseph has volunteered 1 day. The total number of days volunteered BEFORE Joseph volunteered is 15 (see cell D43). But now, with Joseph volunteering 1 day, the total no. of days so far should now be 15+1. Therefore, the number in G43 should be 16.

9. what formula l must put in the "Days so far" row (excluding cell C1, which is manually input) to give me the "should be" results predicted in that row? I'm guessing it will be a formula which looks at each row fragment of the "Days so far" row, row by row, right up to the previous cell in that row, all within one formula.

P.S. I just want to leave the "Days so far" row blank, for any columns where there are no volunteers in the "Volunteer" row, so please don't give a formula which inserts zero for days with no volunteer, and then sums the cumulative total.

View 5 Replies View Related

Autofit Only Current Cell Column To That Cell Width

Dec 15, 2011

When I'm moving around in an Excel sheet, I want to have a keyboard shortcut that Autofits only the column I am "in", and only to the width of the cell I'm "in". So I can arrow-key into a cell, hit ctrl-shift-W or whatever, and the column will autofit.

I try Cells.EntireColumn.AutoFit but that's not what I want, it autofits every column. Sometimes this is not appropriate and I want to do it manually for the columns I want auto-fit.

I guess the more general question is how do I write VBA code for the column I am in and not every column or a specific column - not Columns("A:I") or whatever, it's going to be a different column every time depending on what cell I'm editing.

View 2 Replies View Related

Selecting A Range Of Cells Relative To The Current Cell

Oct 31, 2006

I would like to select a range of cells relative to the current cell and move them to the right two cells. Basically, if I were in cell A1 I want to be able to have a macro select A1 - A8 and move them over by two cells leaving A1 and A2 blank.

Excel 2003

View 9 Replies View Related

Find Column A Cell Value If It Exists For Current Cursor Row Position

Dec 1, 2012

trying to find the value of column A (date field) if it exists on the row that the cursor is presently on. I'm trying to check for the value before performing the next function to make sure the user is on a valid row. Cursor could be on any column A to F. Columns are always 6 but the rows can vary.

View 7 Replies View Related

Current Month: Column B Equal To The Current Month Adding The Day In Column A

Sep 28, 2009

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

View 3 Replies View Related

VBA Code To Populate A Cell Automatically Whenever Another Cell Is Filled In Manually?

Mar 29, 2014

I have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.

When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.

I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.

View 8 Replies View Related

Conditionally Format Cell Based On Another Cell That Is Manually Colored?

Nov 24, 2013

let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?

View 7 Replies View Related

Entering Data In Next Row On Same Column

Sep 24, 2007

I have the bellow code, I'd like enter the input on the next available row on column "A" instead than on "A2"



Dim strResponse1 As String
strResponse1 = InputBox("First Name", "Hey you !")
If strResponse1 = "" Then
MsgBox "You have chosen not to participate!", vbInformation, "What happened?"
Exit Sub
End If
Range("A2").Value = strResponse1

View 9 Replies View Related

Sum Data By Entering Date Range

Feb 2, 2014

I have a spreadsheet that records water consumption in L/s at 5 minute intervals. I've attached an example of the data collected during January 2012.

Is there any formula that will calculate the total amount of water consumed between a user-entered date range? For example:

From: 01/01/12 00:00 (User enters 'from date and time' in an allocated cell)
To: 20/01/12 23:55 (User enters 'to date and time' in an allocated cell)
Consumption: ______ (Formula generated result in Litres)

View 3 Replies View Related

Colour Cells After Entering Range?

Sep 3, 2013

I want the user of my excel sheet to define a certain area For example range 1:

In cell A1 he enters = 5
In cell B1 he enters 10

Beginning with C1 excel should colour the cells C1, D1,...,H1, because the area has the lenght of 6. Colour could be yellow.

For range 2:
The same in the cells A2, B2, ...

View 9 Replies View Related

Put Manually Formula In Specific Cell?

Jun 5, 2014

I would like to be able to put manually a formula in a specific cell (like a "requested date" cell) and then have a table that use this date for the headers and calculate +1 year for each columns.

For exemple:

=2012-01-01
=2013-01-01
=2014-01-01
=2015-01-01

requested date:
2012-01-01

Would you know a way to easily do it? when I try to do it manually (+365) it doesn't consider leap years. I thought about a =DATE formula but the requested date have to be manually entered...

View 3 Replies View Related

Looking Up Two Values From Two Sheets And Entering Third Value In Column?

Jul 22, 2014

I have two tabs BIN QTY and ORDERS. On the ORDERS tab Column A is the order number and is duplicated several times depending how many line items are on the order. The second key is Column H which is the part number which is unique per sales order. I'm trying to use these two values to look up or find the matches on the Bin Qty tab.

-The data in Column A (order tab) gets compared to Column B (BIN QTY tab)
-The second match is the Item no. Data Column H (order tab) to Column C in (BIN QTY tab).

-Once both values match it needs to pull the value from column G (BIN QTY tab) and enter it in column N (ORDERS tab).

My example is highlighted in blue with the result in green. I've tried several different index and match formulas with no luck. my Last was

=IFERROR(INDEX('BIN QTY'!$G$1:$G$7957,MATCH(ORDERS!$A1,'BIN QTY'!$B$1:$B$7957,0),
MATCH($H2,'BIN QTY'!$C$1:$C$7957,0)),0)
MY ORIGINAL SHEET IS MUCH LONGER

View 3 Replies View Related

Move Contents Of Manually Selected Cell

Aug 22, 2009

I have an array, letís say B1:D20. I would like to be able to manually select any cell within this array and then run a macro which will place the contents of the selected cell into the cell in column A in the same row as my selection.

View 3 Replies View Related







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