Insert Row At Variable Location

Dec 3, 2009

I had a quick question for something that should be simple but I can't figure it out. I have a column of sorted values, it goes

A
A
A
A
A
B
B
B
B
B

and I want to insert a row to separate the A's from the B's. But there will be a random number of A's. I can figure out how to count where the A's end. But I can't figure out how to insert a row. Any suggestions? I tried the two below but they don't seem to work. The for section is counting the A's, and it works, it's what comes next that's giving me a headache!

View 2 Replies


ADVERTISEMENT

Interpolation Given A Variable Table Location & Location Of Data Within

Oct 6, 2009

I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow.

simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.

View 6 Replies View Related

Insert Rows At Specifice Location

Nov 30, 2006

I need a macro that will look in column B of a worksheet for the value "Grp2" and begin inserting rows underneath that value based on a variable that I will pass to it. The variable will be how many rows to insert.
For example if value "Grp2" occurs in cell B19,beginning at cell B20 I want to insert a specified number of rows.

******** ******************** ************************************************************************>Microsoft Excel - Test2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutJ19=
ABCDEFGHI19XLRTGrp2CODERTSCFLTROASMLTGDOP*20*Begin*insert*rows*here*******21*********22*********23*********24*********25********326XLRTGrp3CODERTSCFLTROASMLTGDOP*27*********Test*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

Aug 8, 2009

On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

View 4 Replies View Related

HLOOKUP With Variable Range Location

Aug 28, 2013

I have an issue with the below code:

VB:
LastRow1 = ActiveSheet.UsedRange.Rows.count
ActiveSheet.Range("R1:R" & LastRow1).FormulaR1C1 = _
"=IF(RC[-1]<>"""",HLOOKUP(RC[-1],Sheet2!R60:R61,2,FALSE),"""")"

The issue I have is that in sheet2 the HLOOKUP range i want to lookup will not always be in R60:R61 these rows vary depending on the amount of data above this range... is there a way of incorporating some sort of

VB : LastRow = ActiveSheet.Range("B" & Rows.count).End(xlUp).row + 1

To locate the bottom of the data and the start of the HLOOKUP range?? if is there some way of re-coding it?

View 3 Replies View Related

Store Cell Location In Variable

Jul 13, 2006

way to store a cell's location to variables.

Something like:

int a, b
Cell(a, b) = ActiveCell

I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.

View 6 Replies View Related

Create New Folders In Variable Location

Oct 17, 2006

I would like to create new empty folders from the list in column B.

The number of folders will vary depending on how many entry in column B.

I need the folders to be created in an existing folder in the "current directory" called "Shop_Drawings".

The following code may be able to be modified.

Sub CreateFolders()
Dim MyFile As String
Dim sDir As String
Dim rng As Range

Set rng = Sheets("Matdata").Range("B2")
While rng.Value <> ""
MyFile = rng.Text

sDir = "CurDirShop_Drawings" & MyFile
''above is where I am having trouble...don't know the correct syntax''
MkDir sDir


Set rng = rng.Offset(1)
Wend
End Sub

I have attached a sample workbook.

I have been getting by with code which requires changing the destination in the module whenever making folders in different diectories or drives.

View 6 Replies View Related

Insert An Icon Relative To The Button Location

Jul 30, 2009

I was wondering if i could get a hand with inserting a file. Is there a way to insert the file and have it placed a relative distance from the insert button without using cell references?

View 2 Replies View Related

Insert The 'location' Of File Into The Spreadsheet Footer

Jan 21, 2010

I would like to use macros to insert the 'location' of my excel file into the spreadsheet footer. I have the following code already:

View 3 Replies View Related

Insert Row At Specific Location And Auto Fill Formulas From Above

Apr 11, 2007

What I need is to insert a row at row 59 and autofill the formulas including drop down list from the above row. This is what I have so far (Thanks to Reafidy and shg).

Sub ChkDates()
Dim c As Range
Dim DelRng As Range
Dim ArcRng As Range
Dim i As Long
Dim l As Integer
Application. ScreenUpdating = False
Worksheets("Report").Activate
For i = 60 To 8 Step -1
Set c = Cells(i, 33)
If IsDate(c) Then ..............

View 9 Replies View Related

Circular Reference - Value Of That Variable Changes Depending On The Location Of The Cell

Mar 16, 2009

Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.

Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference?

The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.

View 4 Replies View Related

Excel 2013 :: Macro - Paste To Column Cell Location Based On Variable?

Jun 16, 2014

I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.

I have excel 2013.

View 4 Replies View Related

Finding A Cell In A Variable Location Based On The Contents Of The Cell

May 2, 2012

I am creating a macro to automate data analysis for work, but I've become stuck.

I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.

View 1 Replies View Related

Insert Variable In Formula

Nov 3, 2009

I'm using Excel 2000/2002. I have a workbook with 12 sheets named Jan, Feb, etc.
I want to add a new sheet (Report) with formulas in various cells to get data from a cell in a particular sheet.

For instance, in a cell of the new sheet is: =Jan!D64. I want the user to be able to select a month from a drop down list and for the formula to change sheets depending on the month selected. The formula should be: =(sheet name!)D64. I tried playing with INDIRECT, but maybe couldn't get the syntax right. I can't use macros, the Excel is on a server and they are not permitted. The end user is less knowledgeable in Excel than me.

View 2 Replies View Related

