Variable Declaration
Jan 7, 2008
I've been asked to produce a spread sheet that will calculate split payments for a list of households built by our company. The bill amount will be given to us by the power company, and the basis for bill separation is base on the billing period, and the date of house settlement.
The codes I've produced are written below, however I couldn't get it to run properly. The software keeps on telling me that my variables aren't declared correctly.
I've attached all my codes below for your reference.
Public Start_Date As Date
Start_Date = Worksheets("Sheet_Input").Cells("D", 1).Value
Public End_Date As Date
End_Date = Worksheets("Sheet_Input").Cells("F", 1).Value
Public Total_Date As Integer
Total_Date = Worksheets("Sheet_Input").Cells("H", 1).Value
Public aAverage As Integer
aAverage = Worksheets("Sheet_Input").Cells("B", 3).Value
Public bAverage As Integer
bAverage = Worksheets("Sheet_Input").Cells("D", 3).Value
View 3 Replies
ADVERTISEMENT
Aug 14, 2012
I have two issues in SumProduct formula.
1.Formula Def.Cells(c, d).Formula = "=SUMPRODUCT(--(Severity =" & """" & severity_3 & """" & "),
--(OFFSET(Severity,0,4)=" & """" & module & """" & "))"
Where severity_3= Shipping
There are many fields like Shipping, Shipping-UI, Shipping-BE.
I want the formula to consider all the above when severity_3= Shipping and take the count & not only consider Shipping(Shipping*) .
2.Formula test = "0 - Unclassified" Or "Alpha Testing" Or "Beta Testing" Or "Functional - Interface" Or "Functional - Report" Or "Regression Testing" Or "Requirement Review"
when i use the above formula , it is displaying the message "Run-time error'13':Type mismatch There are spaces in the criteria i have used but i am not sure how to resolve this
View 1 Replies
View Related
Dec 25, 2007
I have a smattering of experience within various programming languages, but am still coming to terms with the basics of VBA. I am trying to declare a global variable, assign it a value, then use that global variable. Within 'ThisWorkbook' I have the following...
Public myText As String
Private Sub Workbook_Open()
myText = "Hi There"
End Sub
...and in the Microsoft Worksheet Object Sheet1 (Sheet1) I have...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox myText
End Sub
Now, my understanding of this code is that upon opening the workbook, myText variable will be declared, and then assigned the value "Hi There". Then, once I have clicked anywhere on Sheet1, a message box will appear stating "Hi There". Problem is, the message box is blank. This is all fine, except I want the message box to state "Hi There". What am I doing wrong? Is the variable declared (publically) correctly? Am I assigning the public variable the value correctly?
Am I referencing the public variable correctly in the Worksheet_SelectionChange procedure correctly?
View 3 Replies
View Related
Jul 27, 2007
Hopefully this isn't too vague, but almost every project I've worked on so far has not recognized Public variables in all modules. I've constantly been searching for exceptions to this rule that could be contributing to this issue but haven't been successful.
I have read a million times that declaring a variable as Public makes it visible to all modules and preserves the value throughout. I've also read that if you edit code or reset your project, the values may be lost. I also know that if you try to use a variable before you give it a value (or for object variables set it = to something), it doesn't have a value yet. But all this applied, I'm still not getting Public variables to be consistently recognized or stay with the value I want them to have throughout the lifetime of my project (while the workbook is open)
My specific current issue (one of many so far) is that I have 4 command buttons within a worksheet object. Each triggers a different group of procedures, some of these are user forms. In the first button, I create two worksheets one to contain the current fiscal period "Options" the other for "Summary" of all the totals etc... (all financial data). In this first userform, triggered by the button, I allow the user to name these worksheets: like Summer2007Options or Summer2007Summary and I declare these publically as object variables OptSheet and SumSheet. These variable names and sheets are recognized for the next 2 buttons but now that I'm on the fourth, I have object variable not set errors when I try to refer to this variable.
View 9 Replies
View Related
Jan 2, 2007
The following is not working for me with an overflow #6 error.
Sub multiplythis()
Dim highnumber ' As Long, I've tried Double, Long and now Variant
highnumber = 50 * 250 * 300 * 400 * 20
End Sub
View 9 Replies
View Related
Nov 20, 2008
I am getting "Duplicate Declaration" error while running my code. Cant post entire code as its too long but i am pasting the problem line.
View 12 Replies
View Related
Aug 28, 2009
I'm using a public declaration for a worksheet but I seem to have to initialize it in each routine. Is there a way to set it and leave it? So in a module I have:
View 4 Replies
View Related
Apr 23, 2012
I have some code that runs a mouse event and keeps my computer active when I'm away. It works on my 32-bit computer, but doesn't work on my 64-bit. What is the correct syntax to have it work on a 64-bit?
I get the error: "declare stmts must be updated for 64 bit and marked with PtrSafe attribute."
BTW, I already tried changing "user32" to "user64", that didn't work.
Code follows:
Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Dim TimerActive As Boolean
Sub KeepWindowsActive()
[code].....
using win 7.
View 2 Replies
View Related
Jun 21, 2008
I suspect the array variables declaration in the following example is the problem.
The array Function MyRoots() incorrectly returns 0.0 results to cells I11:J13.
cell B8::3
cell B9: myTrue
cells B11:C14 numerical values
cells I11:J13:: array function {=MyRoots(B11:C14, B8, B9)}
Function MyRoots (a, m As Integer, polish As String)
ReDim a(m + 1, 2) As Double
ReDim roots(m, 2) As Double
Dim j As Integer, its As Integer
Dim x(2) As Double
ReDim ad(m + 1, 2) As Double
......................................my code............
For j = 1 To m + 1
ad(j, 1) = a(j, 1)
ad(j, 2) = a(j, 2)
Next j
......................................my code............
Call Laguer (ad, j, x, its)
......................................my code............
roots(j, 1) = x(1)
roots(j, 2) = x(2)
......................................my code............
MyRoots = roots
End Function
Sub Laguer (a, m, x, its)
Dim x1(2) As Double
......................................my code.......................................
x(1) = x1(1)
x(2) = x1(2)
......................................my code............
Exit Sub
End Sub
View 9 Replies
View Related
Aug 3, 2009
I have a formula that hides rows within a range for a sheet.
View 2 Replies
View Related
Nov 19, 2009
I am using the format of using variables for declaring file paths and document names in VBA however I want to make sure I am doing it properly...
Dim Server, VersionName As String
Server = Worksheets("Calculation Matrix").Range("CalculationMatrix_Server").Value
VersionName = Worksheets("Calculation Matrix").Range("CalculationMatrix_VersionName").Value
ActiveWorkbook.SaveAs Filename:="" & Server & "uploadcomplete" & VersionName & "", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Should these variables be declared as String?
View 9 Replies
View Related
Jun 3, 2007
I am trying to run a VB script that will copy data from 3 worksheets and past it into 3 New worksheets, sort that data into alpha order then delete all rows containing Zeros that may be found in Column B, Then copy that data from the 3 New worksheet back into the original 3 worksheets.
The copying and sorting of the data from one sheet to another is easy. The problem is trying to delete the zeros found in column B once its been copied over into the 3 New worksheets.
The script I am using to delete any Zeros found in Column B is below.
If I try to use this script more then once, so that I can delete all the zeros found in column B on all the New worksheets I get this error “Duplicate declaration in current script”
Is there a way for me to refer back to this script instead of me trying to past it more then once within the VB script.
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const StartRow As Long = 1 'Row to Start looking at
Dim StopRow As Long
Dim Col As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row
Dim cnt As Long
For cnt = StopRow To StartRow Step -1
If Not IsEmpty(Cells(cnt, Col)) Then
If IsNumeric(Cells(cnt, Col)) Then
If Cells(cnt, Col) = 0 Then Rows(cnt).Delete
End If
End If
Next cnt
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Please see attached workbook. You will understand it better once you see the workbook and the VB script in it.
View 6 Replies
View Related
Jun 28, 2009
I get an error: Duplicate declaration in current scope? The formula calculates the compound annual growth rate.
View 5 Replies
View Related
Feb 17, 2012
I have the following code which performs the same action on 2 different tabs within the same workbook:
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("O1"), Range("O" & Rows.Count).End(xlUp))
Rng.NumberFormat = "@"
For Each Dn In Rng
If Len(Dn) < 7 Then Dn = String(7 - Len(Dn), "0") & Dn
Next Dn
i get the following error:
Compile Error: Duplicate declaration in current scope
View 5 Replies
View Related
Oct 27, 2011
I am trying to automate the creation of 3 pivot tables. At first I was having a problem with deselecting all items in the drop-down except one, then I found the code to fix it.
I replaced this code:
ActiveSheet.PivotTables("PivotTable15").PivotFields( _
"Beta")
.PivotItems("Escalated").Visible = False
.PivotItems("Hang Up").Visible = False
.PivotItems("New Hire Requests").Visible = False
.PivotItems("No Trouble Found").Visible = False
.PivotItems("Priority Exchange").Visible = False
[code].....
This fixed the problem, but there are still two other pivot tables that need to be created after this first one. I scrolled down through the code and deleted the other code blocks for the selection of the PivotItems and replaced it with the code above, but then when I ran it I got the "Compile Error: Duplicate Declaration in current scope".
I read more through the forum and realize that it's because VBA is dimensioning it twice, and I read that you're supposed to Dimension at the beginning of your program, but how do I implement this into my coding?
I can't dimension literally at the start of the code, there is some formatting that needs to take place first. (Basically I paste in a bunch of data into Sheet1 of my workbook, center it, space it out, then insert a pivot table into the pre-existing Sheet2 based on that data, filtered on the blank entries in Column N. Then I need to create ANOTHER pivot table based on that same data in Sheet1, but filtered on the blank entries in Column O. Then I center the words in the Pivot tables' headers in Sheet2, and finally I need to create one last pivot table on pre-existing Sheet3 based on the data on Sheet1, filtered for 2 specific entry types in Column N.)
View 2 Replies
View Related
Jun 4, 2007
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test()
Dim StartRng As Range
Dim Buffer As Range
Set StartRng = WorkSheets("Sheet1"),Cells(1,1)
StartRng.Activate
ActiveCell. CurrentRegion.Select
Buffer = rngStart.CurrentRegion.Copy
' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub
View 9 Replies
View Related
Aug 8, 2009
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies
View Related
Feb 20, 2009
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
View 9 Replies
View Related
Jun 27, 2014
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
View 2 Replies
View Related
Dec 7, 2008
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.
View 3 Replies
View Related
Feb 28, 2013
I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.
But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"
How to set the file which got opened from the folder to wbk variable.
Code:
Sub dataintoonesheet()
Dim i As Integer
Dim jk As Integer
Dim j As Integer
Dim rowstart As Integer
rowstart = 3
[Code] .......
View 9 Replies
View Related
Jul 7, 2009
When i try to run the code below i get the error message - object variable or with block variable not set-
Sub REFRESHXX()
'LIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
'SET RANGE
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing
With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With
'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar
'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1
End Sub
View 9 Replies
View Related
Sep 8, 2006
I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow
Here is my
Sub test()
Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long
'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
'for anyof the two lines down I get the message "object variable OR block variable not set"
lRow = rFoundCell.Row
lCol = rFoundCell.Column
End Sub
View 5 Replies
View Related
Nov 21, 2006
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
End Sub
View 9 Replies
View Related
Mar 24, 2014
I am getting error in Set MyRange
[Code] .....
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
Mar 12, 2009
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.
View 7 Replies
View Related
Oct 7, 2009
I am having a lot of trouble finding out why I am getting error. I believe the error is because it can't find the number. In cells C115:C314 i have the numbers 1 to 200 in order. when someone types in 1 to 9 in the text box it works, but on 10 and over i get the error ???? here is the code I have
shCalculator.Range("C115:C314").Select
Selection.Find(what:=CInt(txtPackageID), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
x = ActiveCell.Row
shCalculator.Range("ProposedMeter").Value = Cells(x, 7).Value
shCalculator.Range("Package").Value = Cells(x, 12).Value
shCalculator.Range("ProposedMeterAmount").Value = Cells(x, 30).Value
shCalculator.Range("Term").Value = Cells(x, 62).Value
shCalculator.Range("Discount").Value = Cells(x, 67).Value
shCalculator.Range("Equipment").Value = Cells(x, 72).Value
View 3 Replies
View Related
Feb 16, 2012
I want the select case list of a ComboBox to be treated as a variable in order to shorten the code size. To clarify the problem, i post the code with what i want to do, but don't know how to do it that way.
Code:
Select Case ComboBox1.ListIndex
Case 0: y = "AT"
For j = 0 To 26
Case "j": y = sheets("name").Range("A(j)") 'Range A(j) is a string, so y as well, as seen in Case 0.
Next j
End Select
Is something like this possible?
View 3 Replies
View Related
Jul 11, 2014
I have two macros that run fine separately. I want to join them, but when I do I get an Object variable or With block variable not set error.
Here's the code
Code:
Sub border_highlight_insert()
' Change heavy border to new column, change tint - keyed to year A6
With ActiveSheet
Dim i As Integer
[Code]....
View 2 Replies
View Related
