# Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

ADVERTISEMENT

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'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 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

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)

Can a Function give two or more output variables. e.g.

Sub a()

x = 5

result = Y(x)

End Sub

Function Y (x As Integer) As Integer

Dim B

B = ... * x

Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?

I need this because function works with large matrix and I want to extract some values appeared in between.

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 RelatedI 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 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 ..................

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.

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).

I have a file I inherited that uses many VLOOKUP formulas. It was written in Excel 2003, and works perfectly there.

However, I just had to install Excel 2007, and when I open this file, all those formulas return #VALUE! errors.

Is there a known issue with VLOOKUPs in 2007 versus 2003?

An example of one of the errored formulas is:

=VLOOKUP($C3,ItemMaster!$A$1:$N$5800,2)

It should be noted that the first row of this range is the column titles, rather than data. But as I said, the formula works fine in 2003.

What's completely bizarre is that if I edit the formula to either of the following, it works fine:

=VLOOKUP($C3,ItemMaster!$A$2:$N$5800,2)

=VLOOKUP($C3,ItemMaster!$A$1:$N$5800,2,FALSE)

Changing the first row of the range to row 2, or adding the FALSE at the end makes it work. But why would it work in 2003 and not in 2007?

This is an *enormous* file, and this is just one example of a *load* of formulas that are returning errors (so far, all seem to be related to VLOOKUPs - at least the ones I've found so far). It's 25MB worth of complicated formulas, with external links to Access databases for the source data, just to give you an idea of the scope. So going through and trying to find and then change every error that might be occurring just because 2007 doesn't like it is going to be a complete disaster. You never find them all. It might be simpler to just downgrade back to 2003!

I'm using:- Open "file name" For Input As #1 to read and manipulate files. However, I've hit a problem with how to open and read zipped files (winrar).

View 3 Replies View Relatedi have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0".

At moment I use following

vHour1_KW1 = 0

vHour1_KW2 = 0

...

vHour1_KW53 = 0

vHour2_KW1 = 0

vHour2_KW2 = 0

...

vHour2_KW53 = 0

until...............................

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.

I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..

View 2 Replies View RelatedI'm having a hard time understanding how to accomplish what seems to be a simple result.

I need to display one of two words, based on whether or not a pair of values are above or below the criteria.

FIRST:

IF H6 is greater than 5000

AND

IF AB6 is greater than 25000

Display: Double

SECOND:

IF H6 is less than 5000

AND

IF AB6 is greater than 25000

Display: Single

There is no 3rd scenario, even though logically there should be.

I am trying to put variables in this URL which is related to yahoo finance :

.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"

I defined at the beginning

Dim start_date As Date

Dim end_date As Date

Dim datestring As Variant

start_date = #1/31/2001#

end_date = #11/26/2006#

and put them in datestring

I passed the datestring to a new sub which has the URL:

.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"

So, my question is, i tried to put the (1/31/2001) and (26/11/2007) which is in the above URL which is separated in variables and the URL remain the same

I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.

What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4

I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")

vlookup with 3 different variables, for example cells k4 k5 and k6 can be changed to give different variables. Is it possible to have a vlookup function in cell k9 which returns the correct % when the 3 variables are chosen. example, blue boat 48 would return %value of 21%

View 2 Replies View RelatedI am trying to count the status and type of some work so:

Column A would contain the status of the work e.g. open, in progress, closed etc.

Column B would contain the department: ict, development, operations, etc.

I want to do a summary that shows: How many are in ICT are open, closed etc.

I can do a countif to get the total open, in progress etc or total number of ICT jobs but not ICT In progress.

I am trying to add an additional criteria to the following sumproduct formula. The formula below works fine to add up values that are within a date range. However, I want to add values within a specified date range as well as one additional variable. The additional variable is in column G.

SUMPRODUCT(--($A$3:$A$1015>=$A1026),--($A$3:$A$1015<=$B1026),D$3:D$1015)

Trying to work out the formulas for placing plus minus variables above and below a cell as per worksheet attached. right hand side of the page

View 2 Replies View RelatedI want to make an excel funktion than can distinguish between 3 variables. The three variables are the outcomes of my first function which are 0, 1 and 2 these IF functions can be seen below:

=IF(B11>52;"1";"0")

=IF(B12>5;"1";"0")

Then when I have these results I have tried the following function:

=IF(C11+C12=2;"6-12";"4-6")

By using this function I get an output for 2 (true) and 0 (false), but I would also like an output for 1 (which would be 6 in my work)

It looks like this:

Var 1 58 1

Var 2 4 0

Suggestion 4-6

So in the above case where the sum of var1 and var2 is 1 it is only counted as false for not being 2 and therefore = 4-6 instead of what I would like it to be = 6 (for 1)

I'm trying to count cells in one column that match a variable only if it also matches a variable in another column. For example, I want to count all of the cells in column A that match "Franklin" only if column D shows "True".

View 5 Replies View RelatedSay I have a list of part numbers, and each part number has an X or a 0 next to it, depending on my own set parameter.

How do I then report that data on another tab so that it counts how many there are in a set area AND if its an X.

At the moment I have this:

Copyrights 2005-15 www.BigResource.com, All rights reserved