Dim lngrow As Long.
i want the above variable to hold the value of endxlup . so for example :
lngrow = Cells(.Rows.Count, "A").End(xlUp)
Why doesnt it hold the # of the last cell with data in column A ?
I have the following code in 'ThisWorkbook':
VB:
Private Sub Workbook_Open()
Call Meetdata
End Sub
It calls this macro which is in a standard module:
VB:
Public firstvariablename As String
Public secondvariablename As String
Sub Meetdata()
regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West")
meetdate = InputBox("Enter the date of the Meet.", "Date of Meet")
End Sub
This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .
VB:
MsgBox(firstvariablename & " " & secondvariablename)
At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]
I am using the following code to open a userform. Once the userform is open, the user has the option to choose a continue button or a cancel button. If the user is choosing continue, the code is not working (it always skips to the "else" portion of my "if" statement. I can't figure out why it is doing this.
View 4 Replies View RelatedMy code defines a variable: newrow. Then I call a subroutine using the Call command.
The subroutine does not recognize newrow.
It says it has value zero. How can I pass the value of newrow on to the subroutine.
I have two open workbooks, each having a single worksheet which includes a command button. The VBA code for each button performs tasks on its worksheet, then activates the other (inactive) workbook. I want to set the value of a variable in the button_click code in Workbook1 before activating Workbook2, and then use the value of the variable in the button_click code in Workbook2.
Declaring the variable as 'Public' doesn't work because the variable scope remains within its own workbook project. I must use two workbooks rather than two worksheets in one workbook.
I have a userform that is called within a sub in module. I declared a public string, "divisonb", in the module. When the userform's ok button is clicked, I define the public string through a "select case" method.
At the end of the private sub for the ok button click I have this:
[Code].....
a message box comes up with the correct string for divisonb. After the sub ends and it returns to the module I have the following:
[Code] ....
When this message box pops up, it is blank. Somehow, divisonb was redifined as blank within that 2 lines of code. All my other public strings are returned to the module with their correct values.
I have some code in which I need to pass the value of "j" from one module to another. "j" is declared in Sheet1 (Data) under the Microsoft Excel Objects.
View 8 Replies View RelatedThis might be a dumb question but is it possible to pass a boolean variable as a parameter to another function?
I have the following code that produces some compile error ("expected ="):
I am trying to pass a variable called "Filter" from a Private Sub to a Module but keeping coming up with a zero value in the Module. I tried to make the variable Global but that didn't seem to work.
Private Sub Code as follows:
I have a spreadsheet that has several buttons on that run code using the cells around the code for parts of the data. eg
ColumnA1 ColumnB1 ColumnC1 Button1
ColumnA2 ColumnB2 ColumnC2 Button2
I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)
I've been trying to pass a variable from the OnChange- event on one sheet to a macro located in module one, but it seems I cant (or havent figured out how anyway). Anyone knows how to do it? The code here give me the error "RefreshSetup(WhatSheet) cannot be found"
Alternativly, maybe someone can come up with a better solution on my entire problem. 3 sheets with webquery. All should be handled in the same way (copy-pasting only), but source and destination-sheet differs. My big problem is if two queries are done at the same time, they conflict with eachother, and data from one query is copied on to the wrong sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Running Then
RunWhen = Now + TimeSerial(0, 0, 1)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
Else
RunWhen = Now + TimeSerial(0, 0, 3)
WhatSheet = "HtmlTmp"
Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
End If
End Sub
Module1:................
I'm trying to pass a vector with in Strings (words) to a VBA variable, but something is not working.
That's how I'm writing: ...
I am having a problem with a run time 13 error on a variable assignment. The variable is being assigned a value from a cell that contains a formula, and I am suspicous that perhaps the mismatch is coming thru this. The de-bugger stops on:
Area = ActiveSheet.Cells(count + 41, 8). Here is the complete code (with all my poor coding skills!)
Sub Reservoir()
Dim Inflow As Single
Dim Withdrawal As Single
Dim Evap As Single
Dim Area As Single
Dim Spill As Single
Dim Stor_1 As Single
Dim Stor_2 As Single
Dim count As Integer
Dim max As Integer
max = ActiveSheet.Cells(9, 4)
For count = 1 To max * 12
Stor_1 = ActiveSheet.Cells(count + 40, 11)
Inflow = ActiveSheet.Cells(count + 41, 4)
Withdrawal = ActiveSheet.Cells(count + 41, 6)..........................
I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4.
But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.
If ComboBox2.Text = "Pounds (£)" Then
Sheets("Claims").Range("G16") = TextBox2.Text
Else
Sheets("Claims").Range("F16") = TextBox2.Text
End If
Sheets("Claims").Range("B16") = ComboBox1.Text
Sheets("Claims").Range("C16") = TextBox1.Text
Sheets("Claims").Range("B16") = TextBox3.Text
Sheets("Claims").Range("D16") = TextBox4.Text
Sheets("Claims").Range("E16") = TextBox5.Text
Dim a As Long
Dim b As Long
Dim answer As Long
a = Sheets("Claims").[E16]
b = Sheets("Claims").[F16]
answer = (a * b)
Sheets("Claims").[G16] = answer
I'm trying to pass a variable from Word to Excel. Basically I have a Word document with a plain text content control in it. I'll have users populate this field. I know how to create a reference to that content contol in Word VBA that'll tell me what's in that content control (eg.
Code:
MyWordVar = ActiveDocument.ContentControls(1).Range.Text
).
What I can't figure out is how to pass the value of "MyWordVar" to a variable in Excel.
I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly.
Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1
With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.insert Shift:=xlDown
End With
Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True
End Sub
I want to know how to index a drop down list to be used for calculations.
Up until now I was using
Range("B1").Select 'the cell where the drop down list is
ActiveCell.Formula = SiteID 'site id
But for whatever reason it trims a trailing zero from SiteID when setting it in the second line. SiteID is a VBA string.
Indexing it would work, as would preserving the zero so help on either one is appreciated. Auto Merged Post Until 24 Hrs Passes;Correction, I lied. I was using this line to set it.
Range("B1").Select 'the cell where the drop down list is
ActiveCell.FormulaR1C1 = SiteID 'site id
How do I pass values between procedures? I have two command buttons in the worksheet.
If I have a procedure A that asks user to input a value "x", when a command button is pressed.
Then another command button is pressed that passes the value onto another procedure B, which iterates a msgbox as many times as the number x,
How do I pass that value x from one procedure to another?
I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:
View 11 Replies View RelatedI'd like to know how to send a different value to a cell based on what is selected in the combobox. Like a vlookup, but from within the box.
Something like :
Passing Array to VBA. I have the following
View 2 Replies View RelatedI think this is a relatively easy issue, but I don't know how to do it. I call the Sub SortRange in another Sub SortDeliver. A range (ran5) is defined in Sub SortRange that I also need to use in Sub SortDeliver. How do I pass the range or get the second sub to recognize the range?
View 5 Replies View RelatedI've a userform (UserForm1) that on closing will open one of 5 other userforms. Which of the userforms that opens will be determined at runtime, depending on an option a user has previously selected. The name of the userform is held in a cell in a workbook and is opened as follows:
VBA.UserForms.Add(sheets("Sheet1").Range("A1")).Show
where the value in Sheets("Sheet1").Range("A1") is "UserForm3"
UserForm1 contains TextBox1. How can I pass the value in TextBox1 to a textbox in UserForm3 when the name of UserForm3 is held as a variable in a cell in a worksheet?
Option Base 1
Dim Covariance(1 To 5, 1 To 5) As Double
Dim Mean(1 To 5) As Double
Dim Portfolio(1 To 10, 1 To 2, 1 To 5) As Double
Dim PortfolioValues(1 To 10, 1 To 2) As Double
And my main function:
Function Generate_Portfolio_Values()
Dim a As Integer, b As Integer, c As Integer
Dim m As Integer
Fill_Covariance_Array
Fill_Mean_Array
Fill_Portfolio_Array
I have following code which identifies two worksheets, a source (wbSRC) and a destination (wbDEST). In the following code I am trying to get the values for wbsrc and wbdest to be written to the merged spreadhseet. But I am not sure how to go about this in VBA. The commented out approach does not work.
For Each rcell In wbList.Sheets(1). Range("A1"). CurrentRegion.Columns(1).Cells
Set wbDest = Workbooks.Open(rcell.Value)
Set wbSrc = Workbooks.Open(rcell(1, 2).Value)
wbSrc.Sheets(1).Range(copyAddress).Copy _ Destination:=wbDest.Sheets(1).Range(destAddress)
'wbDest.Sheets(1).Range("q3").Formula = "Workbooks.Open(rcell.Value)"
'wbDest.Sheets(1).Range("q4").Formula = "Workbooks.Open(rcell(1, 2).Value)"
I have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.
View 2 Replies View RelatedHow can I pass a column as a parameter when I execute the sub? ex:
View 2 Replies View RelatedI want to pass the name of the routine as a parameter.
View 6 Replies View RelatedI think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this.
View 3 Replies View Related I'm having issue with passing along a variable. I learned this morning how to pass these along. But for this instance something isn't working correctly.
The code is below:
I cut out all the subs in between so you can see the problem.I think it has something to do with "File search"
The problem is after sub New_Pro_Test runs then returns back to Sub RunFolder I get subscript out of range error. It is suppose to open the next workbook in that folder.