Automate Insert Row Function?
Mar 24, 2007
attempting to accomplish the following in Excel and I believe that I need to use VBA to do it. I am an intermediate Excel user, so you might need to "bring it down to my level" a bit :-)
maintain a master list of tasks (in rows) in a file (File1). In another file (File2), I will keep the task list and related hours charged by our staff (in columns). What I want the macro to do is to compare each row of File2 to File 1 and if they are different (which will happen if I add a new task to the master list in File1), to insert a row. It should do this for each row in the file until it reaches the end (there may be blank rows though).
View 9 Replies
ADVERTISEMENT
Feb 15, 2014
I am trying to write a small macro involving Len and Left formulas. This will take input from two cells and and give the value on a third cell. I know how to do this on excel. This goes as follows:
Value on A5 = HB_AM_1
Value on B5 = 1
My formula to extract the specific text =LEFT(A5,LEN(A5)-LEN(B5)-1).
My result on D5= HB_AM
Here, my inputs are clearly defined and I can write this formula on cell D5 to get the result. I can copy this down to the end of my data and I can get my result.
My challenges: I need to do this for various reports where the data extends to various rows. I think looping will solve the problem, but not sure how to do it. The value on cell B5 will always show the last number at the cell A5 (1 in the eg above). So, if the number in A5 is 10, value in B5 will also be 10. This is already there in my data, so I do not worry about this part.
View 5 Replies
View Related
Jun 23, 2009
i want to do a vlookup in a function to automate my worksheets. im trying to grasp how to do it.
View 4 Replies
View Related
Feb 2, 2007
I have a spreadsheet that I am trying to automate, one of the tasks is to sum some columns namely columns O to T. Column B contains the exact number of entries in the columns O to T, therefore I thought that this would be a good counter. The Spreadsheet varies in number of entries every time. In this instance the rows are in total 200 inclusive a header row. ie r below is = to 200. I have tried to record a macro and from that I have tried to replace the pertinent values.
Sub Sel_Sum ()
Dim r As Integer
Dim s As Integer
Dim t As Integer
Dim U As Integer
Range("B1", Range("B65536").End(xlUp)).Select
r = Selection.Rows.Count
s = (r - 1) * -1
t = r + 1
U = -1
Range("O" & t, "T" & t).Select
Selection.FormulaR1C1 = "=SUM(R" & [s] & "C:R" & [u] & "C)" 'fails here
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
End Sub
View 2 Replies
View Related
Jan 18, 2007
I'm using the SolverOk function to automate upon the click of a button. The arguement ValueOf takes the value (58.6) that was keyed into the Solver Parameter menu when I was recording the macro. I'd like the the solver to pick up the figure for ValueOf:= from cell $D$32 instead so that I can just key in any value in $D$32 and then click on the button to get solver to solve for this. I've tried modifying ValueOf:= "$D$32" but the solver still could not pick up the figure from that cell.
Private Sub CommandButton1_Click()
Range("C32").Select
SolverOk SetCell:="$C$32", MaxMinVal:=3, ValueOf:=58.6, ByChange:="$C$33"
SolverSolve
End Sub
End Sub
View 2 Replies
View Related
Nov 16, 2011
I'm trying to automate a search and match function between two separate workbooks using VBA and am having some problems. I'm using the following line:
search_results = Application.Match(temp, Range("E1:E900"), 0)
which works fine. now the problem is that when i'm searching for lets say "ABCD" in one of the workbooks, if there is an entry "ABCD " (with a space at the end) it says that the entry doesn't exist. is there any way of correcting it such that it would always neglect the space at the end if it exists? i tried using -1 instead of 0 and it messes up, so i'm not sure what to do.
View 2 Replies
View Related
May 7, 2009
I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.
Here is the code i have but it wont even compile........
View 9 Replies
View Related
Sep 14, 2006
A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)
View 4 Replies
View Related
Aug 16, 2014
What I want to do is call a value from a cell of a separate workbook file by just inserting the file name. So basically:
Cell A1: File_Name.xlsx
Cell A2: ='[A1]Sheet1!'$B$2
Such that the formula effectively reads: ='[File_Name.xlsx]Sheet1!'$B$2 , and outputs the value of B2 from Sheet1 of File_Name.
But I keep getting error messages.
View 3 Replies
View Related
Feb 11, 2009
I'm wondering if it's possible to use a lookup formula to populate cell comments? In other words, I'd like to put a lookup table into a cell, and if the value is not FALSE, have that Lookup table then pull in certain information into the cell's comments.
View 2 Replies
View Related
Jan 4, 2013
I'm trying to insert an IF function in a cell with VBA. But it keeps giving me an error and I don't understand why.
Here's the code:
Sub IFinsert_test()
Dim C_IndexKol As String
C_IndexKol = "Q"
Dim C_DebnrKol As String
C_DebnrKol = "A"
Worksheets("blad1").Range(C_IndexKol & "9").Formula = _
"=if(Worksheets("blad1").Range (C_debnrKol & "9")"""","testA","testB")"
End Sub
View 2 Replies
View Related
Nov 7, 2012
I am trying to concatenate the text of a few cells in a specific worksheet. I'm not wanting to use the STRING & STRING type code because I already had a Concatenate formula nested with an index formula and needed to have this formula copied to a certain RANGE, Where the RANGE was specified by a Variable. T
he Code below does what I need if the CONCATENATE/INDEX formula is in the cell that im copying already. (BELOW) The Formula in the cell, for example, on row 19 of worksheet "COMMISSION", that needs to be copied and incremented down the VARIABLE RANGE is
=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1)).
VB:
Dim iInput_Rows As Integer
iInput_Rows = Worksheets("Workspace").Range("D3").Value
Dim iOutput_Rows As Integer
iOutput_Rows = Worksheets("Workspace").Range("D1").Value
Dim iAnalog_Rows As Integer
[Code] ....
My problem is when I use the following code to insert that concatenate/index formula, through vba, then I get errors because it evidently doesnt like the " " for the space i needed between texts. (The Formula is concatenating text in those cells but every other Row) Can I do this in VBA?
VB:
Worksheets("COMMISSION").Range("B19 + iOutputs_Rows").Formula = "=CONCATENATE(INDEX(OUTPUTS!J:J,(ROW(OUTPUTS!J2)-1)*2+1)," ",INDEX(OUTPUTS!K:K,(ROW(OUTPUTS!K2)-1)*2+1))"
View 2 Replies
View Related
Mar 6, 2014
I have a data set, with 20-30 columns and 3500 ish rows - in here I have to get different kind of information on each column.
One of them is Missing Values, that is cells that are blank in the specific columns, I know I can just use the countblank function and then just put in the number - but how do I do it if I want the formula to appear "inside" my pivot table, so that I have this range of data, and I want to calculate how many blank cells this specific column have.So my question is: Is possible to add the formula to my pivot table/data and get it to show next to the other information that I require on the different columns?
View 4 Replies
View Related
Jul 23, 2009
I've been trying to solve this for days. In Excel 2007 there is a 'Insert copied cells' function after copying cells and right-clicking on a cell which basically just inserts all copied cells to your desired location.
My question is, how do you call this function in VB, I've used F1 extensively and searched the net and haven't come up with an answer.
View 2 Replies
View Related
Dec 13, 2013
Is it possible to insert a cell refenece into the FIND function.
So, replace JAN-2013 with Sheet1 cell A1
Selection.Find(What:="JAN-2013", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
View 3 Replies
View Related
Apr 29, 2014
Input:
A
B
CA001
CA001
CA001
CA002
[Code]...
Output:
A
B
CA001
CA001
CA001
CA001
[Code]...
1. Insert a row whenever cell value change.
2. Insert cell value (in previous column) in inserted row.
View 3 Replies
View Related
May 5, 2009
I have two sheets. One called "Roster" and one called "final". On the final sheet i have cols for each question on the final exam. I also have a total col which sums up the pts for each question.
On the "roster" sheet. This is kinda like a summary sheet. On this sheet i use a vlookup (shown below) in the cells which are supposed to reference the cells on the "final" sheet for the total pts.
View 10 Replies
View Related
May 13, 2014
I have the code below. That code call some InputBoxes in sequence, that be filled with correctly information like name, cell phone, date of purchase, etc. (sheet is in Portuguese)
But I want to give a function for 'Cancel' Button, because actually if we click on cancel Button, the macro skip to the next inputbox.
I want to click in Cancel Button, and Exit Sub, I used this Tip for example
[Code] ....
But if we do not fill the Text field, the Macro Exit Sub Too
Some fields are optional, so for this i search for a solution
Click in Cancel Button and Exit Sub
[Code] ....
View 5 Replies
View Related
Mar 14, 2013
I need to create a spreadsheet which will have a start date and duration on programme. Is there any way I am able to automatically insert the End dates based on the weeks on the programme?
Additionally, can a function insert a date for the reviews which should be every two weeks into seperate cells?
Name
start date
weeks on programme
End date
1st review
[Code]...
View 1 Replies
View Related
Jun 17, 2006
My cell contents looks like this.
H1111111111111
I want it to look like this..
H11-111-111111-11
I know this can be done by formula but i dont know it. This info is in col B.
View 2 Replies
View Related
Mar 4, 2008
I am using VB to insert a function into a cell on an excell worksheet. The relative references work fine. For the absolute reference, I want to use a named range (LowTotal). If I simply put the name into the function, then I just get the name as text in the function. The second insert formula is were the named range is intended to go. Here is the code so far...
I need to know how to use the varriable name in the .formulaR1C1, so that VB knows it is a variable and not just text....
View 4 Replies
View Related
Nov 25, 2011
I'd like to know if it's possible (Excel 2007) to insert a function/button into a cell, which when clicked, will paste whatever is on the clipboard?
You can see a screenshot of my document here, and I'd like to insert a paste funtion in the red circle. Is it possible?
[URL]
View 9 Replies
View Related
Sep 4, 2009
I have to create a spreasheet monthly that has perhaps 60 transactions. The data is in columns A - G, with columns E and F having the costs figures. I need to sum the value in column E and also in Column F every few rows. The problem is that sometimes there are two rows per transaction and other times it could be 30 rows per transaction. Where I will be lucky is that there are two rows between transactions, and the sum goes in the first empty row.
Basically what I would need it to do, from a macro point of view is to go down column E, and every time it finds the first empty row, insert an auto sum. It would then need to ignore the next row, because it too, is empty.
Just Column E - I need an autosum in the "empty row 1's)
23.00
15.00
6.00
3.00
empty row 1
empty row 2
6.00
3.00
empty row 1
empty row 2
6.00
25.00
3.25
6.32
View 9 Replies
View Related
Feb 26, 2013
I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.
I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.
View 4 Replies
View Related
Apr 25, 2014
I am processing an infinite set of data from a meteorological station here in Alaska which gives me half hourly data reading with a time stamp 00.00, 00.30, 01.00, 01.30, 02.00, 02.30 and so on.
I am using this formula to detect every time half hour reading is skipped (=IF(TEXT(MOD(B1936-B1934,1),"[M]")="30","","missing")) and it works pretty well.
Still I have to check and manually insert extra missing for every half hour missing but that's bearable.
This formula inserts a "missing" every time it finds a gap.
My question is: How can I insert a row above every cell with "missing"?
How do I do that? Here is also my excel sheet.
CR1000_Meteo_20131113_2_CLEANED.xls
View 3 Replies
View Related
Nov 27, 2012
I am trying to insert three columns within a large amount of data. I am using Excel 2003 edition. The three columns need to measure max, min, and standard deviation of month long ranges and the data goes all the way back to 1993.
Currently, I have a column that has the correct ranges but finds the average for each month
(=AVERAGE($H7214:$H7243))...
And many more ranges as it dates back all the way to '93. Is there a possible way to insert these three columns with their respective commands (=MAX... =MIN... etc.) while keeping all the ranges from the AVERAGE column.
In effect, I am looking to solely switch the begining of the column command
(=AVERAGE($H7214:$H7243)) to (=MIN($H7214:$H7243) etc...
While keeping all of the specified ranges from the AVERAGE column.
View 2 Replies
View Related
May 21, 2014
I am trying to figure out the calculation steps that Goal Seek is doing behind the scenes so I can create the formula rather than clicking on goal seek whenever I need to make a change. I need to maintain certain weeks of supply every week depending on the sales. Let's say i want to maintain 5 weeks of supply every week, I would like to insert a column for "Suggested To Order' column that would change if weeks of supply goes below 5. In this scenario I would do a Goal Seek, but I want to automate the calculation so I don't have to do this step every week.
View 2 Replies
View Related
Jan 26, 2010
I create a schedule based on abbreviations which is given to staff. Then this is retyped up with actual times and put in a schedule book that is the final product. I am trying to automate this change from one sheet to another
n = night shift = 2330-0800
D = day off
etc, etc
I have alot more abbreviations, but I hope I just need some advice and can figure it out from there....................
View 2 Replies
View Related
Feb 11, 2009
I have a workbook that I use for my purchase orders which is pretty simple. Each row is a separate PO, with 10 columns (po#, description, cost, date, etc.).
Is there a way to automatically generate new workbooks that contain all of the info from each individual row? For example: if I have 100 rows on my spreadsheet, which means I've issued 100 POs, then I want to have 100 individual xls workbooks, with the filename for each workbook representing the PO #s (206904.xls, 206905.xls, etc.).
View 14 Replies
View Related
Feb 22, 2007
Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.
Example:
A1 @ 31/01/07 = 50 therefore cell C1 = 50
A1 @ 28/02/07 = 45 therefore cell C2 = 45
and so on for 12 months
what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.
Could this be done by date formula?
View 9 Replies
View Related
