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.
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).....................
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!
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.
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
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".
The MsgBox's are merely for debugging. When the code above runs, the first MsgBox I receive states similar to: "vSh 14.238964 vOp 45.23 vCash 1000" The second MsgBox, from after the multiplication, states: "vCash 1000"
For some reason the vCash variable just will not set. At the beginning of the code, I declared each variable as Dim var, meaning as a variant, and whenever I debug by halting during a MsgBox display, each of the variables appear to be the same types: Variant/Double.
G:G contains a list of integars, though some cells are blank; lets say 75 of the 100 cells in data range are < 0. I want a macro which copies a range where the number of rows = the number of values in G:G. my Macro:
Sub myMacro() Dim rowRange As Integer rowRange = Count("G:G") End Sub
This, as you guessed, comes back with an error. Sub or Function not defined. Anyone the proper syntax for assigning a formula to a variable?
I am trying to open a workbook, assign it a variable and use that variable later. here is what i have
[Code] ......
When I try to activate it later on in another procedure using:
[Code] copy to clipboard
I receive an "Automation Error"
I have tried declaring it as a public variable, even a global variable and cannot get this to work.
All I'm really wanting to do is open a workbook (where the name will change depending on the book being opened), assign that workbook a variable name, and use it later in the code.
I’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.
Ive wrote some code that goes into a workbook. This code then opens up a "master" workbook which has 18 blank tabs in it and then proceeds to open up 18 other named workbooks one at a time and copy some date from these workbooks to the master (i.e workbook 1's data goes to the master workbook on tab "1".)
I have this working no problem but here is my snag.
The workbook name changes every week to correspond with the date (i.e 1_14DEC2011.xls then 1_21DEC2011.xls).
Ideally id like to be able to create a variable for the latter half of the filename so that i can apply this variable to each filepath but i cant quite get it to work.
A small sample of my code is below (and yes i know it is very blunt but so is my knowledge at this stage!)
Code: Sub collate() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual
I would like to assign listbox members to a public variable (quite the opposite of what you normally do). I receive the error message "type mismatch".
Public MemberLB as Variant Public Sub ListBoxTest() n = ListBox_target.ListCount For iCnt = 1 To n MemberLB(iCnt) = ListBox_target.List(iCnt - 1) Next iCnt End Sub
I have a formula which assigns a points score to letters in a range and adds them up. In the example below F=0, P=6, M=12 & D=18. =IF( COUNTIF(AT5:BE5,"="""),"",SUM(COUNTIF(AT5:BE5,"=F")*0,COUNTIF(AT5:BE5,"=P")*6,COUNTIF(AT5:BE5,"=M")*12,COUNTIF(AT5:BE5,"=D")*18)). Thus if F, P, M & D were in cells A1:D1 the result would be 0+6+12+18=36.
My aim is to be able to customise the values of F, P, M & D, using a table and a cell value. See the table below, where the first number in each row represents a cell value which the user enters into BH1, the second, third, fourth and fifth numbers represents the values assigned to the letters F, P, M & D.
Some examples of expected output: user enters 10 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+1+2+3=6. user enters 90 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+9+18+27=54.
I am working on a customer report template that generates our customers reports and will send them out automatically.
This issue I have now is that the system that generates the raw data for these reports only lists the Customers name in a column with an entry for each line of data, the thing is though that the system has lots of variations of the customers name, even more so if that customers has different departments.
What I need to do is from this list of customer names, I need to automatically figure out what the "common" name is or main name so to speak, and then make a variable using the correct full name, which will be used later on in the code to import correct logos, and direct the reports to the correct people.
Here is a quick example of what data we get raw from the system:
Customer Name: John Build Johns Buildings Johns Ltd Building Johns Plumbing Department Glass Doors Ltd A Department of Johns Buildings Johns Building Corporation Hole In One Golf Range
This is just an example, we have thousands of clients, so the length, number of words etc can change alot. Ideally I from a list similar to that I would get a full proper result of "Johns Buildings Ltd" for example, this would then be in a variable to be used in code from then on to reference doing certain things with the reports of Johns Buildings Ltd.
You'll notice there is one name "Hole In One Golf Range" that seems to have no relation at all, this is correct, ideally I would also like to build in some error checking into the code, so that rows like that that have nothing to do with the others would get deleted.
So how would you amazing VBA gurus go about working with data like this? I'd prefer a more general answer with explainations that just straight code, as I'm sure I will have to adapt the hell out of it for it to be useful in context.
I am writing a code wherein I want to assign specific excel range to a declared variable and then paste this excel in outlook message ody but I am finding difficulty in assigning that range to variable " brng"
I think code is right and issue is there in excel setting.
Code: Sub mailer() ' ' ' Dim Ash As Worksheet Dim brng As Range Dim OutApp As Object Dim OutMail As Object 'Windows("Copy of FF RPL REPORT_JULY").Activate
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?
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?
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:
I have a number of cells which are connected to various formulas. EX. A1 = 7. Well I want a formula in A2 which can look at A1 and if A1 = 5-6, then A2 = 1, if A1 = 7-8, then A2 =2, if A1 = 9-10, then A2 = 3, if A1 = 11-12, then A2 = 4. And so on.
I have a macro which retrieves data from web.It works fine.I just don't know how to assign a cell value in web address.i have attached the code here. i just want to assign activesheet.(range"c2").value for "q?s".
I have written the code below. And I found two problems:
1.The code stopped to work when I change from Paste to PasteSpecial.
Sub Copyfriction() Dim DestWB As Workbook: Set DestWB = ActiveWorkbook Dim DestWS As Worksheet: Set DestWS = ActiveSheet Dim DestCLL As Range: Set DestCLL = ActiveCell Dim UserChoice UserChoice = Application.GetOpenFilename(FileFilter:="Text Files (*.xls),*.xls")
[Code] ........
2.As you see the right cell on the the destination sheet need to be selected. So Im planing to run the macro when double clicking the cell in question. I have tried the next
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = "$A$6" Then
I am trying to assign an array element to select a specific cell then assign a value to it. Below is the general code that I am working with. Does anyone know why this is not working?....
I'm working on a tracking spreadsheet that needs to be able to function on both Excel 2004 for Mac as well as Excel 2007 for PC. The code below is written to ask for a date when the status of a particular row of cells is changed to a value other than "". These cells have validation lists where the list itself is just below the cell in hidden rows. It then places the date entered in the corresponding cell to the right of the list choice in the hidden rows and the top row is simply a vlookup to show the date with the status choice.
This way, when the status cell is changed again, it retains the date in the hidden rows while setting a different one. The code below is obviously not the full code, the whole code includes other functions and is a bit long, but I've narrowed it down to this:
I see that IF statements can only be nested 7 times. How do I create a cell that checks to see what item was selected in the dropdown box of 20 different choices and choose a particular cell range depending on what is chosen?