Insert Variable Graphic

Dec 5, 2006

I have a folder of numbered graphic files (500 odd) and want to insert the correct graphic in a spreadsheet depending on the value of a cell (the value will be the graphic name). Thus everytime the cell value changes, the correct graphic file will appear.

View 9 Replies View Related

Variable Columns How To Insert Formula

Mar 25, 2009

I have to work on sheets with variable columns.

So for eg Col A Col B Col C Col D are fixed and then there are variable no of columns - Col E Col F and so on....

I have to insert columns after the last colum ie in Col F in this case.

However, the new column (lets call it Col G) will give a value with formula that connects Col E value with Col B (fixed)value. Again Col H (another new col) will also give a value with formula connecting Col H with Col B(fixed).

Easy to do in normal scenario but when I record macro in excel ( i cannot write VB), the variable columns make it difficult.

View 9 Replies View Related

To Insert A Variable Between Quotation Marks

Apr 21, 2009

I have the following line of
Selection.AutoFilter Field:=1, Criteria1:="=2009_15", Operator:=xlAnd

The highlighted portion is going to change from week to week. I have never been able to put a variable inside of quotation marks. I am sure it is simple, which explains why it is out of my mental grasp. how I can assign a variable in there?

View 9 Replies View Related

Insert Formula Across Variable Number Of Columns With VBA

Jun 19, 2014

I have two variables - the number of stages, and the number of people.

The stages will start in say C22 through G22 (if there were 5 stages). The number of stages will vary upon a user-inputted number in A1.

The names of people will start inB29, so I'd like to insert the simple formula (=Max(C23:C28)) across cells C29-G29 (again with my example of 5 stages), with relative references to the columns, of course.

Ditto for the next name in B30, etc, so that I get the max value for Person#1 in Stage 1, 2, 3...Person#2 in Stage 1,2,3...

Inserting the formula over the dynamic ranges.

View 8 Replies View Related

Insert Variable From Sheet Cell Into SQL Statement In VBA Code

Oct 29, 2013

I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.

Here is my code

cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear

Set rs = New ADODB.Recordset

[Code] ........

This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.

View 1 Replies View Related

Variable File Name And Looping Macro To Insert Formula?

Jun 13, 2014

I would like to create a change event macro that will update multiple formulas that pull data from various tabs on a file. The tabs are named with a single date. So ideally when a certain master cell (Allocation!H2) is updated to the current date the macro will be prompted to update the range that contains formulas and update accordingly...so its and index match fomula based that has a variable being the tab name which is a date. I have a range of dates in cells F4:AB4 and want the formula to go into F5:AB5, I can then fill that down to whatever row I want...that bit I can handle.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Worksheets("Allocation").Range("H2")

[Code]......

View 4 Replies View Related

Copy And Insert Rows From List To Ws Based On A Variable No

Dec 20, 2006

I have a bit of a curly one - I have a sheet with 9,000 records that I need to expand to 24,000 + on a separate sheet records using the following method:

1) Select and Copy entire row 2 from sheet(1)
2) Insert a number of new rows into sheet(2), based on the value of cells(2,8) or cell H2. Column H contains quantity values for each of the 9000 rows which are variable.
3) Repeat for row 3, copying and inserting into sheet(2) based on the value of the cell in H3.
4) loop through all records until complete

I have tried writing the code in VBA but am not even getting close!

View 9 Replies View Related

Excel 2010 :: Search Variable Folder And Insert First JPEG

Jul 17, 2013

I am after tips on creating a macro in Excel 2010 where it will search a list of folder paths in a column on a spreadsheet and insert a jpeg within the sheet from that folder if it exists. If more than one jpeg exists i would like it to insert the first jpeg only. i have found tips where it will insert images if you know the filename, however I don't have this luxury as file naming routines vary from folder to folder the only constant is that the file is a .jpg format.

View 1 Replies View Related

Insert Column Macro Based Variable String Letter

Jun 11, 2008

I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).

View 4 Replies View Related

Insert String And String Variable Into Cell

Feb 18, 2014

I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.

View 2 Replies View Related

Set Variable: Object Variable Or With Block Variable Not Set

Jun 4, 2007

I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"

Sub test()
Dim StartRng As Range
Dim Buffer As Range

Set StartRng = WorkSheets("Sheet1"),Cells(1,1)

StartRng.Activate
ActiveCell. CurrentRegion.Select

Buffer = rngStart.CurrentRegion.Copy

' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub

View 9 Replies View Related

Excel 2010 :: Cell Insert In One Column To Insert In Other Columns?

Mar 23, 2012

I have a master data sheet with four columns, A, B, C and D

Column A has the primary data and B,C,D has dependent data values;

So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;

View 2 Replies View Related

Insert Excel Formula Using VBA......MATCH Function Is What I Want To Insert

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

2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set

Feb 20, 2009

I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...

The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.

View 9 Replies View Related

Summing Variable Values Across Variable Sheets In Multiple Columns

Jun 27, 2014

I need a macro that will create a sheet at the end of the workbook.

Sum data from a variable amount of sheets and display that data on the created sheet.

Here is a step by step:

Starting on sheet 5.

Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.

Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.

(Note: The data would also need started on row 4. Everything above row 4 is headers)

Here is a small example:

D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1

I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).

After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.

So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).

View 2 Replies View Related

Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time

Dec 7, 2008

I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved