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
ADVERTISEMENT
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
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
View Related
Nov 1, 2008
how to set up an equation for an unknown variable in a formula equal to 0 (an Even-Money Probability Formula for Risk Arbitrage assessments).
The formula is as follows, where I am attempting to solve for Pe:
(Pe*EP)+(P2*EL)=0
Pe=even money probability (placed in column B1 in Excel)
EP=expected profit if deal closes (placed in column B2 in Excel)
P2=probability of deal breaking up (=1-Pe) (placed in column B3 in Excel)
EL=expected loss if deal breaks up (placed in column B4 in Excel)
B1*B2)+(B3*B4)=0
So, once again, I am attempting to solve for Pe, or column B1
B1=(-(1-B1)*B4)/B2
say, EP (or B2) =$2.00 and EL (or B4)=-$4.00
B1=(-(1-B1)*-4)/2
B1=2*(1-B1)
View 2 Replies
View Related
Jun 4, 2013
I need to store a known index, unknown value in a variable so I can increase the value and use it in a database. It is used as a version number for a part and the versions go like: "00" -> "AA" -> "AB" -> .... -> "AZ" -> "BA"... etc.
VB:
If tool.Worksheets("TRB Database").Cells(A, "R").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(7, "C").Value Or
tool.Worksheets("TRB Database").Cells(A, "AA").Value <>
ThisWorkbook.Worksheets("Design Calculator, Q").Cells(5, "K").Value Or tool.Worksheets("TRB Database")
[Code] ....
That is a part of the increase and when I try to increase "AA" by 1 it goes to "B" and not "AB".
VB: tool.Worksheets("TRB Database").Cells(row1, "D").Value = Chr(Asc(tool.Worksheets("TRB Database").Cells(A, "D").Value) + 1)
This is where I increase the value.
View 2 Replies
View Related
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
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
Jan 24, 2007
In the code below, a formula is placed in column F to compute the total of that column. I first find the number of rows and place the formula in the cell below it. I'm dividing the sum by 2 since there are subtotals in the column.
Sub AddColumn()
Dim NumRows As Long
NumRows = Range("A65536").End(xlUp).Row 'get the row count
NumRows = NumRows + 1
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:F308) / 2"
End Sub
The problem with the code is that I don't really know that the last row in the column is F308. I need to replace that part with a variable. It will be something like this (which I know is incorrect):
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:NumRows) / 2"
How would I use a variable in the SUM function in place of the cell names? Should I even be placing the formula in a cell, or should I compute the sum in the macro and place the value in the cell?
View 2 Replies
View Related
May 19, 2009
i can't seem to figure this one out.
I attached a file showing how i am trying to format data (the second tab).
The big issue is the counting to format. (the formatting itself is obviously easy.)
The number of columns is not always standard so i need to count for it.
the number of rows in each "room number" is also not always standard, which means i also need to count for those.
If anyone has had experience trying to do something like this i would appreciate the help.
i know it is possible because i had one that someone had given me years ago, unfortunately i lost it in a hard drive failure...also years ago.
if it isn't obvious what i am trying to do, let me know and i will try to be clearer.
View 9 Replies
View Related
Jan 12, 2008
im trying to find and delete records within a column if they occur twice. this works great right now but I want it to exclude the top 8 rows... i think it might have something to do with the LookAt:=xlPart constraint ...
View 4 Replies
View Related
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
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
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
Jan 8, 2007
I have a sort procedure I have been working on. Sort By Active Cell Column
Now I would like to make sure the row of the activecell.column is row 7. I tried
Private Sub comp_myMonthlyReport_SortAscend()
Dim rng As Range
With ActiveWindow
rng = .ActiveCell(7, .ActiveCell.Column)
End With
Selection.Sort Key1:=rng, _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
But I receive this error: Run-time error '91': Object variable or With block variable not set
View 6 Replies
View Related
May 9, 2008
how (if possible) to activate the link between the Excel spreadsheet and the VBA editor, so when I cut/paste row, columns and cells in my spreadsheet, my VBA code is automatically updated.
I have a rather large excel tool combined with a significant amount of VB code, and each time I insert/remove a line, I have to re-type the cell references,
View 6 Replies
View Related
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
Jun 18, 2014
I have read meanwhile dozends of articles and comments about absolute and relative cell references.
But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.
Is
K$10
a valid expression?
In opposite to $K10 is must work this way
View 3 Replies
View Related
Nov 19, 2008
to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.
View 14 Replies
View Related
Apr 4, 2008
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies
View Related
Jun 16, 2014
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have excel 2013.
View 4 Replies
View Related
Dec 6, 2006
on the syntax of things in VBA. here's a description of what i'm trying to do:
1) loop through a particular column ("N"), and compare the values (if not blank) with corresponding cells in another column ("V"). i.e. - comparing N1 to V1, N2 to V2, etc...
2) run If, ElseIf statements to display appropriate values in different column ("O"), but on the row that corresponds to the row the values in Step 1 are compared. i.e. - if N1 > V1 then cell O1 = value; if N2 > V2 then cell O2 = value; etc...
here's an example (but obviously not in proper syntax). hopefully someone can help me convert it to Excel VBA:
Function CalculateFR()
Dim Col1 As Column = ColumnN
Dim Col2 As Column = ColumnV
Dim Col3 As Column = ColumnO
Dim Cell As Cell
Dim IndexValue As Integer
For Each Cell In Col1
If Col1 > Col 2 Then
IndexValue = 5
Else If Col1 < Col2 Then
IndexValue = 4
End If
Next Cell
If Not IsEmpty(Cell) Then Col3 = IndexValue
End Sub
View 7 Replies
View Related
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
Apr 20, 2009
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?
View 9 Replies
View Related
Mar 27, 2009
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.
Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?
Or in the attached file, I would like to say A and 15 and get A15.
View 3 Replies
View Related
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
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
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
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
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
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
