i got this at the top of my module:
Option Explicit
Public intLRow As Integer
Public strNetDrive As String
Public strDocName As String
Const strNetDrive = "\DriveDocuments"
Then i have this as a macro to test it:
Sub test()
Range("A1").Formula = strNetDrive
End Sub
When i run the macro i get this error:
I'm trying to create an array of arrays (a jagged array). According to Microsoft's VB developer center I can declare in the following ways: ...
View 6 Replies View RelatedI need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
where the number of rows and columns depend on other variables?
For example: In sheet 1, I have mentioned A = 2 and B = 3.
In the macro, I declare A and B to be integers. Then I try to declare a 2D array as follows:
Dim AB(A, B) As Integer
During compiling, it gives me an error: "Compile Error: Constant Expression Required" with the above line highlighted. What am I doing wrong?
Also, when I declare an array AB(4, 7), 4 is the number of rows and 7 is the number of columns, right?
I tend to use alot of sub but am finding I need to declare variables each time
for example
Sub Macro1
Dim i as integer
i = 10
some other code...
end sub
Sub Macro2
Dim i as integer
i = 10
some other code...
end sub
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
I'm having trouble creating a macro that will look at a single URL, and based on what text is in a cell, replace that name and put it into a specific location in the URL. Then the URL will open and the rest is a macro I've written already. This will then be done as many times as need as long as there is text in the column.
So I'm looking to declare the text in these cells as different variables, then have something call these variables into a specific location in a URL, then have that URL (which is a worksheet location) open up. This is because I have many URL worksheet locations on an external website, I'd like for my macro to look at the cells in column A, check "A2" and whatever that name is, and have it placed into the URL. I assume some type of loop would be useful, since I do not want them to open up all at once, but one by one based on the text or variable in the column. Then the URL will open.
Then I can run my macro code which will include copying and pasting, closing and saving. Then go the next cell, check "A3" then repeat the process. The URL for all the worksheets are all the same, except for one specifc name, which is what im looking to replace one by one.
Can anyone tell me how to have a variable which can store leading zero's. I am using this variable and part of a string and want it to be able to store and calculate numbers such as 0005 at the moment if such a number is entered it sees it as 5. At the moment I have declared the variables as integers but clearly this is incorrect.
View 9 Replies View RelatedI am trying to make sense of quite an elaborate macro built by someone else. This has the following line of code
Code:
Public Sub readOperation(rowNumber As Integer)
Dim rowOff As Integer
rowOff = rowNumber - 1
'other arguments and conditions
End Sub
What I am buggered about is that I cannot see the the variable rowNumber being declared anywhere but it seems to have a value of 1. I should mention that Operation is a class and I am very new to classes.
Using excel 2003
What is the proper declaration for a decimal, I’ve been using currency because it has two decimal points i.e.
Dim x as Currency ...
I have a worksheet that has 2 sheets. I want to declare a variable that can be accessed for reading and/or overwriting by both of those sheets. Where do I declare it and how do I access it from Sheet1 for example?
View 4 Replies View Relatedhow do you declare public variables? In one of my forms I declare the variables but for some reason can not access them in other forms.
Public Rm_new As Integer
Public Angle_new As Integer
Public Pl_new As Integer
Public Sub cal_graph_Click()
'Stating variables
Rm_new = text_rm.Text
Angle_new = text_angle.Text
Pl_new = text_length.Text
Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node1 to noden using the dim statement. I tried running a for loop:
View 14 Replies View RelatedRight now in cell AE1 the value = "SUM", but the word "SUM" floats in row 1
I'd like a macro that looks through row1, and gives me the column letter that the word "SUM" resides in
sCol_1 As String
sCol1 = (Where ever the word "SUM" is located in row 1)
not sure if this is the best way.
Prior to declaring a variable i used this code and it worked:
Sheets("442000ON-1+6").Range("C7").formula = "my formula"
range(C7).select
Selection.AutoFill Destination:=Range("C7:N7"), Type:=xlFillDefault
Than i declared a variable b/c i have a number of formulas i need to use but still needed it to fill the formula in a series after the formula ran. I started getting an error.
Fix was:
Dim formula1 As String
formula1 = "=SUMIF(Pivot!$A$6:$A$108,'442000ON-1+6'!$C$3,INDEX(Pivot!$B$6:$BP$106,0,MATCH('442000ON-1+6'!$B$1,Pivot!$B$5:$BP$5)))"
Sheets("442000ON-1+6").Range("C7").formula = formula1
Range("C7").Select
Set SourceRange = Worksheets("442000ON-1+6").Range("C7")
Set fillRange = Worksheets("442000ON-1+6").Range("C7:N7")
SourceRange.AutoFill Destination:=fillRange
i'm trying to develop an array that is populated when a userform is activated. The userform has a command button that when pressed, will cross check the information filled out in the userform with the entries in the array. If there are no matches then the array is ReDim and the new information is added to the array. Once all the entries have been made. The array is then transposed to a sheet titled "Database". My problem is that vba is not allowing me declare a public array.Below is the first part of the code. Which is when the userform is activated.
Private Sub UserForm_Activate()
With Sheets("Resources")
cbZIP.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
cbBED.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
cbBATH.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
cbSTABRV.List = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value
End With
[code]....
I am trying to compare two workbooks and am running into the problem that since I declared my two workbooks, I am limited in the methods that I can use. Here is all of the code so far.
Option Explicit
Sub UpdateMasterFile()
Dim wbMaster As Workbooks
Dim wbEmailed As Workbooks
Dim wsPC As Worksheet
Dim Master As Long
Dim Emailed As Long
Dim intMaster As Integer
Dim intEmailed As Integer
Set wbMaster = Workbooks("Master Info.xls").Sheets("PlantsCom")
Set wbEmailed = Workbooks("EmailedData.xls").Sheets("NewInfo")
Master = Workbooks("Master Info.xls").Sheets("PlantsCom"). Range("a65536").End(xlUp).Row
Emailed = Workbooks("EmailedData.xls").Sheets("NewInfo").Range("a65536").End(xlUp).Row..............
Using Excel 2007, I'm trying to figure out (and not succeeding!) on how to declare worksheet and range - in order to:
input a formula to cell xfd1, then copy down to xfd2:xfd100 - and here's what I've been playing about with
Code:
Sub filldownxfd()
Dim src As Range, out As Range, wks As Worksheet
Dim sRangeName As String
Workbooks.Item(1).Sheets.Item ("Sheet1")
Dim example As Range
Set example = Range("xfd2:xfd100")
[code]....
Users select a row number which then opens up a Userform. Comboboxes and Textboxes on this Userform are then populated with coloumn values (dependant on the row initially selected). If the row number was the same each time I could accomplish this by the following.
Private Sub TextBox1_Enter()
TextBox1.Value = Range("a1")
End Sub
but as its a variable I'm trying the following (which I think should work)
Private Sub TextBox1_Enter()
TextBox1.Value = Range("a" & edi & "")
End Sub
I think I need to declare the variable 'edi' as Public. If so where should this be? I've tried 'Genereal Declaration'
I have two string that I want too compare.
String 1 could be something like channel_0_user_defined._system
string 2 could be channel_0_user_defined
I want too compare string2 with string 1 at the left side of string 1.
I don't really know what I should use I found startswith, but vba doesn't have this particular function.
For example: I want to sort through a a bunch of textboxes and refer them to certain cells. I know I can have a string "TextBox" and then the looping number "nbr" and merge them into for instance TextBox1. But I can't figure out how to use this as a command. Like
View 2 Replies View RelatedDifferent formula in different cells give me some text as output, to format this. I would like to copy and paste in one cell, so that I can easily then register a macro on top to wrap the text in one big cell.
View 1 Replies View RelatedI'm trying to insert a set of strings, and for some reason its not going to the next open row, its just inserting them over the last record.
Code:
Private Sub Insert_Payment_Click()Dim Payment As String, PaymentAddress As String
Worksheets("Payments").Select
Payment = Range("E4")
PaymentAddress = Range("B4")
Worksheets("RentalDates").Select
Worksheets("RentalDates").Range("I1").Select
If Worksheets("RentalDates").Range("I1").Offset("1,0") "" Then
Worksheets("RentalDates").Range("I1").End(xlDown).Select
[code]....
I am trying to parse employee names into seperate string variables in order to display surname, first name and middle name in seperate fields on a form.
The format of the full name is "Surname, First Name, Middle Name (If any)
I have managed to deal with surname and firstname but I am stumped on middle name. Not all employees will have a middle name but for those who do, I need to parse it.
Aside from using arrags, is there a simple way of setting up a loop statement involving words?
View 9 Replies View RelatedI'm getting this weird error which must because I've overlooked some simple thing about formatting (is it called formatting?) my variables.
See, what I think is wrong is that I'm comparing a string with a range and that can make 37 not equal to 37.
Because that is my problem; I have an If statement that compares 2 variables that I know should be the same but the If statement doesnt think so. So to my question; am I on the right track here and if so, how should I tell vba what is a string and what is a range and if I am on the wrong track here;
I am trying to compare two cells to see if its two values are the same. Some of the cells have a space in between numbers or a symbol like "-"
Ex.
one cell's value is
1213180IN
and the other cell's value is
1213180-IN
the program I have written marks these as different because of the "-" in the second cell....is there a way, maybe with a string function to overlook that "-"?
I've got above basic VBA knowledge and learning very quickly (largely due to great forums like this) I'm now creating a userform for the first time. I'm trying to create a New Supplier form and one of the fields is going to be for Supplier ID where it takes the first 6 letters of the Supplier name (entered by the user) & tags on '001' at the end. I've managed to do this but I would like some code to search existing supplier ID's ( Column A on separate sheet) and check whether the ID created already exists and if it does add on '002' instead of '001'.
SO for example..
If user creates a supplier called Microsoft the supplier ID will be Micros001 - this I can do, but if Micros001 already exists then it will generate an ID
Micros002, Micros003 and so on...
I have two adjacent strings in a CSV file that I'm importing which were originally intended as a single piece of data. The format of the strings are:
5'-3 3/4" (Yes, it's a good old imperial length measurement)
Right now, I have (5'-3) in one cell, and (3/4") in the next.
I'd like to try to append the second string (3/4") to the first in a single cell. Is there any way for me to do that using VBA?
I'm just trying to view the results of this search, I don't need it to make changes to the cells on the bases of this search.
I'm using control + F to open the search box.
It only allows me to type in one single string, such as "Johnson". Problem is, it finds many rows that contain the name Johnson.
So I'd like to further add a number, such as 1.3338, which it can look for in any cell that's on the same ROW where it found Johnson.
It would then return, for example, Row number 81, which has a cell that contains Johnson and another cell that contains 1.3338.