I want to execute the following code once for each day (Mon to Sun)
Dim Day As String
Dim DeletedDept As String
Set StartRange = Cells(Range("Cashiers").Row + 1, Range(Day & "_Date").Column + 1)
Set EndRange = Cells(Range("Cashier_Totals").Row - 1, Range(Day & "_Date").Column + 3)
Set EntryRange = Range(StartRange, EndRange)
For Each cl In EntryRange
If cl.Value = DeletedDept Then cl.Value = ""
Next
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
Using Excel 2003 with WinXP. I'm trying to run macro code automatically whenever time = 9:30:01 (or whatever time I pick) for a stock market trading program, which is why the exact time matters so much. I've been able to get the time to update fine, but unless I click on the worksheet while the time condition is TRUE then my code doesn't run. The time actually is sent to me from the stock data provider and it shows up in a cell as a constantly updating value.
I've tried using the Workbook_SheetChange function and OnTime method, but without luck.
In both cases, unless I activate the sheet the code doesn't run. By activate, I mean that I have to click on the sheet when the values that trigger the code to run would be true. If I do that it works just fine, but sitting around and clicking on a worksheet defeats the purpose of automation. Since I'm trading stocks this has to be very exact, so I can't trust a macro scheduler to do this.
The code further below is what I'm trying to get to run. The time value is in cell L1. In cell L2 I have the following
Assume a cell -- say A1 -- has a long formula in it. If I select A1, press F2 to "Enter" the cell, then I have a blinking 'l' indicating the cursor/pointer position WITHIN the cell.
Is there any way to control the position of the blinking 'l' (or whatever it is called)? For instance, in a long formula, I would like to write a macro which could transport the blinking 'l' to midway into the formula string.
Alternatively, can I make a partial selection WITHIN a cell and run a macro on it. For instance, if cell B1 has a heading "Dec 2007" and I highlight just the "Dec" portion of the string, I would like to execute a macro to color it red. I have a simple macro that can do it to the entire cell, but not to only part of the cell contents. Is there any way for VBA to be active when I am WITHIN a cell.
Sub Font_Red()
'will add red color to ActiveCell font
Selection.Font.ColorIndex = 3
End Sub
how could I use string to execute VBA. For example
x=10
mystring1="for i =1" & " to " & CStr(x)
mystring2="msgbox i"
mystring3 ="next i"
execute mystring1 & mystring2 & mystring1
Like this, I want to combine a VBA CODE string.
And use this string to execute VBA
I have tried lots of alternative for making Excel Dates & Times calculation work in vba. I can't find the place its creating problem. I have attached spreadsheet to have better look at it. When I am using time in one column (Calender!C) in VBA code, it works fine and update values in R1,R2... columns. But, when I am using time in another column (Calender!D), its not working properly and not updating appropriate columns. I will appreciate if someone can look at vba code in attached file. It looks like I am missing some kind of setting in excel/vba.
View 3 Replies View RelatedI have below code to open DOS from excel. it is working fine.
I wanted to excecute some DOS command automatically after opening DOS, command is entered in the excel Sheet1 E5:E1000
Sub openDos()
Call Shell("cmd.exe " & dosCmd, vbNormalFocus)
End Sub
I have some code that launches a msgbox and I would like to have the code continue to execute with the msgbox displayed rather than wait for the user to click OK/Cancel.
View 9 Replies View RelatedI have a spreadsheet with over 15 columns showing drivers (names numbers etc) and their duties with more inf.
What I need is to be able to find when a certain type of driver in column A (drivers belong to different rutes) say from route A is at rest (this is shown as RD) which is shown in column B and then be able to count how many times those two exact events occur.
Is it possible to write a macro that can import VBA code in a text file then execute it? I need this functionality as I have produced a corporate spreadsheet template that goes out to many people and I will need to be able to update it as requirements change once it has been distributed. So my idea was to build a macro in the template that has the code in it to import the "update code" from a text file that I would send to all the folks that have the template. If the template was centralized, that would save me from this issue but it is however going to be distributed widely.
View 5 Replies View RelatedI would like to write the code or create a macro that will execute when the value of a range of cells is greater than null. The macro or code that I would like to execute will UNHIDE a group of consecutive rows.
View 14 Replies View RelatedI've attached a sample workbook in which there are 3 macro-buttons.
The buttons will paste a shape in the active cell. So this means the buttons themselves could be deleted and replaced with a shape.
Since locking and then protecting the cells disables the macros, how can I amend the code to make sure the buttons' cells are protected from the copying and pasting macros? Or, how do I ensure that the macros only work in A1 - E5?
I have recently used a before_close event on this workbook to save a backup of the open file to another location on my system. This works fine but I was wondering if there was some more code I could add to only execute this event on a write access basis.
The file I use can be viewed by anyone on the network as read-only and only certain users with a password can edit/update with a write access password.
The backup event is use executes every time the document is closed be it read-only or write-access.
Ideally I would like to add some code to only execute this backup if the file is opened on a write-access basis.
I need to be able to hide and unhide a given set of rows based on the value in a particular cell. My current code allows me to successfully do this, ONLY, when I select the cell being 'watched' for changes and press enter. It does not execute the code automatically.
The cell is changed by a set of filters that modifies the data. The watched cell is then a summation of the filter modified cells. If the filters change to all "0" or "-" then my 'watched' cell sum becomes "0" and thus should execute the code automatically.
Current Code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Outline.ShowLevels RowLevels:=3
If Range("I62").Value = "0" Then
Rows("63:87").Hidden = True
End If
End Sub
So, right now if I use the filter and the cells change then the sum in I62 becomes "0", I have to manually select cell I62 and press enter and which point the above code executes exactly as I intend.
I need the execution part to be automatic and not have to manual click into cell I62.
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
I have the following structure: ...
I have the following code:
[Code] .......
What do I need to change in order to make it execute the Call statement on EVERY item in the ListBox2, not the Selections.
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies View RelatedIs there any way to "Inscribe" a cell? I would like to run a macro on Enter keypress, that would execute different code depending on that "inscription" that would be invisible to user. I could use some properties of . Validation property like this:
Private Sub EnterPressed
'following code to ensure proper functioning of Enter in any other Worksheet
If ActiveSheet <> mySheet 'MySheet is global Variable then
ActiveCell.Offset(1,0).Select
exit Sub
End If
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
ActiveCell.Offset(0,1).Select
Else
'something else
End If
End Sub
The problem is, I use Data Validation and Conditional Formatting, so can't use any of these properties.
I'm trying to develop a work spreadsheet template that is to be summarised every fortnight. Our fortnights here go Thurs-Thurs. So I figured I would start with a base date (thurs - Sept 4th) and then have the spreadsheet display the true fortnight ending upon loading. Ultimately this fortnight ending will be used to copy and paste the template information into a summary spreadsheet but for now I am trying to get this intial part working. It's linked to a command button and my attempt at code is below:
Private Sub CommandButton1_Click()
Dim Base As Date
Dim Now As Date
Dim Delta As Integer
Dim No_of_Fortnights!
Dim Rounded As Integer
Dim Fract!
Dim Fort_week_end As Date
(Note the following 8 lines are not the problem, they just open the main sheet. The problem is the eights lines starting with the word 'set')
Sheets("Technical Inquiry Form").Activate
Sheets("Technical Inquiry Form").Range("b3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Sheets("Technical Inquiry Form").Range("b3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Technical Inquiry Form").Activate
I have the following code for a user-defined function:
Function SeriesSum2(A, B, x, C, D, y, z, Num)
Summation = 0
For i = 1 To Num
Summation = Summation + (((A - B) * (((0.01 * B / (A - B)) _
^ (1 / (y - 1))) ^ i) + B - x) / ((1 + x) ^ i))
Next i
Product = 1
For i = 1 To Num
Product = Product * (1 + ((C - D) * (((0.01 * D / (C - D)) _
^ (1 / (z - 1))) ^ i) + D))
Next i
SeriesSum2 = WorksheetFunction.SumProduct(Summation, Product)
End Function
To simplify, suppose Num = 3, and the three terms of Summation are {3, 5, 9}. Call these S1, S2, S3. Further, suppose the first three terms of Product are {1, 4, 12}. Call these P1, P2, P3.
My desired result of the function is 3*1 + 5*4 + 9*12 = 131. Instead, I'm getting 3*12+5*12+9*12 = 204. That is, the function is returning S1*P3+S2*P3+S3*P3, while I want S1*P1+S2*P2+S3*P3.
Can anyone instruct me as to what I need to do to get my desired result? I'm guessing it has something to do with storing Product as an array, but I'm a very novice VB'er, so I'm not sure.
Of course, if there are any other glaring errors in my code,
I am using follwoing vb code to enter formula to sum a range. However it is giving sum of different range.
Cells(Row, Column).FormulaR1C1 = "=SUM(R[" & a & "]C[" & b & "]:R[" & x & "]C[" & y & "])"
Where a, b , x and y are variables containg starting row, starting column, last row and last column value like a=19; b=3; x=24 and y=3. When i check the formula in that cell, it appears as Sum(F25:F30)
This vba code will delete all the rows which have data in. At present it starts on A2 (so doesnt delete anything above A2. Which part of the code in red would I need to change for it to be A3 and which would I need to change for it to be B3.
View 5 Replies View RelatedI have two columns one with the variables names and the other with the values now I have to assign vales to teh variables dynamically using the macros and vba code.
View 7 Replies View RelatedIn this workbook Test1.xlsm under column "D" i have Po numbers. Some PO numbers are the same and some aren't. I have a specific code that creates an invoice for a customer when i hit the code however i must select a cell under column "D" in order for the code to create an invoice for the customer on that row. I usually create the invoice for all the customers of the same PO number. Sometimes the amount of invoices i have to create is overwhelming so i was wondering if it's possible that when i select a cell under column "D" that has, let's say, PO number "654" if the code can create an invoice for all customers that have the same PO number of "654"?
View 1 Replies View RelatedIm having a bit of an issue getting my head around why I am getting the result I am. I have data in ws1, a search list in ws2 and the results of the search in ws3.
It works though for some reason it loops 3 times... not sure why.
Additionaly I want to be able to run the same sub against 2 work sheets, ie, ws1 and another ws titled "otherWS" for this example. So run the exact sub again and return the results to ws2 as well.
I have some VBA that dumps various sheets data into an SQL Database.
Part of that requires me to sanitize all of the fields before they make it to the DB, (at least to prevent the code from breaking itself w/ errant ' characters.
right now my code is as follows
Code:
If InStr(aa, "'") > 0 Then
aa = Replace(aa, "'", "''")
End If
If InStr(bb, "'") > 0 Then
bb = Replace(bb, "'", "''")
[Code] ......
I was hoping to condense it to something like the following, however it is not working how I hoped / want it to. .. I had found somewhere out there that this Eval() function possibly could be used to 'reference' dynamic variables, however it does not appear to work at all anymore, and even then it may only have worked to 'read' and not 'write' to the variable. (Excel 2010, on Windows 7 64-bit).
Code:
itemsToSanitize = "aa,bb,cc,dd,ee,ff,gg,hh,ii,jj,kk,ll,mm,nn,oo,pp,qq,rr,ss,tt,uu,vv,ww,xx,yy,zz,aaa,bbb,ccc,ddd,eee,fff,ggg,hhh"
ITSArray = Split(itemsToSanitize, ",")
For Each thing In ITSArray
If InStr(Eval(thing), "'") > 0 Then
Eval(thing) = Replace(Eval(thing), "'", "''")
End If
Next thing
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
I am trying to have the formula =( SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Put into cells through vba. What I did to get the formula is typed it into an excel cell to find the average of a group of cells that do contain blank cells. The formula brought out the proper results. So all I did is put the formula into vba and changed the appropriate parts. The range will not be the same of course, but there is what I have.
ActiveCell.Formula = "=(SUMIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "," & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & "))/(COUNTIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "))"
When I show a msgbox for ActiveCell.Formula (Msgbox activecell.formula), it shows me the formula as above - =(SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Except instead of the s:ranges, it shows $L2:$L125 (which is correct). The quotes do show up around the criteria in both the sumif and countif. I keep receiving an error. I put a msgbox err.description & ", " err.number dialog in. The error comes up as ", 0" (no quotes).
I'm trying to execute a macro and it won't put the focus (radio button) to select x pages wide by x pages tall in the Page Setup/Page/Scaling Area. I looked at the macro and can't find a setting in the code but yet the focus won't change. What can I do about this? In other words the radio button stays selected as "adjust to "" % of normal size. Here's the code...
View 2 Replies View RelatedI am trying to use VB to vlookup between to workbooks
1. Make active workbook WBK1
2. Make workbook being open WBK2
3. Copy and Paste between WBK1 and WBK2
4. Have a vlookup in WBK1 and bring in the values from WBK2
5. Close WKB2
6. Copy, Paste, and transpose values in wkb1 within wkb1
The script works fine until it reaches the vlookup step. I have used the vlookup by itself without the copy and paste code successfully but when I combine the two it provides me with the error 9. Subscript out of range.