VBA - Insert Formula With Sheet References In All Blank Cells In Used Range?
Sep 9, 2013
I am try to get the following VBA macro to work; however, I keep getting hung up on errors regarding the formula I am trying to input. It is getting hung up on the apostrophes and dollar signs. I am fairly new to VBA so I am lost when it come to converting my sheet formulas to VBA.
.Formula = "=RAND()*0+VLOOKUP(INDIRECT(ADDRESS(1,COLUMN(),3),TRUE),INDIRECT("'"&TEXT(INDIRECT("$A"&ROW(),TRUE),"DD-MMM-YYYY")&" Inv'!"&"$J:$K",TRUE),2,FALSE)"
.Value = .Value
I have a worksheet with many different formulas in many different cells. When I insert or delete a row, there is one formula in one cell that does update to reflect the change in rows. The cell is located several rows below the section where rows are changing. It is a simple formula too. Here is the formula: "= SUM($E$3:E11)*0.09". If I delete more rows that cause the highest row number to be less than 11, this formula updates. If I insert more rows and go beyond row 11, the formula does not update. I have tried using $E$11, $E11, and E$11 to no avail.
I'm working in a workbook with several sheets, the first two collums of each sheet are =to the first sheet. some times I need to insert a line, but when I did that, I need to type again all formulas or drag the previous ones, and format again the cells. is there another way to do this?
I have a spreadsheet where I want to filter out a row if the entire row has zeros across all the columns. I cannot just use a Sum() formula because some of the numbers are negative and there is a chance it could zero the sum out.
Currently to do this I am using the following to tell if there are values in each of my rows:
I've normally just dynamically selected a range using the xldown feature but because this row contains formula it goes always down to the bottom of the formula instead of the last cell which isn't blank.
How would i go about generating this dynamic range that stops at the first cell that contains no value (but has a formula)? Perhaps a do while loop which looped down until it hit the first blank and then assigned the cells it had looped through as the range?
I need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:
=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")
Could someone show me an alternate way of using/writing this code instead of using RC references to the Sheet1. I am trying to select ranges that dynamically change according to the range of data in the next column. This is the code I have but it is static in the range it chooses.
Any way to always include the sheet name before the cell address in a formula? For example, in my "Summary" sheet, I have a formula:
Code: =A2 + B4/B5
However, without typing anything else, I'd like the formula to populate the sheet name since I will be extracting the formula as text and need the full reference:
Code: =Summary!A2 + Summary!B4/Summary!B5
A way to get around this is to enter the equal sign, then temporarily select another sheet, then go back to the "Summary" sheet where all cell references will include the sheet name, but if there is a quicker way to do it using some kind of property or event in VBA, that would be ideal.
I want to sum A2 to B10. The catch is: I have the letter "A" in cell A1, the number "2" in cell B1, the letter "B" in cell C1, and the number "10" in cell D1. In effect, I want to make a function =SUM("A1B1":"C1D1") such that it gives me =SUM(A2:B10). Hopefully, the answer will work for any function (e.g. SUMIF, COUNT, VLOOKUP, etc).
I have mananged to find and tweak the following code in order to serve my copy / paste to master sheet requirements.
At the bottom of the code I have added a delete rows based on column contents routine although I'm not sure if it is actually the right one to use.
I have included it in the same module in order to tidy up the whole process and have it all operate under one click.
As stated on the sheet in this exmple, The paste could consist of any number of sheets although the range is always the same on each sheet. (only amounting to around 20 rows that we are dealing with so no reams of data with odd empty rows that would take an age to find otherwise).
The data can look like it does on Sheet 2 here and also could look like it does on sheet 3. (this data is coming from a sales rep's order sheet)
From the routine in module 1, I just don't know where an "add an empty row" or delete all empty rows bar 1" code would need to be inserted in order to keep the process going until all sheets are copied.
I'm aware that with the delete code at the bottom, the whole copy process is completing before the delete process then does it's bit so think I'm on the right track in thinking that the delete all empty rows but leave one" requirements needs to be further up the code but I just don't how to get the result I need or where that code should break into the routine.
The only other way I can think to acheive the result is to allow the range to increase by 1 row on the form but somehow make sure that the last row in the range contains a character in column B to "trick/force" the delete routine to leave that row in. That would be do-able but the trouble is, how do you get a value into a cell that the delete proces would treat as data but not be visible so keeping my spacer line tidy? I've tried just putting a space in the last row of the range in column B but the row still gets deleted.
I have the following macro that inserts a blank line when a field changes. This works fine. However I would like to use this in another spreadsheet that has several addition columns of unrelated data. Therefore I want to insert 1 (row of) blank cells only in the columns specified in the range. The range is 3 columns wide, E - G. I think I'm close, but ...
Dim myRow As Long myRow = 3 'data starting row Do Until Cells(myRow, 5) = "" ' the # is the column that changes which I want to trigger a blank insertion If Cells(myRow, 5) = Cells(myRow - 1, 5) Then myRow = myRow + 1
I have a worksheet that contains data arranged in fields from columns A to J. The relevant columns for the purposes of the macro are columns B (customer codes) and G (sales values). Column B may contain a single instance of a customer code, or multiple (over 50), depending on how active a customer has been. The worksheet always contains many different customer codes with varying numbers of rows for each customer, sorted by customer. What I'm trying to do is write a macro that will loop through the worksheet and insert a blank line immediately after a change in value of column B (customer code), and in this blank line, insert the customer code in column B (which comes from the cell immediately above), a sum formula for all values within a range that relate to that particular customer in column G, and an IF statement in column J that relates to the SUM formula. The loop concludes when there are no longer values in column B.
I have managed to insert blank lines on change in values in column B, but am not sure about the best approach for inserting the SUM formula or the IF statement in columns G and J respectively.
I am running a research experiment in triplicate. Upon generation of data for all three, I create an averaged value of the three. I then take this value and do more calculations with it on a separate sheet. I wanted to know if there was a way in which I could quickly generate formulas based on a changing reference cell.
V1A 28 V2A 26 v3A 30 AvA 28
V1B 20 V2B 22 V3B 18 AvB 20
Vxy represents the value of y sample, x triplicate. V1A being sample A, first triplicate. AvX is the average of the three values.
I want to be able to generate a formula that references AvA which can be applied to the cell immediately below to reference AvB. A simple example would be to square the average.
I am having trouble trying to figure out how to insert blank cells:
I need to insert cells, in columns B through H, equal to the number of returned IDs in column B.
Please take a look at the spreadsheet I attached.
Here is the code I am using to separate the IDs in column B so that I get one ID in each A cell. When I do this it separates them great, but then I have to manually go in and insert cells down so that the rest of the rows match up with the right ID.
Sub Macro1() Dim fromCol As String Dim toCol As String Dim fromRow As String Dim toRow As String
I am trying to come up with a code that will copy column A entirely, and insert the copied cells into the first blank column. In the columns with information in them, there will always be text in the first cell, so that can be used as the test to find the first blank column, but I'm not sure how to get this done for my macro.
I need to put an extra "spacer" cell in each row that contains either 8 or 9 blank spacer cells in columns A through H or A through I, respectively, so that everything in those rows shifts to the right by one column. The problem is that all the other rows don't need any extra spacer cells. There is no pattern to the rows that need the extra spacer cell (such as every 5th row, or something).
I have a large list of cells in excel: 15, 33, 90, 102, 149, 159, 217, 228, 238, 247, 305, 312, 369, 417, 428, 486, 538, 548, 606, 621, 671, 679, 737, 805, 816, 874, 915, 923, 981, 1029,1038 .
Under each of these cells I would like to insert 20 blank rows. I have tried various codes but i'm struggling with the fact that as soon as I insert 20 rows at cell 15, all the other cellnumbers change.
This is a reformulation of this post: [URL] ...........
I need to do two things in my Excel spreadsheet: 1) I want it to insert a blank row everytime a value in the 1st column changes. 2) In the blank lines, I want to do a COUNTA for each of columns G through N. If I can get the program to insert the blank rows...they will not be a set # of records apart...some will have 3 records & some might have 17 records. Is there a way to automatically cause #2 to happen instead of having to choose the function icon and then tell Excel the first and last cells in each range?
As you would normally use indirect formulas so the cell references don't change. Which that is what I want in the end, but I need to copy them to an indefinite number of cells first and would like to not do it by hand. I have found some solutions to similar questions/problems but cannot figure out how to make them work for me. So, what I am looking to do is this... (I have also attached the spreadsheet for reference)
I have gotten the information in columns A through F on the first sheet to update as rows are added, moved, deleted on the second sheet using Indirect range. Also, I could do this for Column I (Copmleted Proj. Avg. Terminations) but I would have to do it manually (as I began doing in I3, I4 & I5) but that would be time consuming. So I am hoping there is a way I can copy the formula down the cells are updated for the initial copy but then don't update if the referenced cells are moved or deleted.