Summing Across Unknown Range In Unknown Cell

Oct 26, 2009

I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!

I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.

Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.

Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?

Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).

Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!

View 14 Replies


ADVERTISEMENT

Define An Unknown Range

Aug 13, 2006

is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown

View 9 Replies View Related

Identifying An Unknown Range

Mar 11, 2007

I'm trying to set a range to a particular cell in Col D but I don't know the row number because this can vary depending on the amount of data entered. At present my code looks like this

Dim MyCount As Integer
Dim r1 As Range
'No of rows in Col D
MyCount = Range("A2:D2", Range("A2:D2").End(xlDown)).Rows.Count
Set r1 = Range(Cells(MyCount, 4))

The problem is with my line

Set r1 = Range(Cells(MyCount, 4))

View 2 Replies View Related

Autofill For Unknown Range In Different Sheet.

Jun 16, 2009

I need to autofill a range of formulas (A2:O2) but the actual range to fill up depends on the range of data on sheet1. I've managed to use:

View 4 Replies View Related

Modify An Unknown Range Of Cells

Jul 25, 2007

i have a list of countries in a column and have to go through this column ensuring they are in the correct format. I'm struggling to figure out how to iterate through each row of the column, test it's value and then change if necessary...

for example
column a --> column A(modified)
Usa --> USA
Ukraine - Mobile (Umc) --> Ukraine - Mobile (UMC)
Uk --> UK
Uruguay --> Uruguay

so values in column a becomes like those shown above.

View 9 Replies View Related

Range Of Cells Of Known Columns But Unknown Rows

Mar 19, 2008

I'm working to build a macro to help clean up data I download out of our company's online resources. I know that the data will run from column A to N but the number of items (and thus number of rows)will change. I need to sort all used rows except for the last one (which containes totals and I don't want to include in my sort). So right now I'm just trying to figure out how to select from A3:NX, where x is the row above the last used row.

What I've managed to find so far on the forums is

Offset("$A$3", 0, 0, (Match(9.99999999999999E+307, "N:N", 1) - 1), 14).Select

which I think should find the last row in N to have a number in it, minus 1. However I'm getting a compile error that function or sub are not defined. (seemingly refering to the Match, but I'm not sure).

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

Unknown Cell Entry

Sep 17, 2009

I am using a spreadsheet written by someone else that contains a type of cell entry I am not familiar with. These occur in several tables. The first 3 columns of each table contain numbers or basic formulas. These are followed by two columns in which every entry appears to be the following:

{=TABLE(,B33)}

Despite the same apparent "formula", the number displayed in each of these cells is different. If I click on the formula display box (to the right of the cell address box) to edit the "formula", the brackets disappear.

Can anyone tell me what sort of beast I am dealing with here?

View 7 Replies View Related

Unknown Cell Refernce In Formula

Nov 19, 2008

This may be an obvious one, I have received a spreadsheet with cell references or references to values that I am not familiar with, is anyone able to shed some light on how to interpret these references or at least find out where their source is within the spreadsheet?

View 3 Replies View Related

Assigning A Variable To An Unknown Cell

Dec 27, 2006

I'm attempting to create a macro that will look at the total in column (K) and send an e-mail to two different addresses, depending on the amount. If the amount is over $10,000 then one address (over@macro.com) if under, then the other (under@macro.com)

My main problem is that I never know what cell the total is going to be in.

I currently have all quotes going back to the person that sent in the request, no matter what the amount. Their e-mail is in the sheet.

So what I think I need to do, is find the last cell in column K with data and assign it a variable. If that variable is over $10,000 then I can send the e-mail to a hard coded address. If it's over, I just use my original code.

View 9 Replies View Related

Set Variable To Unknown Last Used Cell In Known Column

Jun 24, 2008

I'm trying to define the last row in a column which is determined from a find command. It's quite a large sub, and the rest is working properly, so I'll just post the (still rather large) troubling section.

'startingPoint is declared as a range. Destbook, reporttarget and FullControlName all exist.

'this sets StartingPoint to a one cell range in a sheet named from a custom class in a workbook named from variable destbook

Set StartingPoint = DestBook.Sheets(ReportTarget.Name).Cells.Find(what:=FullControlName, searchorder:=xlByColumns)

WriteCol = StartingPoint.Column

'this changes the range to include the whole column of the existing range
Set StartingPoint = Range(StartingPoint.EntireColumn.Address).....................

View 2 Replies View Related

Fill Conditional Cell Values Based On Unknown Number Of Rows

Dec 15, 2009

I have a set of data in column R, with an unknown number of rows that looks like this

Days Late
-28
150
3
16
41
.
.
.

and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:

View 4 Replies View Related

