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))
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!
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
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 Relatedi 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.
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).
I am trying to develop an IF formula based on 2 variables;
1. The value in column Q = 4
2. The value in column AA is between -10% and 10%
When both are true, it should return a value of "YES" (or "NO" when untrue). I am having trouble with the range part. It could be that the range spans negative and positive integers, but I'm not sure. Here is what I've got at the present time:
=IF(AND(OR(Q2=4),OR(AA2<0.10)),"YES",IF(AND(OR(Q2=4),OR(AA2>-0.10)),"YES","NO"))
I have two tables,
one table (TABLE1) contains the name and a mile point a
the other table (TABLE2) contains name, mile point 1, mile point 2, and other data
I want to be able to pull data from TABLE2, based on TABLE1's name and MP. I want to be able to select data from TABLE2 whose name and mile point's fit with the data from
TABLE1
Line
MP A
SYS
100
8
?
TABLE2
SYS
Line
MP1
MP2
DATA
A
100
0
5
BOB
A
100
5
10
KON
B
100
10
15
B
I want to be able to replace the ? from table1 with the correct SYS from table2 based on the MP given
I started with something like: =if(and(name=name,mpa=>mp1,mpa=<mp2)),vlookup...
The problem is this doesn't work because im comparing one to many.
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 Relatedhow to convert number into text (acutal formating)
Eg. : 150500
One Lac Fifty Thousand Five Hundred
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
Using web queries i get in a cell ie:
3¾
or
1¼
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?
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 RelatedI 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?
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 RelatedTrying 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.
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
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
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...
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.
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 Relatedis 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:
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.
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 RelatedI 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] ...........
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 RelatedI'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.
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.
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
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