I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.
View 1 RepliesI am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.
Say I have 3 worksheets for arbitrary measurements:
1) filename 1mm
2) filename 2mm
3) filename 3mm
I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.
When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.
I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.
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 RelatedSay 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.
I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.
View 2 Replies View RelatedI would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
I am wondering if anyone as created a what to do word art, using a input variable.I.e in may example I have a person, Suzie Smith who is an Chief Manager, when I box requires her signature, I would like for it eo be imprinted going diagonal across the box. Is this possible?
View 7 Replies View RelatedColumn A will be completely empty.
When an * is inserted into a single cell in column A, that Row number will become the variable in this equation:
=Sheet1!$G$'variable'
i.e. if i put a * in A14, then the formula should read (=Sheet1!$G$14).
How would I create a variable range with:
Sheets("output").Cells(I + r, 1):Sheets("output").Cells(I + r, 9)
I currently have the below macro:
If Cells(1, 3) = 2 Then
For a = 1 To 336
b = Round(a * 2 + 0.99)
Cells(b + 0, 3) = a
Cells(b + 1, 3) = a
Next a
MsgBox " Cell C1 is not equal to 2"
End If
where the intent is that whatever cell A1 is equal to, that is how many times over it will duplicate a number in consecutive cells before moving to the next number, ie:
1
1
2
2
3
3
etc to 336.
How can I tweak the code so that if I change the data in cell A1 to 9, I don't have to go in and change the macro?
I'm trying to use the ActiveSheet Name as the folder name for images being imported into excel. For example, on the Sheet "Sample 1" I want to pull from the File Path "C:Documents and SettingsuserDesktopSample 1"
I'm getting an error, I think this code should work in regular VBA not sure whats wrong in excel?
Code:
Dim strSName As String
strSName.Text = ActiveSheet.Name
Const strPath As String = "C:Documents and SettingsuserDesktop" & strSName & ""
Needing to do a VLOOKUP on two criteria, I have set up a string using "&" to join together the contents of two cells. This is in a workbook with many identical worksheets. The string works for some of the sheets but in others, for reasons I simply cannot fathom, it is returning a #VALUE! error.
View 9 Replies View RelatedI'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:
='F:Projects[CustomerDB.xls]Sheet1'!VariableCellGoesHere
I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:
='F:Projects[ProjectDB.xls]Sheet1'!$A$355
='F:Projects[ProjectDB.xls]Sheet1'!$B$355
='F:Projects[ProjectDB.xls]Sheet1'!$C$355
Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?
I am trying to create a matrix in VBA with variable matrix size. The matrix I need will be anywhere from 3 to 9 columns and 15 to 30 rows. I want to set the size as variables and then create the Matrix using these variables, similar to:
rows = 4
columns = 25
Dim XMatrix(rows, columns) As Variant
I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?
View 6 Replies View RelatedIs there a formula that can be used to convert a manufacturing LOT Number to a Date? For example, the first six digits of our Lot Number are the MMDDYY followed by an item description (120913DKP-01). We are trying to pull the 120913 portion out of the lot number to equal a Date of Manufacture of December 9, 2013.
View 4 Replies View RelatedI am having some difficulty with a pdf that I converted to an excel document because I wanted to use the data from the pdf tables in a different program I am currently working on. However, the data is in the improper format. For example, in the table it reads 2-1/8 as string and I want it to be the number value 2.125 . Likewise if the value in the table reads 5-1/4 I want it to automatically convert it to something that will be read as the number 5.25
View 4 Replies View Relatedhow do we do something like Range("A1:B2") using an index for the cells. For example Range(R[0]C[0]:R[i]C[j]) which doesn't work. I know about Cells(i,j), but that only returns a range object of a single cell.
View 9 Replies View RelatedI recorded what I wanted but don't know how to generalise it so regardless of sheet name it can be sorted instead of specifically looking for "leanne final test" worksheet and sorting it by precise cells.
I have starred the section out so you don't have to focus on the rest of the code. Initally I ask for the file to be opened using WeeklyFN, would I need to use it again somehow ?
I have an endless list of information that I need to turn into a text string, lookup with a validation on the end.
The easiest way is to show you so I've attached the info and what result I want.
Attached File : Test.xlsx
Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?
For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.
I have a strange problem I cannot solve in that I'm trying to create an auto filter that returns records based on a date variable.
If I set the code to equal the date variable, it returns information. If I set the code to return records AFTER the date, it returns nothing.
Code to return records on the date:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="=" & startdate
This returns data, however if I slightly amend it to get records on or after the date:
Code:
Dim startdate As Date
startdate = Format((Now), "dd/mm/yyyy")
ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:=">=" & startdate
It returns nothing. Is there a syntax problem in my code.
I understand that a everything is already considered to be string, but how would I phrase this?
If txtBolow = (a statement which indicates the textbox is full) Then
txtBolow. SetFocus
txtBolow. SelStart = 0
txtBolow. selLenght = Len(txtBolow)
If txLowl = ("") Then
txtLowl. SetFocus
txtLowl. SelStart = 0
txtLowl. selLenght = Len(txtBolow)
What would I use to indicate that the user has completed the data in the textboxes, and the old data is highlighted to make way for the new?
I am trying to use a variable for the name in the Sheets command so that I can put in the names of the CSV file that I have captured in a variable. Here is the macro that I recorded using Excel 2007:
View 4 Replies View RelatedI believe I should use the INSTR function, but I simply dont understand its use very well. I have a string in a cell, "MEAS/SPHERE,F(QUA_1),5", the characters I need start at the "(" and end at the closing ")", ref: (QUA_1). The start and end will always be the same, the length of characters and the spelling of characters be anything. Thus, QUA_1 can be CAL_1, CAL_2, SOMEOTHERTEXT_1 or 187bgo_199, etc, always enclosed in "( )". I wish to place the characters and the closing "( )" into a variable as a string.
View 5 Replies View RelatedCALIB/SENS,S(S5a90b-90),FA(CAL_15)
This one has two sets of quotes, with the information from the noted post I can retrieve the string "S5a09b-90", now that I have a double set of brackets "( )" how do I get the value for the second set of brackets, "CAL_15"?
In the quoted string, CALIB/SENS,S, is constant. The first set of brackets are constant (string length is not), the second set of brackets are constant (string length is not).
It's been a long time, but I use to parse a string in C.
How do you strip off the long directory path of a string to just the filename and extension?
String Example:
S:2006 jobs602f EID Bass Lake Recycle BPSConstructionSubmittal Review16940-Instrumentation602f-16940-01.rev.doc
Wanted result:
0602f-16940-01.rev.doc
if it is possible, to use a string variable in a Formula via VBA.
For example: ActiveCell.Formula = "=SUM(Sheet3!strVariable)"
Of course the code is not working that way. The Variable has the following structer: CellAdress;CellAdress;CellAdress... so it would perfectly fit into the formula-syntax.
Is there any possible way to do that? The reason behind it is, that I have to deal with a flexible range and I want to process this range with a formula. Actually the range is flexible, because of different filters and I only wanna count all numbers in the unhidden rows.
My varibale would then contain all celladresses, for the visible cells.
So in a nutshell: is it possible to somehow use a varible in a formula?
I have a string variable contacting a file.
I want to set a workbook variable to this file, WITHOUT actually opening it!
ie:
DIM wb as Workbook
DIM filenm as String
filenm = "C: empdata.xls"
set workbook = filenm
============================================
So I know this won't work, but is there anyway around it? (bearing in mind I don't want to open the file!
Just a quick question, is it possible to use a string variable in a Excel formula (in VBA)? Im trying to do the following in VBA
mortgageSchedName = acName & " Schedule"
Range("B6").FormulaR1C1 = "=mortgageSchedName!RC[254])"
the above results in an error because there is no such sheet as "mortgageSchedName", but I want it to access the value of that string variable.