How To Use * With Unknown Text

Oct 6, 2008

i need the same result as in table bellow (yellow column)

I need tipe "Voice" if it word conteind in text

I used ( =IF(D8="VOICE*","voice","data") )

But it doesn`t work

View 9 Replies View Related

Unknown Characters

Apr 20, 2009

Using web queries i get in a cell ie:

or

The last part of these "numbers" (3/4, 1/2, 1/4) is text that i want to convert into numbers (0.75, 0.5, 0.25).
If i isolate this text in a cell (with the right() formula) the code() formula gives 63 as result for all the above texts.
I may solve the problen storing these texts (3/4, 1/2, 1/4) in separate cells (pre-fixed in some cells) and then for my new data (from query) do some search/find .
Is there a better way solving this using a different way-macro?

View 9 Replies View Related

Use LINEST In A Way In Which X Values Unknown?

Jun 23, 2014

is there a way to use LINEST in a way in which the x values are unknown and the y values are known? The opposite of how the function usually runs...?

View 2 Replies View Related

Unknown Popup (not A Dialog Box)

Dec 31, 2009

I'm modifying a template that originally shipped with Excel 2003 (I have not upgraded to 2007). In the template, there is a pop-up box (not a dialog box) that shows up when I'm on certain cells. I've attached an image of it. It's the yellow box containing the words "Company Information..." etc. I cannot find any way to remove it! It's not a comment, and selecting it doesn't allow you to edit it. What is it, and does anyone know how to remove it?

View 2 Replies View Related

SUM A Column With Unknown Length

Jun 26, 2012

Trying to sum up a column that has an unknown length. This is a canned Excel form that is saved within a software system and is used to create customized documents. Usually, I would enter the table name.field name in a cell to pull the data from various parts of the system.

In this case, that data resides in AA234 of my customized Excel sheet. When the user retrieves this doc from the system, the data could run several rows, starting from AA30 to AA255. So, in my "Total" cell, I've entered =SUM(AA1:AA255), so it will just add up whatever is in the column, regardless of how long it runs upon document generation. When I try to pull the document, I get "=SUM(#REF!)" in that field instead.

View 9 Replies View Related

Sum To Unknown Rows With Conditions

Apr 24, 2006

Need to sum through an unknown number of row generated by advanced filter at run-time

Formula at C16
Data starts at row 57 (from copy/paste - advance filter)
Last row unknown

Criteria to match in A16
Data to be evaluated in B57:B (row unknown)

Found this formula on microsoft.public.excel.misc
=SUM(A57:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
Which I modified formy start row

View 9 Replies View Related

Sum Across Unknown Number Of Worksheets

Sep 20, 2007

The colours are just to mark the ranges

As every month is different the number of sheets adding up to the Red sheets(week total)
will change and the same with the Blue sheet(month end total)

Is there a code I can run for this summing up to be done?

Yellow is where data is entered

Red is where the Yellows range sheets need to add up before it

The Blue is where all the Red range sheets need to add up

View 9 Replies View Related

Replace Unknown Characters

Oct 4, 2007

Is it possible to display a dialog box or msgbox that doesnt have an OK button ?

i.e I want a message that comes up on the screen that says "Links Updating...Please Wait" which then automatically changes to "Links Sucessfully Updated" on completion...I dont want the macro to be interrupted by the msgbox/dialog...

View 5 Replies View Related

Max And Min Unknown Malfunction With Multiple Criteria

Feb 14, 2014

I have a spreadsheet (attached) with historic weather data for a certain location.

Column A (Weather Day)
1-Jan
2-Jan
3-Jan
...
...
31-Dec

Column B (Record High)
28.4
39.2
37.4
...
...
39.2

Column C (Record Low)
-36.4
-38.2
-32.8
...
...
-36.4

I have a user defined/input "Entry Date" & "Exit Date"

The code to return the max is as follows: {=TEXT(MAX((Weather_Day>=Entry_Date)*(Weather_Day<=Exit_Date)*(Temp_Record_High)), "0.0") & " áµ’F"}
This code works perfectly fine for all input dates and returns the Record High between any Entry/Exit day.

The code to return the min is as follows: {=TEXT(MIN((Weather_Day>=Entry_Date)*(Weather_Day<=Exit_Date)*(Temp_Record_High)), "0.0") & " áµ’F"}
It's identical except MAX is now MIN. However, the returned MIN doesn't always return the correct value. It is either correct or returns 0.0.

Examples would be:

Entry Date: 4-20
Exit Date: 6-25
Both values return correctly

Entry Date: 4-21
Exit Date: 6-25
Record High returns correctly, Record Low = 0.0

Entry Date: 4-21
Exit Date: 11-7
Both values return correctly. However, with 4-21 as the Entry, 11-7 is the first Exit date to return a correct MIN value.

View 4 Replies View Related

Activating Another Open Workbook With Unknown Name

May 15, 2009

I have my VBA Codes set in a workbook (Production) that my supervisors open to calculate production. So when they open the workbook (Production) I have a button that they push that starts the code however I need to put something in that activates the other open workbook, The problem is that the name of the workbook that they run the code on can change.

View 2 Replies View Related

Open WB Located In An Unknown Path - VBA

Jun 17, 2009

is there a VBA way to determine an unknown path in which a KNOWN WB is located !?

Assume a WB named: 1X1.xls Located at C:TEMP

[In case the user uses more than one partition and/or more than one HD - it might alse be located at: D:TEMP

The known open command for a known file AND Path is:

View 14 Replies View Related

VBA Copying Unknown Amount Of Rows

Jul 15, 2009

I need to know the function to copy an unknown amount of rows. After a certain row it should copy everything.

So like after row A6 it copys every row below it with data in them then I want to paste it in another worksheet. I dont want to just do

Rows("6:65000").Select
Selection.Copy

If someone could point me to a good site that explains alot of useful VBA commands that would help me alot. Most of my problems come from just not knowing the full function names.

View 10 Replies View Related

SUMPRODUCT Use But With Unknown Number Of Rows?

Sep 15, 2012

I need to put a value on every line having "file:" in the second column. The value requested is a sum of the numbers in the forth column following this line until the next "file:" line.

I know it is not trivial, but sure it is possible.

57
file:
HIRES-~1
#VALUE!

58
208
1
1

[Code] ...........

View 9 Replies View Related

Select Column Of Unknown Length Using VBA

Jul 14, 2014

I have a column that begins in the same place all of the time (Cell C6) however the length of this column can vary. How can I copy the column using VBA?

View 2 Replies View Related

Unknown Characters In Compare Files

Aug 18, 2008

I have two access databases which export results to two excel files. I am trying to compair the two excel files (generated by access databases) to find the common data in the two files. I am using Vlookup function.

The data looks like this
File 1
Serial No Fault
40293 A
40294 B

File 2
Serial No Solution
40293 Procedure 1
40294 Procedure 2

The result should be
Serial No Fault Solution
40293 A Procedure 1
etc...

But the column serial no in file 1 has some unknown characters like 40293followed by a small square or a vertical line. The datasource for the access file that generates file 1 is actually a lotus notes database. I think thats what causing this. Is there a solution to clear these spurious characters?

The VLookup function works fine if I manually delete those characters.

View 9 Replies View Related

Combining An Unknown Number Of Cells Into One

Nov 4, 2009

I have the following setup,

Down column A I have a list of jobs, job 1 , job 2 etc
Down column b I have either a blank cell or other data (will be numbers but is irrelevant)
What I want is a formula for a cell that tells me which titles in column A have any data at all in column b. I need it to look like the following : Job 1, Job 2, Job 4
assuming b1 b2 and b4 have any contents and b3 and b5 and onwards are empty

I am seeing that concentate gives me close to what I want but the number of rows in column a and b change constantly and also may be above 30

View 9 Replies View Related

Sort Unknown Number Of Rows

Sep 30, 2009

I am working with 2 workbooks the first one "LH Enrollment" is used to enter information for new students being enrolled in the program. After the information is entered the macro is then copying the information to the second workbook "LH Children Records". The part I'm stuck on is that I now need to sort alphabetically by last name which is entered on Sheet 1, Column B of "LH Children Records". The number of columns will stay the same but the number of rows will change each time a new child is added. Eventually all this information gets copied over to several other workbooks, but I'm stuck trying to sort. I have attached both workbooks

View 5 Replies View Related

Accounting For Unknown Number Of Worksheets

Jun 28, 2006

I have a macro set up to copy and paste data from worksheets into worksheet 1. But the number of worksheets often changes. Is there a way to write this to include all worksheets even if the number changes?

Windows("0285 WORKING FILE 0406.XLS").Activate
Sheets(3).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R501C1"
ActiveSheet.Paste
Sheets(2).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R1001C1"
ActiveSheet.Paste

Sometimes I will receive this file and there will be additional tabs. I just never know how many.

I copy down 500 rows knowing there will never be more data than that. And for each worksheet I paste to sheet 1 I add 500 rows to not paste over other data.

I would like the computer to read it as "select last indexed worksheet, copy paste to index 1, repeat while selecting the left adjacent worksheet until you reach index 1.

For each new worksheet selected add 500 rows to the last pasted amount."

View 7 Replies View Related







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