I have named a range TEST
I know how to type the range name in a formula e.g. a VLOOKUP
I want to be able to type the name of the range TEST in an empty cell (say D1) and in another cell say D5 have a formula e.g. =VLOOKUP(A1,TEST,2,0) look at that word and use the word in the formula.
So if I change the word in D1 to DATA (which is the name of another range I have created) then the VLOOKUP in D5 changes to =VLOOKUP(A1,DATA,2,0)
I have a number of spreadsheets (12) that feed a summary sheet. The 12 sheets derive their static information from the summary, e.g. item names, dates, etc. While data is input into the 12 sheets.
I am trying to create a macro that will add a row, for a new item, in all of the sheets. I have a range defined below the last row of the items section that needs to be copied to the new row - this is not the last row of the spreadsheet. Each sheet has a different range name as they are for different periods.
I have a range of names which are sorted alphabetically. I need a procedure that will find where a new name entered via a textbox will reside, insert a blank row into the range, and insert the name into the blank cell within the range.
View 11 Replies View RelatedHow would I modify the code belwo to insert the copied range above the existing data in the worksheet? (The part giving me the problem is in bold italics.) Also, how would I modify this code so that it only copies the filtered data, NOT including the column headers?
View 14 Replies View RelatedI have some data generated from same sample as listed below:
A: Amplitude
Time Time(hours) Y (A1) SD (A1) Y (A2) SD (A2) Y (A3) SD (A3) 3.97 95.28 0.01 0.00 0.01 0.00 0.01 0.00 3.98 95.53 0.01 0.00 0.01 0.00 0.01 0.00 3.99 95.78 0.01 0.00 0.01 0.00 0.01 0.00
B: Beat
Time Time(hours) Y (A1) SD (A1) Y (A2) SD (A2) Y (A3) SD (A3) 3.97 95.28 1.00 0.00 0.01 0.00 0.01 0.00 3.98 95.53 2.00 0.00 0.01 0.00 0.01 0.00 3.99 95.78 3.00 0.00 0.01 0.00 0.01 0.00
Is there any easy macro to combine data under A and B. For example:cut A1 data under B:Beat and insert it after A1 under A:Amplitude; cut A2 data under B:Beat and insert it after A2 under A:Amplitude, et al?
I am trying to write a code which automatically inserts the users username in a cell once a macro button is pressed.
Sub Approve()
If ThisWorkbook.HasRoutingSlip = True Then
With ThisWorkbook.RoutingSlip
.Delivery = xlOneAfterAnother
Sheets("infosheet").Select
.Subject = "Here is " & ThisWorkbook.Name
.Message = "Here is the workbook. What do you think?"
End With
Workbooks("myfile.xls").Route
End If .....................
I've created a named range (A500:AB500) called "RowTemplate_C"
This row only contains specific formatting which I would like to use throughout my workbook.
How can I create a macro function whereby:
When I select a cell or row, and click a button (called "InsertRow_C" ),
This inserts a new row, and pastes the named range there.
I've recorded my actions and made several attempts .. but my knowledge in VBA is, as you can see, quite limited.
Sub Macro1()
Selection.Insert Shift:=xlDown
Range("RowTemplate_C").Paste
End Sub
On sheet "Kilo" I have 2 command buttons one "insert" and one "delete". I would like the insert button to work as if it was being utilized as if it was still on the "Nom roll" sheet. If I use the button on kilo as is it messes up the Kilo sheet.
View 9 Replies View Relatedi am trying to insert a formula into a range of cells using a For... to loop
This is what it looks like:
VB:
For i=1 To n
Worksheets("Ret_sheet").Cells(i, 8).Formula = "=if(mid(B" & i & ",3,1)=""A"",""PY Campaigns"",mid(B" & i & ",4,3)"
Next i
The row with the formula returns a syntax error.
what the right syntax is? I have read a lot about inserting a formula in a cell using VBA, but i never met the case with a counter (i) usage. I guess the problem might be there.
I have run into a problem with a Pivot Table overwriting other content when new data is added and refreshed.
This pivot table is included across a range of "Product" worksheets. To resolve this issue I would like a macro to insert a new row below the pivot table , this way when new content is added the pivot table will not overwrite the content below it.
The Pivot Table data is on the "Client" worksheet. Each Product worksheet includes a Pivot Table referencing the the Client data. I would like to force a user to add a new client data with a macro this way I can force a new row to be inserted across All "Product" worksheets.
I need a VBA code for a worksheet to automatically insert cut paste a row from a range of unprotected rows to the range of protected rows once the user decides to do so.
I have a sheet called "Customer Stock" which has two tables called "Uncollected" and "Collected".
I need "Uncollected" table to be unprotected and "Collected" Table to be protected.
Once the a Customer collects the goods, the user can cut and insert the collected row from the unprotected "uncollected" table to just above the first row of the protected "collected" table,
so that the inserted cut row now should be protected.
The password should be 1234 and the user won't know the password. Only the administrator will be having it.
I'm having a toughs time with what I thought was a simple task. I need to cut and insert/paste a range of cells and then repeat in a loop. Explicitly, I have 3 columns with 2990 rows. Every 46 rows is a 'group' and I want to cut each group and paste at the top of the sheet so I have only 46 rows and (2990/46*3) 195 columns. I need to preserve the order so that the group at the bottom becomes the rightmost group........
View 5 Replies View RelatedI want to apply conditional formatting to a range, but only to the number of rows in the sheet (to avoid formatting blank rows). I am not sure how to insert the row count into the range select.
NumRows = Application.WorksheetFunction.CountA(Range("A1:A65536")) gives me the number of rows, but how do I get that into
Range("A5:F18").Select
18 being the number I want to substitute the row count for.
My macro inserts the formulas correctly up until the last loop where I get an Application Defined or Object Defined error on the .FormulaR1C1 line. The msgbox returns the correct values.
iCtr = 0 'data starts on 1
For c = 6 To 14 'columns F to N
iCtr = iCtr + 1
MsgBox ("Column " & c & " Index " & iCtr)
With . Cells(6, c)
.HorizontalAlignment = xlCenter
.NumberFormat = "#,##0;;"
.FormulaR1C1 = "=IF(ISBLANK(INDEX(MyNamedRange," & iCtr & ",1)),"""",'" & Replace(MainPage.Name, "'", "''") & "'!R" & NextRow & "C6)"
End With
Next
I am trying to insert a formula into the cells in a column dynamic range.
I never know how long the column will be from instance to instance.
I have been trying to use the following code,
Sub insertf()
'
' insertf Macro
' Macro recorded 10/11/2007 by bbactadmin
'
'
Do Until myrange.Value = ""
ActiveCell.Range("f10").Select
ActiveCell.Offset(1, 0).Range("f1").Select
Range.Formula = _
"=VLOOKUP(LEFT(E11,3),fam,2,FALSE)"
ActiveCell.Offset(1, 0).Range("f1").Select
Loop
End Sub
I am trying to insert the lookup in all of the cells in the column within the dynamic range.
I am updating a wsheet via a form using vb to locate next empy row etc.
The issue I have is with the formula's no matter how I try I cannot get the formula to increment with each row. I know I can drag and auto fill on the sheet but I want to use vb if poss. eg
Previous entry to sheet:-
A1=1
B1=2
c1=A1+B1(formula)
New entry via form selecting next empty.
A2=(updated from form.)
B2=(Updated from form.)
C2=(Update code VB)
This is all okay and functional the problem starts from here
I so far have been unable to increment the formula in c1 from A1+B1 to A2+B2 using vb, is this possible or am I just chasing my tail.
Trying to get this short piece of code to work. I'm trying to show the formula in the cell not just return the result.
[Code] ......
Why this isn't working ?
I built a Microsoft Access database for my church to track member information. I was able to import a lot of the needed information to create the records for each individual and household easily. What I can't do is import their offering donations until it is "Normalized." Which means that I need each row to be 1 record with the fields listed as the column headings. My church has a spreadsheet with several sheets one of which is named Input. I've attached the desensitized file for you to view/play with.
What I need to do is loop through the input sheet and find all the values in range B:4 to GN:387 that are greater than zero or not null (I believe they entered the default as zero so their other calculations would work). Then I need those values to be transferred to the column labeled Offering Amount on the ImporttoAccess sheet. Somehow I also need to define three variables associated with the cell that is contains a value greater than zero.
1. I need the row heading value for the envelope number (Column A of input sheet) which intersects with the amount
2. I need the column heading value (3 field merged) for the date (Row 1 of input sheet) which intersects with the amount
3. I need the column heading value (labeled: GenFund, WLA, or Missions) for the designation of the offering (Row 2 of input sheet) which intersects with the amount
Each of these I need to attach to the value offering amount and insert into their corresponding column fields for that record. I have entered 2 example rows of data into the ImporttoAccess sheet for you to see what I am after. I am sure it's possible since the data is there, But how to go about it.
On the first row of a spreadsheet template is a <hidden> contiguous series of conditionally formatted cells (range name "stdRow" =production!$1:$1), with formulas, that I want a user to be able to easily insert at whatever row they might be in a worksheet.
If, for example, the user is at D24, then clicks the [InsertRow] button that I have positioned at the top of the worksheet (in a fixed pane), I want the attached macro to insert an instance of "stdRow" directly underneath the user position (at row 25, in this case). The use's position should still be at D24 when the macro finishes. If the user clicks the button multiple times, multiple rows should be inserted (again, without changing the user's position).
This is what I have tried so far:
Right now, the code below is inserting an entire row when it pastes the values in I only want the values to be inserted in Range (A:AJ).
View 5 Replies View RelatedI have recorded a macro code for which is as follows. This Macro goes into a worksheet and gives percentile value for a range of data. But this is becoming cubersome as this sheet is 65531 rows and can span multiple spread sheets. Can this be put in a loop of some sort to go through the whole worksheet? ....
View 9 Replies View RelatedWhat I need to is create a VBA function with several parameters that reads data from an M x N range of rows and columns (matrix). I cannot just pick and choose certain cells within this range as the function goes into a loop through at least 200 cells in the 35 x 200 range. Obviously, I am here posting as I cannot get this function to work. I believe my troubles lie with reading this range into the function...
Application.Goto Reference:="range"
rangearray = Selection.Value
values(I, J) = rangearray(I + 1, J)
Do I need to set a function variable equal to that range, or do I do this another way? Basically, I need to have the ability to pick a given cell(m, n) out of the range and have my way with it in the function.
I will have a list of unique values. For each unique value, there is an indicator that tells me how many lines of information I need. Based on that criteria, I need a macro to insert that many rows and populate it with text. This will ultimately become a look up table that I will just use index/match function to populate.
I've attached a sample sheet. I'm not sure if I need a macro to insert rows or a macro to paste a copied range. Or maybe I need a mixture of both with a formula to calculate the number of cells I need?
I have a sheet that has a section in range (c18:k25) (it is merged and text is wrapped) If that amount of space is not adequate to fit all their information I would like to add a macro that when activated it asks how many rows would you like to add and then based on that number whether it be 1-10 it would insert a new row to that merged range so they can continue adding more information . I have the sheet locked down to prevent changes so it would have to be unlocked and then re-locked within the macro.
View 1 Replies View RelatedI would like to Insert the contents of a Range of Cells into a single Comment. ie:
The contents of Range A1:A50 into a Comment placed at F1. Is there a Macro that will do this?
I'm looking for a quick and easy way to insert a value in the first empty cell in a multi-column, multi-row range using VBA. I suppose I could loop through the range, but the table could grow to immense proportions and I don't want to slow everything down.
For example, the new value should be placed in cell C4. It doesn't matter whether the range is looped through the rows or columns, either will work just fine.
A
B
C
1
5
65
56
2
32
12
89
[code]...
I am trying to put a SUMIF formula at the bottom of the data. Doing it manually is fine, but I want some vba to automate it, and my vba doesnt work.
Sub balance()
'adds journal line so journal balances in each month
Dim Rga As Range
Dim Rgb As Range
Dim Rgc As Range
Dim Rgd As Range
Dim Rge As Range
Dim Rgf As Range
This code just inserts text into the formula, rather than the range of cells.
I'm currently trying to insert the copy range of one worksheet to another. Let's say I have 3 worksheets, one main and two additional info sheet. I want to search column C in main worksheet for blank cell and use column F in the same row as the worksheet name which I want to copy from, then use column E from main worksheet as the keyword for the range I want to copy(range from column E to column Z). But when inserting, I want the keyword in main worksheet such as 'G' in the same row as blank cell to match the 'G' in copied worksheet. Because the 'G' in main worksheet is not in the same column for each row, I try to search the forum but can't find the inserting part. I also attach the example workbook.
View 3 Replies View RelatedI have another little VBA macro problem that appears to be beyond my coding knowledge.
I have attached a small excel spreadsheet, with a macro recorded (CTRL-P) of what I'm trying to accomplish. Basically, If the data in column A matches a single entry in column F, I need to copy and insert the row (columns F-L) that matches.
I have a formula that works fine in the cell of an Excel spreadsheet but I'm struggling to translate it into VBA (your help please).
The cell formula is:
=If(B2="", "", B2 & " (version: " & F2 & ")")
I want to iterate through all rows in my spreadsheet (about 2000) incrementing the relevant row numbers in the formula @ each pass - eg changing B2 -> B3 -> B4 etc and F2 -> F3 -> F4 etc where column 'B' contains the name of the product and column 'F' contains the version number, resulting in "Product Name (version: 123)" per row.
The closest I've got to this is:
Sub LookupNameInColumnA()
Range("A2").Select
Dim i As Integer
For i = 1 To Selection. CurrentRegion.Rows.Count - 1
ActiveCell.Formula = "=IF(B2="""", """", B2 & "" Version: 999"")" ' problem line?
ActiveCell.Offset(1, 0).Select
Next i
End Sub
This works OK'ish but I want each line to reflect the different data per row. I'm struggling to increment row 'B' and row 'F' in the formula, having tried "B & i + 1" and "F & i + 1" but am getting confused with quotes and concatenating strings within formulas.