Code: Defining Variables
Sep 15, 2008
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
View 9 Replies
ADVERTISEMENT
May 25, 2006
What the code does is that it runs in a Master workbook, and searches for XLS files in a defined folder. It opens a file, count, closes the workbook and takes the tallied number of sheets and places it in the master file, column A being File Names and column B as Number of sheets. What I need is for the code to take the file name from column A, run it and then place sheet counts in Column B and then move down the columns until all the counting is done. Currently I am only able to define one row using the range function and i'm at a loss.
'Get file names and enter into Master
Public Sub ShowFolderList()
Dim wb As Workbook
Dim ws As Worksheet
ChDrive "C"
ChDir "C:Documents and SettingsTest Folder"
outrow = 2
Filess = Dir("*.xls")
While Not Filess = ""
Cells(outrow, 1).Value = Filess
outrow = outrow + 1
Filess = Dir()
Wend.......................
View 2 Replies
View Related
Apr 5, 2012
It's just meant to work through a long list of data blocks defined by column 8 changing. At that point I want to calculate the average of the first 20 entries in columns 12, 13, 14, 15, 16, 17 in that particular block.
I can achieve the sort and calculate the block start rows OK, just can't get the average definition right.
Code:
Option Base 1
Dim c(6)
Sub calc_averages()
[Code]....
View 2 Replies
View Related
Jun 18, 2008
I started plugging away at VB a little over a month a go and i feel i've learnt alot but can see just how limited my knowledge is. how to define all my variables in one module that then will feed into the code on other modules and forms in the workbook where required. For example, my spreadsheet has approx 30 different variables that are called upon by a variety of subs. How do i define all these in one place and then just call on the relevant ones when required rather then having to redefine them for every new sub.
View 2 Replies
View Related
Aug 25, 2009
I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.
View 3 Replies
View Related
Apr 27, 2006
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................
View 3 Replies
View Related
Feb 5, 2009
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
View 2 Replies
View Related
May 13, 2009
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: ...
View 11 Replies
View Related
Jun 29, 2007
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,
View 9 Replies
View Related
May 10, 2007
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
View 3 Replies
View Related
Sep 4, 2007
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)
View 3 Replies
View Related
Oct 28, 2008
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 Related
Jul 6, 2012
I 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 Related
Jul 17, 2012
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
View 7 Replies
View Related
Aug 26, 2008
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?
View 4 Replies
View Related
Apr 15, 2008
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).
View 2 Replies
View Related
May 21, 2008
I 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.
View 9 Replies
View Related
May 22, 2008
I would like to use a varible Cell for the following forumula:
ActiveCell.Formula = "= ROUND(PIExpDat(""TimeEq('""&Cells(RowNdxG,4)&F$2,F$3,F$4,F$5,0,)/3600,1)"
I have underlined the variable. This is not working but is rather showing up as a string value Cells(RowNdxG, 4).
View 6 Replies
View Related
Sep 9, 2012
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Code:
Sub populate()
Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer
Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
View 6 Replies
View Related
Feb 17, 2014
I'm trying to make a chart viewer on the first sheet of my spreadsheet and am using this website as a guideline, [URL] However instead of the 3 charts in the example, I have 12 which makes my formula for defining a name substantially longer.
By moving my charts to the same sheet and getting rid of the INDIRECT formulas, I shortened my formula to 291 characters which is too long as the limit is 255.
=IF($G$9="Sodium",$N$34,IF($G$9="Manganese",$N$35,IF($G$9="Iron",$N$36,IF($G$9="Nitrate Nitrite",$N$37,IF($G$9="Nitrite",$N$38,IF($G$9="Nitrate",$N$39,IF($G$9="PH",$N$40,
IF($G$9="Conductivity",$N$41,IF($G$9="Turbidity",$N$42,IF($G$9="Fluoride",$N$43,IF($G$9="Selenium",$N$44,$N$45)))))))))))
Is there any way to make my formula shorter? Or to reference a cell where I place the formula?
View 5 Replies
View Related
Oct 7, 2008
If Average GMV (Cell A1) is less than 75% of Average BP (Cell B1), use Average GMV, however the Average GMV or Average BP can't be less than $600 million. Multiply the result by .01*1/3. Below are two equations I've created, however each of them only solves one part of the problem.
=IF(A1<0.75*B1,A1*0.01*1/3,B1*0.01*1/3)
=IF(B1<600000000,(600000000)*0.01*1/3,B1*0.01*1/3)
Essentially Average BP would be used, unless Average BP was something like $900,000,000 and Average GMV was around $650,000,000. At this point Average GMV is 72% of Average BP and above the $600,000,000 minimum.
View 2 Replies
View Related
Jun 16, 2009
Knowing Excel adapts its formulas upon inserting a row (i.e. a sum formula will automatically include a new row inserted in the middle of its range). Is it possible to do this with a named range. I have a pivot table that serves as a table for a vlookup, but the table sometimes changes month to month with extra accounts. I need to be able to keep the formula even though the table changes, so I figured a named range would help me out but it still doesn't accomplish what I need it to do. So basically, is there a way to make the Named Range, say AGroup (A1:C5) extend to (A1:C7) if 2 new rows are added in?
View 12 Replies
View Related
Apr 23, 2009
I have a sheet containing several charts. I have a script which I found on this site that helps me create dynamic min and max values for the axis.
Since I have several charts it seems that I need to define which chart that I want to change the axis.
This causes an error.
View 9 Replies
View Related
Aug 7, 2009
I have a form in excel that people are going to populate, then a button which runs a macro which copies all the cells out into a new line in a different worksheet inside the spreadsheet. im going to use that sheet to formulate graphs etc, the macro works however it overwrites previous data, im assuming i need some function to calculate which is the next empty cell then use this to to copy the data to
View 12 Replies
View Related
Jan 22, 2010
I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!
1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under
blanks or zeros = unknown
View 8 Replies
View Related
May 21, 2014
I am starting to write a macro and first thing i Need to define is all the columns that I need in my report. I started out by defining an array and this is what I have so far.
I need that every time I run this macro it should vlookup the data from cleantxt files that are saved in my c:autofile ReportsFC Banded Summary and essentially create separate excel files for different buyer names and save those sheets to my C:Banded Summaries with the name as Bandedsummary(todays Date)_BuyerName
This is going to be the bones of it then I have to figure out how to vlookup data for all the columns and do sumifs and format it.
This is what I have so far:
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
[Code]....
View 4 Replies
View Related
Nov 6, 2008
Defining Cell Name when Dynamic?. Here is whare I am.
View 3 Replies
View Related
Dec 7, 2008
I have a piece of code I am using to important data from a text file. The code works fine but I am having a hard time understanding how defining a Variable "As New Collection" works. I have tried to research on numerous websites dedicated to excel but had no luck finding any further information.
I have executed the code line-by-line over-and-over and believe what the "new collection" does is it creates some type of external database that is hidden within excel (that's just my guess). I have attached a sample file and a text file with dummy data if needed to see how the code is working.
View 4 Replies
View Related
Jan 20, 2009
I tried to run this code and encountered an error telling me that N should be an object.
N has to be a variable number representing the amount of sheets in the workbook -2.
Does anyone know how this is written?
View 6 Replies
View Related
Dec 5, 2009
I've got a spreadsheet that could have 1 of 3 possible print ranges:
PR1 = B2:K67
PR2 = B2:K132
PR3 = B2:K197
If there is a value greater than "0" in Cell B68, the print area would be PR2, but if there is a value greater than "0" in Cell B133, the print area would be PR3, otherwise it would be PR1.
These print ranges are such that the page break would would be below B67 and B132, but I'm concerned that I may need to set these page breaks in the Macro.
I'm not sure how to use the IF, THEN, ELSE, in the Macro to test the value of the cell and then set the print area.
View 3 Replies
View Related
