String Variable To Call Defined Range Variable
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
ADVERTISEMENT
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
Jan 8, 2014
I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.
The value is returned to CurrentRange, but it's the last line that's giving me the problem.
Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String
CurrentRange = Range("AG3").Value MsgBox (CurrentRange)
'This returns the value [N7:N75], which is what I want
Set UtilizationRange = Range("CurrentRange")End Sub
View 1 Replies
View Related
Apr 28, 2014
I have a long string that has a list of digits in the middle that I want to extract. The string is variable length and the number of digits I want to extract is 5-7. I also have slashes in between the numbers I want to extract. I need a UDF that allows me to extract the 5-7 digit number from the string and restrict around the slashes (i.e. if two sets of digits in the string match the condition for extraction, extract the one around the slashes.) For example my original data is like
1. aaa/12345/aaa/123
2. aaaaa/123456/aaaaaa/3423
3. 323/aa/1234567/aa
and I want for results
1. 12345
2. 123456
3. 1234567
Is there a UDF that allows me to do this?
View 14 Replies
View Related
Jul 31, 2014
I have a spreadsheet with column headers like, "Date" "Phone number" "Name" ect., and rows of data below. Each of these rows contains data pertaining to a prospect employee, as the purpose of the spreadsheet is to keep track of job applicants. One column is used as a 'Recruiter Sign-off' area, where the recruiter who processed the application inputs their name:
Example.png
I'm trying to automate a variation of excels sort function. However, I don't want to sort 'From A to Z.' Instead, I want to have the rows of data sorted with the 'Recruiter Sign-off' column used as the sort Key, and a selected name (of one of the recruiters) used as the sort Criteria.
I already have a userform that allows the user to select a name from a listbox. When they press confirm, the name is stored in a Public String variable, (selectedRecruiter). I'd like then like to have the rows below the column headers to be sorted based on the selected name. Example: User selects the name "Jon" from a list. The application then brings all of the applicants that 'Jon' has signed off on (those rows that contain his name) to the top rows of the range.
The workbook is shared, so I cannot use tables.
I found a code that accomplished what I want, however it also leads to a horrible system crash after a few times running it (BEX crash).
[Code] ..........
View 3 Replies
View Related
Mar 13, 2012
I am trying to name a Range in VBA with the following Case statement.
Select Case counter
Case "2": Set Wk(numWeeks) = Range("P1:Q136")
ActiveWorkbook.Names.Add Name:="Wk" & numWeeks, RefersTo:=Wk(numWeeks)
I have many more cases and have defined Wk(5) as a Range. What I am really after it is possible to to name the range using something similar to the code above that uses a predefined variable in the naming process.
View 4 Replies
View Related
Feb 7, 2014
I am trying to run different macros by clicking various different buttons on the sheet, I then want different data to load into the user form depending which button was pressed. So I have buttons named "SV_1" and another named "SV_2". when either button is pressed then it runs a common macro that gets the name of the calling item. then I want to add "Macro" to the beginning of the calling item name and then call that macro. here is the code that I am working with, when using a watch i can see the value of the variable is "MacroSV_1" when button 1 is pressed but I cannot get it to run the Sub.
VB:
Public ClkBtn As String
Public CallMacro As String
Sub ItemCall()
[Code] ......
View 5 Replies
View Related
Aug 12, 2008
I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:
If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
Etc, Etc, Etc
What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t
I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?
View 9 Replies
View Related
Sep 8, 2007
I have 46 drop downs that I need to call from a main sub. I want to create one loop that will call each one to perform its function. Below is a simple example of what I am trying to do, but where I need help is with the 'Call Y' line. I am not sure what character to use to tell excel to look at what is in the value of Y, not 'Y' itself
Sub testitout()
Dim y As String
For X = 1 To 2
Sheets(" Lookup"). Range("E3").Value = X + 4
y = "dropdown" & X & "_change()" 'this will return "dropdown1_change()" for the first loop
Call y 'i want to call dropdown1_change() below
Next
View 9 Replies
View Related
Apr 4, 2008
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies
View Related
Feb 23, 2013
I'm trying to call another sub with a variable string. I'm using excel 2007. I get the error "Compile Error: Expected Sub, Function, or Property" on the "Call" code. The want the code to run multiple different subs based on user input. For example if they typed "this" it would run the t sub, then h sub, then I sub and finally the s sub.
Code example
Dim Uncvrtdtxt as string
Dim SubName as string
Dim i as integer
Uncvrtdtxt = "this"
For i =1 to Len(Uncvrtdtxt)
SubName = Mid(Uncvrtdtxt, i, 1) & "Route"
[code]....
View 7 Replies
View Related
Jan 18, 2007
I have a code below which need some input from user. This input will also be serve as the input of the subroutine which i am going to call. However, i do not know how to go assign this input to the subrountine which i will be calling, can anybody help ?
For example, the "input" variable will also be served as an input in subroutine test2 ...
View 6 Replies
View Related
Feb 3, 2009
The attached file contains a simplified version of a more complex macro in which a sub routine will be called a number of times to change the colours of cells in different ranges. However, an error is triggered because the a variable and range are not defined in the subroutine although they are in the main part of the macro.
I know I am missing something obvious here, but I'd appreciate any help in knowing how I can define the ranges in the main procedure and then call the subroutine to change values in the different ranges.
View 6 Replies
View Related
Jun 19, 2008
I have this structure:
Sub macro()
Dim columnSheet2 As range
With ThisWorkbook
Set valueCell = .Sheets("sheet1").range("B2")
Set timeCell = .Sheets("sheet1").range("J2")
End With
If timeCell.Text "#N/A" Then
myHour = Hour(timeCell)
myMinute = Minute(timeCell)
'#######################
If (myHour = 5) Then
If (myMinute = 0) Then
Set columnSheet2 = A65536
...
if I run it it gives me an error here
Set columnSheet2 = A65536
saying variable not defined, why?
View 10 Replies
View Related
Mar 3, 2007
The master has rows corresponding to numbered files:
4300 | CellValue1 | CellValue2 | etc
4301 | CellValue1 | CellValue2 | etc
In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following:
='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1
This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself.
Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.
View 6 Replies
View Related
Sep 23, 2013
I'm getting a Variable Not Defined error
in my workbook_open I have the following code
Code:
Dim workround As Integer
workround = 0
in one of my sheets worksheet_change I have
Code:
If workround = 0 then
when I get to this I get the Variable not defined error. I am using this to bypass some code that does not always need to be run (it slows the workbook down). So I also have another piece of code that toggles workround from 0 to 1.
I have a feeling this error has something to do with Option Explicit. But My limited understanding is obviously insufficient to know what is happening or how to fix it. As I believe that I have defined the variable within the workbook_open code.
View 3 Replies
View Related
Mar 7, 2008
I have a macro which defines a workbook selected by the user as the name ServicingInfoFile.
The macro then proceeds to write vlookup formulas in another workbook to pull data from the ServicingInfoFile workbook.
Only problem is that the code errors out at the vlookup formulas every time.
Can I not use a Variable in a formula?
Is it just my syntax?
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'" & ServicingInfoBook & "'!C1:C2,2,FALSE)"
View 9 Replies
View Related
Oct 20, 2008
I have some code which defines a variable 'F'
View 9 Replies
View Related
Jun 13, 2008
Sub addInterestingDataPoints(myChart As chart)
'code goes here
End Sub
The above code is causing a Compile Error: variable not defined (highlights the sub heading line).
View 2 Replies
View Related
Mar 11, 2008
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
View 3 Replies
View Related
Jun 27, 2014
I want to be able to call one user form from multiple ActiveX command buttons. The problem is, the number of command buttons depends on user input on another worksheet, so it's variable.
I've renamed all of the command buttons so they are named "CommandButton" & i, where i is an integer between 1 and, say, 200. I want each of these buttons to direct to the same UserForm where additional information can be entered.
I can't think of a way around the event-handler procedure name.
Sub [Command Button Name]_Click
to call the User Form. I won't know the command button names, because I won't know how many there are (max i) until the user inputs.
Basically, I want to create a For loop through the max i and have the event-handlers call the user form
View 14 Replies
View Related
Feb 15, 2012
I am acquiring multiple spreadsheets that do not always match row number, due to additional information on some sheets. By this I mean that the information may be in row 31 on on sheet and row 39 on another, the column location is the same each time. I have been trying Vlookups, indexing and matching plus combination formulas - with no luck. How to get the information I need with a moving cell reference?
I can provide a small copy of a workbook, if needed.
View 1 Replies
View Related
Aug 12, 2014
New to VBA and just trying to make some edits to some existing code. I have basically copied a pre-existing form and module and changed names from "Appendix" to "Drawing" as I am trying to replicate what the piece of code already produces for a table of appendices, for my drawings.
However, when I try and run the form I get Compile error: Variable not defined with lstDrawings highlighted.
[Code] ...........
Is this something that should be defined in my global module which I am missing?
View 3 Replies
View Related
Jan 21, 2012
The following code has been used previously to enter data from a userform to a worksheet without a problem. However, since I added some new bits of code I am getting a compile error with the message variable not defined.
Here is part of what I have so far and the bit that is highlighted after the error comes up is the 'Set ws' line;
Code:
Private Sub CommandButton1_Click()
Worksheets("Duties").Range("C5") = txtdate
Worksheets("Duties").Range("H5") = txtarea
Worksheets("Duties").Range("N5") = txttea
Set ws = Worksheets("Duties")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
The code I have recently added:
Code:
Private Sub UserForm_Initialize()
Set wsRes = Worksheets("Resources")
With wsRes
.Range("B1", .Range("B" & Rows.Count).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("L1"), True
With .Range("L2", .Range("L" & Rows.Count).End(xlUp))
cboTeam.List = .Value
.EntireColumn.Clear
[Code] .......
View 8 Replies
View Related
Jul 15, 2013
I am creating a UserForm to get macro running options from the user. The Workbook__Open calls the macro which shows the UserForm right after the variables are defined. Is there a way to avoid the compile error?
07.15.2013-14.15.54 - Bermex's library
View 1 Replies
View Related
May 27, 2008
I've used Excel for many years, but finally am learning VBA, using Excel Progamming for Dummies by John Walkenbach. Excel is version 2000.
I've searched extensively on Google and on the MrExcel site for the solution to my problem, but still haven't found it . Any direction would be most appreciated.
I'm trying to execute the examples in the book, and have gotten the error message "Compile error: Variable not defined" with "MyString =" highlighted with several of the examples. Could someone please tell me where I'm going astray?
Following is the latest subroutine I typed into the module, from page 124:
Option Explicit
Sub GetLength()
MyString = "Hello World"
StringLength = Len(MyString)
MsgBox StringLength
End Sub
View 9 Replies
View Related
May 6, 2009
I keep getting 'compile error - variable not defined' in the following
Sub LockIt()
TextBox1 = ""
End Sub
View 9 Replies
View Related
Jan 13, 2010
It works as a regular Module but when I put it into a "Private Sub" for a button its not working.
Basically I have a master sheet that I create Tests from. I push a button and it creates a Test for others to take. I want the macro to transfer from the Master to these so when they are done I can press a button and upload the answers to a tracking sheet.
It errors on the strSecondFile
Private Sub CommandButton1_Click()
Dim wbk As Workbook
Dim NAME As String
'strFirstFile = ActiveWorkbook
strSecondFile = Application.GetOpenFilename
Set wbk = ActiveWorkbook 'Workbooks.Open(strFirstFile)
NAME = Range("A6").Value
View 9 Replies
View Related
Jul 18, 2006
I came across a weird situation using a standard print macro. One of my users could not use the print macro due to a compile error. The row that was highlighted was
.PrintErrors = xlPrintErrorsDisplayed
the error states that the variable is not defined. I removed that line of code and everything prints fine.
I tested this code out on excel 97 and it worked; is this sensitive to OS version? Will leaving this line of code effect anything big?
View 9 Replies
View Related
Oct 10, 2006
I keep getting a "complie error: variable not defined" and I am not sure why (the error highlights the first instance of "sh"). Any feedback? I am trying to test a value of a cell for each row and if the row meets the test then it increases the value in another cell by 1.
This is what I have so far...
View 5 Replies
View Related
