Difference In Two Methods Of Referring To Other Workbooks?
Feb 22, 2014
These workbooks are located in different folders.
The first is pasted "as link" from two open instances of Excel and is an array formula.
The second is just pasted from another open workbook.
{=Excel.Sheet.12|'xx.xxxFolderFolderFilename.xlsx'!'!SheetName!R2C9:R1500C22'}=IF(H4="N/A","N/A",VLOOKUP(A2,'I:FolderMore Folders[FileName.xlsx]Sheet'!$A$1:$T$35,13,FALSE))
What is the difference between using these two different methods?
View 1 Replies
ADVERTISEMENT
Jan 27, 2009
Sub vocab6()
Dim fname As String
fname = "c:Documents and Settings
obertDesktopvocab.xlsx"
Workbooks.Open Filename:=fname
Workbooks(fname).Worksheets("tangible nouns").Range("A1").Value = 9
End Sub
I just want to open a workbook saved on my desktop (from a different workbook), and insert 9 into the first sheet (which is named tangible nouns).
View 9 Replies
View Related
Oct 5, 2009
I'm trying to use VBA to write from one workbook to another. I'm trying to populate other workbooks from a single 'control' workbook with a template.
I've managed to figure out how to open up Excel workbooks within a directory (this needs to be done multiple times) supplied by the user, then retrieve information from it to be collated, but I can't for the life of me figure out how to write to the file that I've opened.
I've got the code below, and it's very scrappy as I've been trying lots of different things but nothing's worked. I'll try and highlight as best as I can where I'm having difficulties, as I get the "object not defined" or some such error.
View 12 Replies
View Related
Apr 10, 2013
I am developing a VBA for Excel application which uses transient worksheets to collect data from users before writing the data back to master spreadsheets.
My application opens an instance of excel with a workbook and worksheet using the following code:
Dim xlsInstance As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set xlsInstance = CreateObject("Excel.Application")
Set wkb = xlsInstance.Workbooks.Add
wkb.Activate
Set wks = wbk.Worksheets.Add
wks.Activate
I can reference various ranges in the worksheet and do most of what I need but I need to override the Worksheet_Change method. Easily done for static sheets, but this must be set at runtime when the worksheet is created.
View 9 Replies
View Related
Sep 4, 2013
Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.
Code:
Sub DBPreferredWay()
folder = ThisWorkbook.Path & "mydatabase.mdb"
Set db = OpenDatabase(folder)
strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus
Set tR = db.OpenRecordset(strSQL)
[Code] ........
I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb
Code:
Sub SqltoACCPAC()
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password"
strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]
View 8 Replies
View Related
Feb 28, 2014
I have thousand rows of data in the following format:
Despatched Time
Batch
Date
Time
Amended Time
20/4/2013 3:45:00
1
20/4/2013
03:45
[Code] ..........
'Date' is generated by =IF(A2="", "", TRUNC(A2))
'Time' is generated by =IF(A2="", "", IF(E2 = "", A2 - TRUNC(A2), TIME(LEFT(E2,LEN(E2)-2),RIGHT(E2,2),0)))
And sometimes I may want to change the Time, so I used another column 'Amended Time' to insert a 4-digit value to do so.
I want to arrange them into batches according to the hh:mm, so for the column 'Batch', I used the following formula:
=IF(D2 = "", "", IF(EXACT(D2, D1),MAX(B$1:B1),MAX(B$1:B1)+1))
The problem is, when I changed the time by inserting 4 digits in 'Amended Time', the time value is actually different from those generated by TRUNC(), so even two cells have the same time '03:46', the time value is not exactly the same and so they are arranged into different batches (please refer to the following table).
Despatched Time
Batch
Date
Time
Amended Time
20/4/2013 3:45:00
1
20/4/2013
03:45
[Code] ..........
View 9 Replies
View Related
Aug 11, 2007
Instead of typing out my code hundreds of times, I would like to replace it with an abbreviation or variable(?). Here is my example. If I have this in the wrong place please let me know. I want to replace Activecell.Interior.Colorindex with ACIC. I tried to Dim ACIC as range, variant, string...to no avail. In combination I Set ACIC = Activecell.Interior.Colorindex and that didn't work. I have been toiling with VBA for a year and I am still trying to learn the terminology and understand it so please forgive me if I seem really NOOBIE. :-)
View 9 Replies
View Related
Jun 18, 2006
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..............
View 2 Replies
View Related
Aug 12, 2006
I am trying to implement code that can handle a dynamic number of objects, specifically check boxes.
I know of eval( ) in perl that allows one to dynamically create command lines. For instance, the following code would change all of the Checkbox values to 1. The syntax may not be correct but the idea is solid.
Do While i < num_checkboxes
eval("Checkbox" .i. ".Value=1)
Loop
Does this funtion also exist in excel?
View 6 Replies
View Related
Aug 16, 2007
I need to learn the syntax and all options I can use with the Selection function - where can I find that info? I can't find anything in in Visual Basic Help (I find microsoft help is generally totally useless).
For example, in the code below, what are all the options I can use after "Selection. " (ie .Copy, .End, .Select) and what do I use if I want to select a certain number of cells to the left or to the right or up or down?
Sub Fill()
Selection.Copy Destination:=Range(Selection, Selection.End(xlDown).Offset(-1))
Selection.End(xlDown).Select
End Sub
I'm just noticing now that the "(xlDown)" is X L, not X 1, which I thought might make sense to say "1 time Down". What does the "xl" stand for?
View 7 Replies
View Related
May 27, 2014
how to calculate averages and standard deviations based on different time periods without having to manually change the cells?
example:
1st average output at z3, 1st std dev output at z4
data to calculate from c3:c50
2nd average output at z5, 2nd std dev output at z6
data to calculate from c51:c98
3rd average output at z7, 3rd std dev output at z8
data to calculate from c99:c148
and it goes on based on this sequence. i would like to know how to do this without having to change the cells each time i want to calculate. basically what is the quickest way to calculate following this sequence?
View 6 Replies
View Related
Jul 19, 2014
I am doing a spreadsheet where I have 1 main sheet and 2 sheets with data.On the main sheet I have a drop down list to select either one of the 2 sheets and upon selecting,all the data from that selected sheet will be displayed on the main sheet.
What i do not understand is the code given below
data:=IF($C$7='A2'!$A$2,INDEX('A2'!$A$6:$B$20,ROW( $A3),COLUMN(B$1)),
INDEX('A1'!$A$6:$B$20,ROW($A3),C OLUMN(B$1)))
How do i attach spreadsheet in this forums btw?
View 7 Replies
View Related
Feb 11, 2009
some of your favorite Methods/Properties exposed by the Range Object. How's bout some that you just can't live without?
Have an tricks for example that you can do with XLS03 that for example couldn't do with XLS2K or perhaps XLS07 that you couldn't do with XLS03?
View 9 Replies
View Related
Jun 17, 2009
I am trying to refer to a cell. Not sure if thats a good way to put it.
Example: I am in Cell B1 and I want to have a formula that says if C2 is 5 then A1 is 5, else B1 is 500.
I need A1 to be completely blank with the formula only in B1 and C2 has the number typed in it.
How can I put a number in A1 without having a formula or any text in that cell (A1)?
View 9 Replies
View Related
Nov 5, 2012
So far I have 2 Functions with a sub to test to do certain calculations.
VB:
[B]Private Sub test_getBaristaSalary()
Dim InputYear As Integer
InputYear = InputBox(Prompt:="How many years of Experience?")
[Code].....
View 1 Replies
View Related
Jun 12, 2014
I need a better way to refer to an empty cell. I'm using “” but if the cell contain a formula but show empty it affect the conditioning format I'm using this =AND(cell <> "", existing_rule)
View 9 Replies
View Related
Feb 20, 2010
I have the following formula that I want to copy to a worksheet named Tankersley:
=IF('Tankersley Input'!B5=0,"",'Tankersley Input'!B5)
I would like the formula to read the worksheet name rather than entering the word Tankersley directly, so that if I copy it to another worksheet named Tyson, the formula would read:
=IF('Tyson Input'!B5=0,"",'Tyson Input'!B5)
How would this formula need to read?
View 2 Replies
View Related
Nov 13, 2009
Lets say we have several numbers and combination of letters/numbers. What can we use to commonly refer to them correctly,
34566
R3456
88900
D2344
I tried to use
Like *####
But thats not correct
View 3 Replies
View Related
Feb 21, 2012
I have a workbook with a list of staff in it. The format is: the manager's name is in row 1, the 8 or so team members are listed below. And again in row 20, there is a manager's name with 8 or so team members listed below. This is duplicated over many columns and multiple sheets. The sheets are all named after the ops-managers who manage all the managers on their respective sheet. The book is called "stf.xls"
I'm using the following code to look up a staff member and return who their manager and ops-manager is to the relevant cells in a different workbook:
Code:
Sub AdvisorFind()
Dim Wsht As Worksheet
Dim Slookfor As String
[Code]....
My problem is how to return the manager's name for a staff member in the middle of the book. Activesheet. is the last worksheet so no good. Wsht.name.Range(manager) is what I want to do but I don't know the correct syntax.
View 2 Replies
View Related
Jul 31, 2012
My first version is this:
Code:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19
Works without problems, but I really don't want to use "ActiveSheet" there
I changed it to:
Code:
Dim chtTR As ChartObject
Set chtTR = shtData.ChartObjects("Chart 1")
chtTR.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19"
and it gives me
"Object doesn't support this property or method."
View 3 Replies
View Related
Mar 31, 2007
I have the following formula to extract a specific cell from a closed workbook. It works fine. I want to be able to make the file name refer to a another cell so I can create a spinner to change it. For example:
='C:Documents and SettingsTom Desktoplabor[01_032407.xls]Stats'!A4
Cell A1 would be 01_032407, and I would replace [01_032407] with [A1]
but it does not work.
View 9 Replies
View Related
Dec 11, 2007
I'm creating a button so as to input a chart where the user inputs the name of the range that they want the chart to hold. The named ranges are dynamic thus I want the graph to also be dynamic but when added the source is simply the range of the name rather than the name itself.
Is there anyway I can enter in a VB variable into quotations marks so that it simply writes that.
View 9 Replies
View Related
Feb 24, 2010
I have every month a product demandplan (for each of 150 models) in a workbook. This workbook contains a seperated sheet for every sales region and is saved on network.
What I want to do is a new workbook be able to pull in the information from the closed demand plan. That part is easy. The hard part is that I want to select the sales region from droplist and than get demand per model. VLOOKUP won't work since I can not select the region and INDIRECT doesn't work with closed workbook.
View 9 Replies
View Related
Mar 8, 2007
I am trying to insert an If statement using the
With Sheets("Sheet2").Select
Range("c" & rBegRow).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[-8]C[4]<>0,Sheet1!R[-8]C[4]*-1,Sheet1!R[-8]C[5]*-1)"
Range("C" & rBegRow).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End With
The reason is that once the formula is inserted in to the cell in worksheet 2 then I drag it down kind of like a copy paste so it changes automatically the if statement cell reference to worksheet 1 automatically. the trouble I am having is that I do not want to use the R[#]C[#] since when the contents of the information changes the formula does not work properly. If i substitute them for G9 instead of the R[-8]C[4] and H9 R[-8]C[5] but it inputs it in the actual excel worksheet as =IF(Sheet1!'G9<>0,Sheet1!'G9*-1,Sheet1!'H9*-1) therefore creating a value error.
View 9 Replies
View Related
Jun 29, 2007
I have this formula:
=IF(C38="C"*F38>=0,"1","")
which is meant to return the number 1 in a cell if C38 has the letter C in it and F38 is equal or greater than 0. I have met both criteria and it returns an error of #VALUE! What am i doing wrong? NB in F38 there is a Vlookup formula that returns a number (in this case it is 0).
View 3 Replies
View Related
Nov 27, 2013
Can you set up countif to work to refer to a certain cell instead of a specif name or text.. for instance
HTML Code:
countif(a36:b36, "c1"
View 3 Replies
View Related
Apr 11, 2014
I have this code for a texbox to search into a listbox
It's working properly but sometimes it doesn't look at all the names in the list.. probably because some of the rows are hidden?
View 4 Replies
View Related
May 5, 2009
I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month. Kind of lost on how to possibly make this work. I basically need something like: =location/[workbook.xls]worksheet!cell - where worksheet = B12 and B12 =month(today()) and is formatted to MMMM
View 4 Replies
View Related
Mar 14, 2012
Is it at all possible to refer to a array that may change in a formula?
For example I need to use a Vlookup formula, however the table array will change depending on the value of another cell.
I need the user to be able to select the column heading that the lookup should work off from a drop down list. So if the user selects column heading C, the array should start from column C though it will always end at column Z. If the user selects column heading Y the array would be Y:Z.
View 7 Replies
View Related
Jun 20, 2012
I just named a range on a sheet and would like to paste that range on a different worksheet within the same workbook. I can't figure out how to refer to the range. I've tried several different variations so far. Example below:
ActiveWorkbook.ActiveSheet.Names.Add Name:="RawSectorData", RefersToR1C1:="=R6C1:R29C11"
'Add new sheet and paste data
ActiveSheet.Name = "Raw_data_Sector_Summary"
ActiveWorkbook.Sheets.Add.Name = "RawData"
Range("A1").Formula = "=RawData!(RawSectorData)"
View 5 Replies
View Related
