Split from Copy, Cut, Fill Relative Or Absolute Formulas
Originally Posted by AAE
Welcome to Ozgrid.
Assume the following:
Cell A1: is the input cell
Cell B1: contains a number
Cell C1: formula = A1*B1 or $A$1*$B$1
Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting.
If you "cut", rather than copy, Excel will maintain the original cell references in the formula.
Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.
See this training page on how to toggle between relative/absolute cell references: [url]
Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.
Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.
Back to coding in VBA after long months of Matlab, and banging my head around this simple code snippet:
Sub openfile(FilePath) Dim myWBk As New Workbook 'Now open Form myWbk=Workbooks.Open(FilePath) End Sub
This returns a nasty error "Activex Component couldn't create object". It opens the file given by FilePath though. What I would like is to assign the open workbook to the myWbk variable, such that it is easier to work with.
Putting the final touches on a project I have and the last element doesn't work 100%. the scenario: I have a function that I wrote based on the user's input of the desired column to perform the operation. As a method of error-trapping and ease of use, I set instructions for the code to activate:
1) if the user highlights multiple cells of the desired column 2) enters the letter of the column (up to "z", not case sensitive) 3) enters the number of the column
Sub Button5_Click() Dim ColumnUsed As Variant ' find number of columns selected For MyColNum = 1 To Selection.Columns.Count Cols = Cols + 1 Next MyColNum 'find number of rows selected For MyRowNum = 1 To Selection.Rows.Count Rows = Rows + 1 Next MyRowNum..............................
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.
Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.
For example, under certain conditions
DataIdentifier would be set to "DataSetA" and under other conditions DataIdentifier would be set to "DataSetB".
Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.
This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).
I am doing a Regular Expression search on a string variable assigned to the HTML content of a Wikipedia search. However I am currently manually going to Wikipedia, searching for the term, saving the html page, opening the saved page with Notepad and then copying the content into a cell.
Can the above process be automated with VBA, how to assign the html content of a Wikipedia search to a string variable.
I am using a ComboBox on a worksheet where the items in that box are pulled from Sheet1A:A. When the selection of the item in the comboBox is made, I would like several textboxes on that same page to be auto-populated based upon info derived from cells adjoining whatever row in Column A was chosen on Sheet 1.
For simplicity sake, let's say the range on Sheet 1 is A:H, and TextBox1 = "B", TextBox2= "C", etc.
so if, A B C D JOHN 15 DOGS BLUE SUE 18 CATS RED BOB 20 TURTLES YELLOW
When Combobox selection is JOHN, I want TextBox1= "15" TextBox2= "DOGS" TextBox3= "BLUE"
I need to count the number of times a value appears in a row and assign a number that tells which occurence of the text it is. For example
Name Address Jim 123 Smith Street Jane 123 Smith Street Bob 543 Apple Street Mary 543 Apple Street
I would like a way to insert a column that puts a 1 next to Jim and a 2 next to Jane, a 1 next to Bob and a 2 next to Mary and so on. Ultimately, I want to sort the list and delete all the twos, thus deleting the duplicate address entry.
I am trying to write a macro that will cut the text in a given cell to 16 characters. My code works as long as the text is always in the same cell, but I eventially want to ask the user what cell to use. So I want to replace the “RC[-25]” with my WorkCell variable. I can’t seem to figure out how to put it in there.
Sub test() Dim SizeOfRange As Integer Dim WorkCell As String SizeOfRange = 10 WorkCell = A1 For x = 1 To SizeOfRange Range("z" & x).Select ActiveCell.FormulaR1C1 = "=LEFT(RC[-25],16)" Next x End Sub
Excel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.
I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.
I've attached a sample workbook.
If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.
I have a worksheet that logs equipment activities. Equipment may already have a standard unit # assigned to it or it may need a temporary unit # assigned for tracking. In a separate worksheet is a column of predefined temp unit #'s that can be assigned and next it is a column to place an "X" once a unit # has been assigned.
I need to write logic that, when an "X" is entered in Column F (Assign a Unit #) of the Activities worksheet, the Unit # (Column G) will automatically select the next available Temp Unit # in the Assign worksheet...and mark that Unit # as no longer available. Quite frankly, this may be more easily done with VBA, but I am not experienced in that at all. I have attached a sample worksheet showing the format of the unit # to be assigned, etc.
In a macro I am writing, I am trying to use the data stored in a variable inside a formula. Specifically, my variable holds a string that represents the title of a specific sheet in my workbook. I am trying to put this variable into the count formula to be used as the sheet location.
I need to run a formula each month which calculates the ‘product’ for a range of cells in a column, where the cell range will change each month. The starting cell for the range is static, but the range end will always change - additionally, the last valid cell will always be followed by a null value cell. The attached example shows the basic format of my data – a range of values, by row, which will always end with a null value cell. The formula I’m using (to match my example) is: =(PRODUCT(A10:A13) –-> and I want to display the result in cell C10.
While I’ve been able to identify the ‘ending good row’ several ways, I haven’t been able to figure out what to do with this information – I seem be be lost in the translation of OFFSET values to CELL REFERENCE values. Has anyone encountered this specific scenario and found a solution?
How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.
I care about is the line starting wks4.Cells(Di, 2) = I am trying to sum data from a variable length column in worksheet 3 and place the result in column 4 However, when I hit enter after entering the formula into =SUM() I get
Expected: list separator or )
[code] Sub Macro1() Dim wks3 As Worksheet Dim wks4 As Worksheet Set wks3 = Worksheets("Sheet3")
In order to deal with the multitude of screen resolutions that can be found at our work, I have got a module to check the current screen resolution on loading the workbook. I then have set up 3 different sized userforms, all containing the same coding, and based on the displayed screen resolution one of these is open (the userforms are named Customer1, Customer2, Customer3).
The displayed userform then has a button to show another userform (Customer_Details), and this form requires some referencing back to the previous form for some of it's information eg,
customer_details.textbox1.text = customer(1, 2 or 3).textbox1.text customerdetails.combobox1.list = customer(1, 2 or 3).combobox.list
way for me reference the specific "Customer" form that has been opened, without calling it by name?