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.
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:
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
How does one pick up the data contained in a worksheet cell (say B5) and use it in the vba code of a custom function without passing the cell as an arguement
I have a FOR loop which is supposed to loop through all the worksheets in my workbook, create a named range, then add some data to the right of the range. However, when I run my code, all the named ranges refer to the last worksheet in my workbook. The loop only seems to be partly working, and I just can't work out why.
Sub maxLifData() ' for each worksheet in the workbook, do dmax formulae and add results to new sheet. bhole id on left, depth across top. Dim ws As Worksheet Dim wsName() As String Dim i As Integer Dim mCount As Integer Dim nr1 As Range Dim nr2 As Range Dim head1 As String Dim head2 As String Dim wsRangeName() As String Dim maxF As String Dim fRange As Range Dim rng As Range Dim mRng As Range head1 = "STCN_DPTH" Set fRange = Range("G3") Set mRng = Range("A2") Redim wsName(Worksheets.Count) Redim wsRangeName(Worksheets.Count).............
I have two spreadsheets, i suppose one part of the solution may be to copy spreadsheet 2 into a new worksheet in spreadsheet 1, but this is my current situation:
Spreadsheet 1 has "Employee lastname" in column F, "Employee Firstname" in column G. Column A in this sheet needs updating with the data populated in column H for that employee in spreadsheet 2 - the lastname is contained in column B, firstname in column C - so i need to to a match.
I'm trying to create a nested IFs statement that looks at a range of cells between 2 tabs within the same worksheet, determines if there is a value of 1 in ANY of those cells, and if there is, to multiply the value of yet another cell by 1 in a cell on the second tab. So, if 1 is a value in any cell of Tab 1 cells C82 to C86, then in Tab 2 cell B21, multiply 1 by the value in 'Tab 1 Cell H5'.
I would like to have a nested if or select case statement to handle a worksheet event. The conditions it will check are: 1.Make sure target is w/in range, otherwise EXIT 2.Make sure that target offset value is not empty, otherwise display message 3.All is good, open form
I’ve tried various formulations and positionings of the statements ,but not all conditions are met with equal success.
What happens is I get the the first two conditions, but the third doesn’t work.
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)
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.
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.
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.
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.
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.
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).
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.
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)"
I'm trying to find the details required to refer to contents of a cell and then treat the value as the formula. IE for Sum(B5:B8), i'd like to have two cells, one with a 5 in it and one with the 8.
I know its possible if i put "b5" as the cell content using indirect - but I'll be using the same cell value to update formulas in different columns so I cant afford to include the B. I assume theres a very simple method along the lines of Sum((B&(Cell(a4)):B&(Cell(a5)))?
I have noticed in many of the Excel help files that the developers liked to use shortcut notation for Range reference in VBA.
For example, Range("A1:B10") would be [A1:B10].
I was doing a bit of testing with this, and was not able to make the shortcut notation work with a variable. Does the shortcut method have the capabilities to do the equivalent of Range("A1:B" & LR)?
If it does have that capability, is it just due to force of habit that we always use Range() to refer to ranges, or would there be a more in-depth reason.
If I have a button on each of numerous sheets, how do I get all of the buttons to refer to the same bit of code, without having to copy it to the click event of each?
I think I understand what you were talking about now with the generation of the watchbill using the rand() and sort. You were trying to get me to get rid of the whole system I was using before and use only the rand and sort. I thought you were asking me to incorperate the rand sort thing into the randomization process I already had using offset etc. The whole thing works good now, however, because the column Ive designated for the roster names may or may not always be filled the formula sometimes refers to empty cells in that column thus producing 0s on the watchbill. I was thinking maybe (if its possible) having a formula to identify a 0 and if so skip to the next cell down. The formula would repeat until it found a name without a zero in it.
I have a number of different formulae I want to invoke according to a choice. I am able to request the formula I want by entering them ‘in line’ as value1, value2 etc into the CHOOSE formula … e.g. =CHOOSE($H$1,IF(N3>O3,1,""),IF(N3<O3,1,""),IF(N3=O 3,1,””))
but this is cumbersome and confusing since I have a growing number of choices and some of the formulae are quite long. I want to enter them in a list and extract the formula which corresponds with the Index_num in the CHOOSE formula. I also need the cells in the formulae to be relative so this rules out using NAMES to refer to the formulae.
