I want to insert a cell reference into the code below to replace the hard coded "100500" so the value in cell A1 replaces the criteria below. Is this possible ?
Rows("3:3").Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$B$17").AutoFilter Field:=2, Criteria1:="100500"
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
I need to open a customer-submitted workbook which has a list of items in column B, marked by an 'X' in column A on selected rows. Each customer's list is tailored to them with, say, 25 rows each.
I have a master list with identical formatting but which has the data from ALL lists combined.
I want to insert an 'X' in column A of my master workbook for any matching items for which there is an 'X' in column A of the customer's workbook.
I could do this with a formula in column A of my master list, but each customer's workbook has a unique filename and I don't know how to make the formula use a changing lookup array, so I figured this is something that I have to accomplish with VBA.
I want to link cells B1 thru B16 to Cells F1 thru F16 receptively.
However, at times I need B1 thru B16 to show other columns 1 thru 16.
I would like to type in the column designation in A1, ie. F,G,H,I.....and have the values of those columns shown in B1 thru B16.
The first three columns of a spread sheet we use if function to insert values to these cells based on the next three column values. We have already done it using IF function. However, the same function should happen through macro by referring to the column headers.
Example 1:
If in column E header (E1) ‘Contract_Status’ and E2 cell value is ‘FAIL TO PAY’ then A2 value should be ‘No Owner’, B2 value should be ‘Terminated’ and C2 value should be ‘FTP’
Example 2:
If in column E header (E1) ‘Contract_Status’ and E3 cell value is ‘TERMINATED’ then A3 value should be ‘No Owner’, B3 value should be ‘Terminated’ and C3 value should be ‘TERMINATED’
Example 3:
If in column E header (E1) ‘Contract_Status’ and E4 cell value is ‘EXPIRED’ AND column F header (F1) ‘Contract_Renewal_Status’ and F4 cell value is ‘Cancelled by Customer’ then A4 value should be ‘No Owner’, B4 value should be ‘Renewal Cancellation’ and C4 value should be ‘cancelled’
Can anyone help? I need to achieve the following involving the insertion of rows from a specified value reference within the worksheet to which the rows are being added.
The original data would look like:
ABCDEFG1PeterABC, DEF, GHI32DavidABC, DEF23SamABC, DEF, GHI, JKL44TomABC, DEF25
The number of rows to be inserted under each entry is listed in column "C" (which is a count of the separate entries in column "B".
The output data following the application of the "solution" would need to look as follows:
ABCDE1PeterABC, DEF, GHI3ABC2DEF3GHI4DavidABC, DEF2ABC5DEF6SamABC, DEF, GHI, JKL4ABC7DEF8GHI9JKL10TomABC, DEF2ABC11DEF
Where the specified number of rows have been inserted underneath the original entry and the separate values in column "B" have been listed in consequtive cells, relating to their original entry in column "D".
Can anyone suggest code to achieve this outcome?
I have referenced data in two colums on a sheet A and B. Column A contains the latest data, each month i insert new column (moving column A to column B). However all of my references continue to follow the original data (eg will change from column A to column B). this happens despite using Absolute references. (=$A$1). Is there a way to lock these cell references to only ever display column A etc?
View 2 Replies View RelatedA 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.
When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")
ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.
I've a workbook in which I run an excel macro to filter out data for five regions W1, W2, W3, W4, W5, the macro creates five sheets (w1, w2, w3, w4, w5) and places them in the same workbook along with the original master sheet called "west".
The data in all the sheets is layed out in an identical fashion except that the the number of rows in each sheet will differ depending on the number of records for each region in the master sheet "west".
I was looking for some vba coding to automatically add sum totals in two columns (I & J) for all the five region worksheets.
The first record always begins from row 4, in all the sheets.
so as an example W1 sheet could have a sum formula in I20 = Sum (I4:I8)
and in COl J as Sum(J4:J18). Row 19 is a blank row, and the intention is to leave a blank row just before placing the sum total in all the sheets.
W2 will have the same starting range but might differ in how many rows to sum .
And so on for the 4 remaining region worksheets in the workbook.
I do not know VB coding but is in need of help on data filtering/matching. I have 2 very big excel workbooks with a few thousand lines and am trying to do some filtering/matching. Example below.
The two workbooks I have contains same data in column A and also column D from the first data set is the same as column B in the 2nd data set. I am trying to add column C and D from the 2nd data set to the 1st data set and need to make sure they match up to column D from 1st set and B from 2nd set. It is quite strenuous to do this manually,
A1 2 3 100A
A1 2 3 100B
A1 2 3 100C
A1 2 3 100D
A1 100A AA BA
A1 100B AB BB
A1 100C AC BC
A1 100D AD BD
I have an excel spreadsheet set up as shown in the attachment. When I insert data by using the macro (Insert farm), for some reason it puts a NA in a cell (C17).
I then select the cell click into the formula and click enter and it works. how can this be fixed. Look at the excel spreadsheet to be able to understand.
I have a spreadsheet. This has columns begining with A to S (with data in it)
It has rows beginning on row 5 and goes down to 85 but it gets bigger every day.
I want to create a macro with several phases
Phase 1 - Select the rows I specify, ie pop up box to decide on the rows
Phase 2 - Filter down and select the rows in this selection where column K has a value (unselecting the rest)
Phase 3 - Copying Columns A, B, D, E, F, K, from the selection
Phase 4 - Pasting these columns into A, B, C, D, E, F, of the sheet the macro sits on....
I have a written a macro to populate a filter criteria with a comma separated list, but the filter fails to work as each individual item on the list needs to be surrounded by speech marks (") and I don't know how to do this.
This may not be an actual vba issue?
The list I am using is derived from =SUBSTITUTE(TRIM(BK9&" "&BK15&" "&BK21)," ",",") There are 120 different cells that this formula references that could be blank. The SUBSTITUTE TRIM functions allow me to just use the cells that have data in. But I can't seem to get the " symbols in there. (I've tried the TEXT function on the original cells, & this doesn't work.
I'm having with creating a macro to filter some data from one sheet to another. I used the "record a macro" function to get the code I've posted below but part of the code is always changing.
In my workbook I have a main sheet called "Data" and another sheet called "Parts". The code below does what I need it to do, but the problem is that the cell range (A2:I139) is not constant and may change depending on how many rows of information I import on the "Data" sheet.
What I'd like to have happen is either have a dialog box pop up and prompt me to select the cell range (I've tried using the Application.InputBox function but can't figure it out), or since the first cell will always be A2 and the last column will always be I, have excel automatically figure out the last row that contains data.
I would like to use a dropdown box for all the names in my worksheet. Once a name is selected have all the rows filtered on that name and copied to a new workbook.
I think I'm making this harder then it is, but I'm just not comprehending what needs to be done.
I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.
View 9 Replies View RelatedI want to make a filter started from row 2
the criteria should not be equal to B1
weekucase = UCase(Range("B1"))
Rows("2:2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:=" weekucase"
Rows("2:2").Select
Range(Selection.Offset(1, 0), Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
End Sub
But it doesn't work
I've got a problem that's causing me a headache. i have a document with 1 column and 4000 rows (its an export from active directory).
I need to do a search cell by cell (so A1 - A4000) for any cells that contain the text "changeType". When a cell is found, i need excel to insert a new row above the current cell and insert the text "modify" into the new row (in the first column).
eg.
x
x
x
changeType
x
x
Becomes:
x
x
x
Modify
ChangeType
x
x
I created a contact list that incorporates buttons at the top of the page to sort the data based on two customer type columns (thanks to Richard for help getting this far!).
I am attaching the file so that it will be easier to take a look. Four of the five macros are working fine but the second one ("All Clients") filters on the premise that "yes" is answered in either of the two customer type columns. In other words I am trying to show the records that have "yes" entered in either of these two columns, not necessarily both columns.
What I want to do is pull data from columns A,B,C,D in 3 other closed workbooks (export.csv, export1.csv, & export2.csv) to my main workbook (Thunder.xlsm)and append that data in columns A-D on my main workbook. In addition I created a list of 10 names (but need to expand or delete from this list as personnel change) in Column O that I would like to filter the data in A-D. The names in the list must find the data I want to keep in column D, the rest I want to delete (not the entire row, just A-D for that particular row that does not match the criteria in column O, and shift those cells up).
I've attached a sample spreadsheet that will clarify what I am asking. I'm preferably asking for 2 macro's, one for the pull of data, and the other for the filtering.
Column A
Column B
Column C
Column D
[Code]....
I have a project that I'm going to attempt to handle, unfortunately I have no idea where to start. So any ideas or input to get me in the right direction would be greatly appreciated!! I am new to macro programming in excel and have a very light background in programming.
What I have:
Right now I have a data file(Sheet1) that lists the data of various accounts. Each row is a different account and each column is data that pertains to the account. Ex. Row 1 will be account # 1, with things like name, account number, address, etc... in columns A B C and so on.
What I want to do:
I need to make forms for EACH account in another sheet (sheet2), and in that form i need to reference specific data from Sheet1. THe reason it needs to be referenced is beecause if we make changes to Sheet1, the form will also need to be updated. Also, I dont need all the data in sheet one for each account, I will be pulling out data from different columns, ie. only columns A, E, F, etc...
I have the following VBA: ActiveSheet.ListObjects("Dashboard").Range.AutoFilter Field:=64, Criteria1:="Adams, Lon"
The problem is when I move columnsin my worksheet, my VBA quits working. I'd prefer to use a table column name like this instead:
ActiveSheet.ListObjects("Dashboard").Range.AutoFilter Field:="Dashboard[BC Owner]", Criteria1:="Adams, Lon"
But I can't get the VBA filter to work with a reference name / table column reference.
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
I am trying to write a formula to insert in a macro for multiple lines of data.
Column B contains the date that I am comparing the date in Column C against. I want to be able to highlight the content on that row if the date in column C is greater than or equal to column B. how to write this?
Sample data:
3/12/20123/12/2012
3/12/20123/1/2012
3/6/20123/6/2012
2/29/20123/2/2012
I would need row 1 and 4 to highlight in red. this is part of a long Macro that is written and includes many other steps, but I cannot seem to make this step work correctly.
Worksheet excel with multiple lines and colums. I need a macro to automatically insert a new Line where the cursor is located, and duplicate data from previous cells: let say cursor is located L10, macro will insert L11, and duplicate L10 -C1 data to L11 -C1. I have a macro that does this function, but the Line insertion is always at the same line.May be the macro should "read" first the cursor position, Lx and then move down +1 line prior to duplicate the data.
View 2 Replies View RelatedI have written the below code to enter some formulas, validation, and formatting. I keep running into a problem when trying to add the first validation.
The debug msg is...
Run-time error '-2147417848 (80010108)':
Automation error
The Object invoked has disconnected from its clients.
Debug highlighted portion is...
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PRODUCT" ....................
when you decide to cancel the option from choosing any picture I keep getting the debug error, "Run-time error '1004': Insert method of Pictures class failed". I would like it not to error out at all, obviously.
Sub InsertPicture()
Dim myPicture As String
myPicture = Application.GetOpenFilename _
("Pictures (*.gif; *.jpg; *.bmp; *.tif),*.gif; *.jpg; *.bmp; *.tif", , "Select Picture to Import")
'If myPicture = Null Then
'MsgBox ("You did not make a selection.")
'Else
If myPicture "" Then
ActiveSheet.Pictures.Insert (myPicture)
ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Select
With Selection
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = ActiveCell.RowHeight
.ShapeRange.Width = ActiveCell.ColumnWidth * 5.25 + 4
.Placement = xlMoveAndSize
End With
End If
End Sub
Private Sub CommandButton1_Click()
InsertPicture
End Sub
I need a macro in Excel sheet.
What I need is If Cell Value A4 = India then it should insert one row automatically after Row A10 & Value of Cell A 11 should be "VAT".
how to reference cell A1 in a macro?
View 9 Replies View RelatedWriting code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
I'm trying to write a macro similar to the one found here: here:http://www.techonthenet.com/excel/macros/checkbox.php.
Rather then use a textbox, I would like the cell to display the date when text is inserted in the cell to the left.
ie. I insert text (the letter 'a') in cell E11, and the date appears in cell F11.
I would also like the macro to do this for a range of cells ie. for E11 to F21, then from G11 to H21, then from I11 to J21, all the way to column IV.