I have data in columns a - d; the number of records can vary from a few to a huge number which (to me) makes the problem more complex.
I need a macro that will copy every 3rd record into rows e - h; and every 5th record into columns i - l, etc.;
I am trying to search partial txt with in data range.
View 4 Replies View RelatedI've attached a sample document of my data layout. On the Compare tab, I'd like to build a sumif formula that takes the data in column B and uses it to reference the other tabs without having to use a large nested IF statement. For example, on the Compare tab, in cell C2, the formula will know to look at the "JAN MONTH" tab because B2 says "JAN", then the rest of the sumif would be looking at the data in column A and matching it with the data in column A in the correct tab.
View 1 Replies View RelatedI need to create multiple copy of workbook from a named range called - CoCode - range is on sheet called - Data - cell range - L2:L37. Problem is that i want each workbook named as the name that is in cell range (CoCode). Also in same workbook in a sheet called - Company Summary , in cell J1 there is validation list that refers to range same (Codode) i want that to change in each workbook.
I.e 1st copy of workbook name is equal to 1st reference in range CoCode and same 1st reference in Validation list in - Company Summary - Sheet in cell J1. Also selecting Validation populates several sheets in workbook to information revelant to that company code selection in cell J1, - there are no external links to this document-
I am trying to copy data from one spreadsheet to another but am having trouble with the VBA code. I am new to doing this so have looked online and tried to find the code to do it. I have come up with the code below but it keeps failing at the While section.
Sub Starters_Click()
Dim Counter As Long
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim Source As Workbook
Dim Dest As Workbook
Dim OriginalWorkBook As Workbook
Set OriginalWorkBook = ThisWorkbook
Const MyDir As String = "C:Documents and SettingsAdministratorDesktop"
On Error GoTo Err_Execute
Application.ScreenUpdating = False
For Counter = 1 To 100
Set Source = Workbooks.Open(MyDir & "HRTest" & Counter & ".xls")..............
I have a list of names in column "A" worksheet1. I need to copy the name from "A1" sheet1 to "A10" on sheet2 Then print a range called "Document" from sheet2 Then get the next name "A2" sheet1 etc. down Until it reaches a " " in column "A" sheet1
View 7 Replies View RelatedI haven't work with Excel for a while. I have a "main" file, with info such as name, address, phone #, etc. and another file with additional info for each person required to, in the end, do a merge. How do i combine the 2 files so that the secondary info plugs in next to the last column in the same order as the "main" file.
View 1 Replies View Relatedi am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid)
example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account.
If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1.
If D:D is "payment pending" do same as above but in row 2 and so on.
I think I am closer but how do I get all of the data from A2 in the origination worksheet and then copy it to the last blank cell in column A in the destination sheet?
Code:
Sub test4()
Dim lastrow As Long
Set StartSheet = ActiveSheet
'MFG PART NUMBER
StartSheet.Range("R2").Copy
Workbooks.Open Filename:="D:\_DoxaMobiusServtrax Import Template"
[Code]...
I have a folder full of uniformed web pages ( name and design) and I was wondering if it's possible to create a macro that will open up each page, copy specific data from each page, and paste it into an Excel 2002 spreadsheet. Unfortunately, my skills in VBA are very limited at best and I'm not sure if this is doable or is it something I'll have to go into each page and copy the data.
View 3 Replies View RelatedI'm attempting to copy some files into an archive folder in the same main folder. These files appear weekly in a folder that contains numerous other files.
Each of these files has a filename such as:
RAH_MRL_TOP_40_(ddmmyy_TIME)
with time being the time that the report was run.
Every week the filename stays the same but the time and date changes. I was hoping to be able to simply use the "Filecopy" command and specify the source file with a "*" after the main file name. However this does not seem to work.
Is there a simple way to do this? (I dont want more than a few lines of coding really as I will be duplicating it for each file and then changing the file name as needed.
I have a list of about 2 million phrases. The list was created by combining two lists in all possible combinations:
a list of about 800 street names
a list of about 1000 property names
example
church street flat
church street apartment
house church street
house king's road
etc
what I would like excel to do is the following:
look at each cell and determine which of the 800 street names it contains. write that streetname into adjacent cell.
this would then leave me with the original column, where each of the 2 million cells has been assigned one of 800 streetnames.
for example:
church street flat | church street
church street apartment | church street
house church street | church street
house king's road | king's road
that's all. I think that can't be too difficult, and probably it has already been discussed here in the forum. unfortunately I did not know for which keywords to search, that is why I couldn't find the posts.
I hope somebody can help me as I need to get this sorted for work as soon as possible. All semi-automatic ways I could think of to accomplish this would take me days that I don't have.
I am looking for a macro that will insert a new row and copies the formulas from the row above, but not from all the columns.
This is the current situation:
A
B
C
D
E
F
G
H
I
J
K
L
M
Name
Project Nr.
Work Order
hours
[Code] ......
What I want is that if column G contains "11374340" an empty row will be inserted above and the formulas from column E until M to be copied a row down. And the sum function in the last row of M needs to be changed automaticly as well (if possible).
I already have the macro to insert a new empty row which is as follows:
Sub InsertRows()
Dim i, LastRow
LastRow = Range("G" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "G").Value = "11374340" Then
Cells(i, "G").EntireRow.Insert
End If
Next
End Sub
I have tried to write the below VBA to copy a specific cell to a specific workbook. I have set the folder path in B1 and listed the file names in column E4 onwards. E1 being the number of files in column E. I get a run-time error 91 "Object variable or With block variable not set" on Current File = ActiveWorkbook.name.
Sub UpdateParameters()
Dim CurrentFile As Workbook
Dim wbOpen As Workbook
[Code]....
I have noticed that the basic problem I have is a common one on this forum with different varibles for different people. I have attached a dummy copy of the spreadsheet that I am using.
I need to copy cell information for one spreadsheet to one of 2 other spreadsheets depending on a dropbox condition. The master spreadsheet is the Issues spreadsheet, and depending on whether the user chooses Transferred Complaints or Transferred Offences (in Column K) I need to transfer certain cells to the Complaints or Offences spreadsheets.
The information I need to transfer from Issues is: .....
I am trying to copy partial data from Column A into Columns B and C, then I want to delete Column A without affecting the results in Column B and C. Column A consists of a stock number in the format "1234-56-789-0000" (including the dashes) and is formatted as TEXT.
I'd like to paste the "1234" from Column A into Column B and the "56-789-0000" from Column A into Column C AND remove the dashes (-). I need to do this for the range (or number of rows) that is populated in Column A. This could be as many as 50,000+ rows of data. After this is done, I want to delete Column A. I tried using the LEFT and RIGHT formulas, but they are dependent on keeping Column A intact.
........... A .................. B .............. C ......
1234-56-789-0000 ..... 1234 ..... 567890000
and then eventually like this:
. A ............. B ......
1234 .... 567890000
On occasion, leading zeros in Column C have disappeared, and it's necessary it stay in a 9-digit format. There are 2 additional columns of data, but they shouldn't be affected by the above.
My question, and it's my first one here, is regarding the use of ListViews in Excel forms. I need to populate it with a range of several columns with data from a spreadsheet.
View 4 Replies View RelatedBasically, i have a workbook that contains 2 worksheets with stock data for two stock holding sites, we're consolidating our stores and need to confirm what stock is held at both sites. I need to compare column C on both sheets to find if an item exists on both sheets, and if so copy the entire row from Sheet 2 onto the next free row on a new sheet. Col C on each sheet contains the stock number, however, on Sheet 1 the stock number is abbreviated ot the final 7 letters, with Sheet 2 showing the FULL stock code.
I need whatever is in "Sheet 1:Col C" (for example '1234567') to be compared to whatever is in "Sheet 2:Col C", and if a match is made (for example 9999-00-1234567) then the row containing the match be copied to the next free row on Sheet 3.
I'm trying to get some vba to automatically find the range of data in a spreadsheet (So start from a specified point and loop until it's blank) and populate a combobox with this range accordingly. I have read numerous articles and posts but nothing seems to work!!
I am using Excel 2007.
I've posted this query before, not on this forum, but I don't think the replies I've had so far are going to do what I want. Initially I was looking for a formula, but the suggested pile of nested IFs won't work for the number of conditions. I saw a previous post on here for a VBA macro to search for a text value in a cell against the cell contents of a range and it seemed to do at least the first part of what I wanted. I attempted to manipulate it a little to test its applicability for my own nefarious purposes but for the life of me I can't get it working.
This is complicated by the fact that the actual data is commercially confidential, so I can't show you the actual file, but I can fake what I want with two simpler ones. I've attached them to this post. What I want is a fair bit more complex than the other post I found - I want to be able to compare a partial text string from a given cell in a range ('Check Value' in the attached TestBook2 ) against the strings in a range of cells ('Value 1' in TestBook1), and return the corresponding value from 'Test Value' to the corresponding adjacent cell to the tested 'Check Value', with an order of precedence, for example...
Testbook2 contains an entry in C5 of 'a, e, h, z, x, y'. Testbook1 shows that the return for a, b, c, or d is 'moo', for e, f, or g is 'steve' and for g through q is 'fred', all others being no returned value. Moo>steve>fred, so I want the corresponding 'moo, steve. fred or <blank>' cell to contain 'moo'. Conversely, C6 contains 't, u, z' and therefore shouldn't have a value in 'moo, steve, fred or <blank>'. C12 contains 'f, z, s, y, u' and C15 'i, x, z, s', and therefore should display 'steve' and 'fred' respectively.
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 have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.
Example data extract requirement.xlsx
I have data below, what I am trying to do is to put an "x" if the data has a partial match. This is what I am using below.
HTMLSheet1 ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOL 2Electrical Magnet SchoolELECTRICAL 3Sports Magnet SchoolCOLUMBUS SCHOOL 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOL 6Abe Lincoln SchoolLINCOLN SCHOOL 7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOL 9Abe Lincoln SchoolLINCOLN SCHOOL 10Abe Lincoln SchoolLINCOLN SCHOOL Spreadsheet
[Code] ...........
What I would like is this
HTMLSheet1 ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOLx2Electrical Magnet SchoolELECTRICALx3Sports Magnet SchoolCOLUMBUS SCHOOL 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOLx6Abe Lincoln SchoolLINCOLN SCHOOLx7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOLx9Abe Lincoln SchoolLINCOLN SCHOOLx10Abe Lincoln SchoolLINCOLN SCHOOLxSpreadsheet FormulasCellFormulaC3=IFERROR(IF(SEARCH(A3,B3),"x",""),"")C7=IFERROR(IF(SEARCH(A7,B7),"x",""),"")
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
I need to match up the columns of 1 spreadsheet with those of another. I have a partial address that only lists city and state in one workbook. I need to match these partial addresses up with the master list that contains full addresses. It seems like it should be an easy thing to do, but I can't figure it out.
View 9 Replies View RelatedIs there a way I could achieve copying cell's content minus some characters?
For example in the cell A1 would be: "two apples"
I would put into the B1 cell something like =A1(-4charactersincludingspace) - the result in B1 should be "apples"
Is it understandable what I mean?
[Code] .....
My data are ABC:apple and im looking in a column where data are like these : apple, melon, lettuce.
I have two cells on Sheet 1 - Cell A1 and Cell A2.
Cell A1 is the first cell that will receive either a number, or nothing. When it receives a number, the number will always contain five digits. (For example: '23456'.)
The contents of Cell A2 depend on the contents of Cell A1 - If Cell A1 contains a five digit number, then Cell A2 will display that same five-digit number with '-1' following it. (In our case: '23456-1'.) If Cell A1 contains no number, then Cell A2 will just be another ordinary cell allowing the user to enter whatever he/she desires.
The extension in Cell A2 should remain editable at all times - if the user wishes to change the '-1' to a '-2', for example, he/she should be able to do so without any error messages appearing. However, the user must not be able to change any of the first five digits in Cell A2 as long as Cell A1 contains data. A message should appear stating that changes to the first five digits should be performed in Cell A1 - which would then change Cell A2 accordingly.
Also, if the contents of Cell A1 are erased for any reason, Cell A2 should keep the five digit number, but lose whatever '-x' extension it contained.
Can this be done using Data Validation?
I have had much assistance with this complex (I think it's complex) macro in the past, however, there are still a few bugs that need to be worked out in order for it to be completely efficient. Any help would be really appreciated, and I'll do my best to explain what's going on at this point:
I have a worksheet called the 'Data Entry Page'. You will find it attached. Here are the requirements:
The PO Number cell will only allow ten digits with a dash, and will receive values such as 1200012345-1.
The Part Number cell will receive a value which will usually be six digits long with a dash, and will receive a value such as 654321-1.
The Identification Type cell will receive at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):
I
I I
I I I
IV
IA
I IA
I I IA
IVA
or (some combination examples):
IA & I I I
I I & I I IA
I VA & I IA
I have set up data validation on a cell that produces a
drop down list of part numbers.
Is there a way that I can enable a partial part number search?
If I type in 354, all part numbers that start with 354 will be in the drop down list for me to choose from.