I have a line in my VBA code that i need to change the location of Sheet1 to another workbooks. Instead of Match at Sheet1 at the same workbook I need to match with sheet1 at another workbook called ("Master.xls). The Original Line:
View 3 RepliesI have a workbook with approximately 30 worksheets. This workbook already has a massive macro that I've written. I'd like to write a code that will take a designated worksheet, check to see if there is data in cell A2, if so, save it as a new workbook.
The name of the workbook should be predetermined, for example "SIA April(Previous Month) 2008(Current Year) P-Card Import Template.xls"
The months and years will need to change.
The file to save it in will also change monthly- for example- G:PCard DirectoryCloses2008 ClosesApril(previous month) 2008(current year)
I would like to know if it is possible for the output from a match function to be correlated to a location? The output I receive is just a number. I would like to use this number as the row, but I would like to hyperlink it into a formula so that I can create a macro for this process.
In essence I would like for a number in one cell to become a location in another cell.
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.
If a INDEX,MATCH function returns a zero, how do I get it to use the value in the adjacent cell which is a unique number?
=INDEX(Sheet2!A:C,MATCH(A2,Sheet2!A:A,0),3)
Example:
I have a workbook name Sample and I want the code to run when its opened and it should check the first sheet "Sheet1" in all the workbooks in the folder C:FolderTemp.
And if the first sheet name is not "Sheet1" then just ignore that workbook and move to next workbooks. If the first sheet name is "Sheet1" in a workbook then store the value present in cell B1 in Sample workbook "Sheet1".
So if there are 5 workbooks in the folder C:FolderTemp and two of them hasn't got first sheet named "Sheet1" then display value of B1 in rest of the 3 sheets to the "Sheet1" of "Sample workbook" one after another.
Any way to do the following macro. I am trying to search column A on Sheet 1 and see if it finds the match on Sheet 2. If it finds the match it will delete the row on Sheet 2 and move all the information up and then move onto the next value in Column A in Sheet 1.
TestSheet.xlsx
I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.
Is someone aware of a function which will target a relative location instead of an exact cell?
I have a folder which contains multiple 'Customer' workbooks (example attachment 'Customer_001'). Each workbook has a filename unique to the customer (Customer_001, Customer_002, Customer_117 etc). The workbooks contain a single sheet with customer information and answers to questions. These 'Customer' workbooks are automatically saved into a folder once the customer completes a Userform and clicks 'save'. Potentially, there could be 100's of customers' workbooks saved in the folder, each with their own unique filename.
I also have a 'Master' sheet saved in a different folder (example attachment 'Master'). The 'Master' workbook has multiple sheets named 'Department 1' and 'Department 2'. The purpose of the master sheet is to consolidate all information from the individual customers' workbooks.
Specifically, I would like a command button on the 'Master' workbook to execute the following tasks...
1. Copy the data from range A3:F3 from each of the 'Customer' workbooks held in the folder.
2. Paste the data into the next blank row on the 'Department 1' sheet in the 'Master' workbook.
3. Copy the data from range A7:F7 from each of the 'Customer' workbooks held in the folder.
4. Paste the data into the next blank row on the 'Department 2' sheet in the 'Master' workbook.
5. Save the 'Master' workbook.
6. Delete all 'Customer' workbooks in the folder.
I am trying to change data vailidation settings on Sheet1 based on a state name provided on a separate worksheet (Sheet2). The below code is part of a macro that is assigned to a button on Sheet2. The issue is when the state name changes on Sheet2 and the button is pushed to initiate the macro to run, the state validation does not change on Sheet1.
View 2 Replies View RelatedI can't understand why my rate1() function (my macro) doesn't work properly. While I'm dragging M5 cell in Sheet2 down to M14 cell it gives the wrong result.
Even when I change value in Sheet1 and refresh data of pivot table in Sheet2, my function rate1() isn't updated. But my problem is solving (getting the correct result) when I'm going Edit Mode (pressing function key F2) in active Cell M5 or M6 or M7..so on and pressing Enter each time.
compare two lists of data in order to identify the possible matches considering date&time and the location stored in different columns as shown in the example file attached. The range of date and time for the comparison is one hour, but it can be changed...
View 4 Replies View RelatedI have a row b1, b2, b3, b4, b5 etc. I need to change it to c1,,c2,c3,c4,c5 etc. Is there a fast easy way to do it. I know about $ but I'm past that.
View 6 Replies View Relatedwhich part of this macro I need to edit to make it open a folder I want?
Sub OpenFolderRequest()
YesNo = MsgBox("Would you like to open the folder to see" _
& vbCr & "which files are currently there?", vbYesNo + vbQuestion, "Open Folder?")
Select Case YesNo
Case vbYes
myval = Shell("c:winntexplorer.exe c:winnt", 1)
Case vbNo
End Select
End Sub
I have tried changing the (c:winntexplorer.exe c:winnt) to the location name of the folder I want to open (S:Stock ControlSTOCK CONTROLOrder Confirmation)but it doesn’t seem to work.
I am using the below code and it is not performing the operations of comparing and deleting the duplicate values from sheet1 and pasting unique values in sheet 1, p.s. Do not need values from sheet 2, just want to compare the sheet 1 with 2 and delete dups in sheet1.
[Code] .....
i am looking to create a small table from the attached worksheet that will show how many users there are for 3 different locations, the user names are in column a and the locations in column c. the thing is, the actual worksheet i am using in work contains almost 1000 lines, and is being updated daily, there can be multiple entries for a person for projects etc.. and someone can even be in the availability and pto area's without being in the main project area. each resource can only be on one location so that will not change.
also, there can be blank cells for resource which should not be counted. i was wondering if there was a formula or a macro that would count the distinct number of names and reference them to the location and give a count of resources by location?
When I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop?
In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?
I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.
Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.
I created a drop down in a file that contains a list of locations. The people that run these locations have to input data (mileage) in other areas of the worksheet. Is it possible to have the data in the cells change as someone changes the location in the drop down so the information is not lost or overwritten each time?
I am really trying to avoid making a workbook with several tabs.
The cells that are colored blue are the cells that the people will have to put input into. I would like to be able to keep the data from all the cells that are blue each time they change the location. The rest of them are already set to change with the location change via a formula. Those are all based on data that I pulled and was able to provide.
The comment boxes keep changing shape and location in my spreadsheet. Some are completely smashed together, while others are extraodinarily wide. I had them all to the right of the cell they applied to; but now they are all over the place.
View 1 Replies View RelatedI have a file that has 114 rows of data. One for each office I'm working with. I need to insert 6 rows in between each of those rows and type text into some of the fields. I recorded a macro (since I don't know how to write one), but it uses a static location for the rows and data, as opposed to a relative location. So, what happens is, I get the same data entered over and over again. Also, even if I get this to work right with a relative location, I still would have to run it 114 times. I'd like to create one macro that would do the entire document. Here's what I've recorded: .....
View 9 Replies View RelatedWhen someone right clicks on a cell and chooses "Hyperlink" current folder is being displayed as a default.
is there a way to change this for a particular workbook through VBA or API calls ?
I would like to always have "c:" as the default folder no matter where the workbook is stored.
Trying to set this code to position the input box over the target cell so a user has to fill in the box before proceeding. Is this possible?
ElseIf Target.Value = "Plate" Then
Add = InputBox("Please Enter # of sides to be Painted. If Not Required Enter 1", "Paint Sq.Ft.", "1", "c", "Target")
If Add = "2" Then
Target.Offset(, 19).FormulaR1C1 = "=RC[-9]*2"
Else
Target.Offset(, 19).FormulaR1C1 = "=RC[-9]"
End If
I Know input box movement is set as twips but can i just somehow specify the intersection between row c and target as it's position?
I am trying to achieve can not be based on a range of rows or cells it must as this data is imported from a forecasting application and the location of the targets may change.
Perhaps an offset to the current selection can be used some how, but have a look-see if you know where Im coming from.
'I have used this to find a target in a data range.
Dim r1 As range
Public Sub FindDataIn Range(r As Range, target As Variant)
Set r1 = r.Find(target)
If r1 Is Nothing then
Msg Box target & " was not found"
Else
r1.Select
End If
End Sub
'Then I inserted and Named this procedure to find the data on various assumptions or targets - SUCH AS THE VALUE "14306".
FindDataInRange ActiveSheet.Range (A1:A226), "14306"
Selection.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=x1Down
Selection.EntireRow.ClearContents
Then I write this again for another Target such as 14307 and it repeats
The result is that it finds the target cell I get an empty row above the two rows that contain that target. (LET ME EXPLAIN WHAT I MEAN BY TWO ROWS)
The thing is there are two rows containing 14306 in that range and the range is sorted ascending so that they are positioned one under the other. Each row has different forecast totals beside this number because one is an export SKU and one is a Domestic.
What I want to do is combine the two rows as one with one row of forecast totals for the number rather than two.
Like this:
14306big Widget Domestic 26 89 (This is combined as a new row)
Instead of this:
14306big Widget Export 12 14 (These 2 rows are then deleted)
14306big Widget Domestic 14 75
Also the Forecast totals run across 12 columns (one for each month) and then there is a column for year totals of each row that needs to remain the same.
Most important this here is this cannot be based on a range it must as this data is imported from a forecasting application and the location of the targets may change.
I have the following code written but I'm wondering if it's possible to modify this to change the red line to update to the path that the workbook is saved in? Meaning that User1Folder1 would change but [Workbook1.xlsm]Sheet1'E1 would always be the same.
[Code] .......
I use an image analysis program ImagePro which can call Excel within it's macro language (which seems to be visual basic). I looked up on this site how the change the drive (ChDrive command). But still when the Excel section (after With oExcel) executes the default file location in the browser is in My Documents on the C: drive.
Prior to this code Excel has been launched by this ImagePro macro, and a file Cumberland Template has been opened from the C: drive. Now I would like to do a Save As, but have Excel start at the I: drive location.
I have a formula in Cell A1. It tells me the cell location of the last number in column E. So, for example, I input the number 44 in cell e85, the value for cell A1 becomes $E$85.
Let's say I now put the value 33 in cell e86, now the value for A1 becomes $E$86.
I want to calculate the average of the values in column E, all the way from E1 to whatever is in A1.
Here's what I need: =AVERAGE(E1:<whatever is in A1, like $E$85>)
How do I write the AVERAGE formula to make this work?
I am having some trouble getting a formula to work. I am building a report that pulls figures from a pivot table in another workbook. I am using a vlookup with match function to get the column index to find the relevant data I want. Where I need to add two columns together I am using sum, with the vlookup & match formulas nested in them e.g.:
=SUM(VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("FAID",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("BPCM",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE),VLOOKUP(F13,'[PIVOT 156.xlsx]PIVOT'!$C:$AQ,MATCH("COMD",'[PIVOT 156.xlsx]PIVOT'!$C$6:$AQ$6,0),FALSE))
Where:
F13 = Employee number
Column C on the pivot 156 workbook is where the employee number is based.
The Match formula is then getting the column index from the column headings of the pivot table ie. "FAID"
This in itself works fine, as long as it finds a match in the column headings. This is where i get the error as in the above function "COMD" is not in the pivot table. However I need to keep it included as it may appear on a future pivot table. Is there a way of getting the sum function to complete even though later in the formula it can't complete the vlookup? So it will ignore it, or assume the value is zero if it can't find it? The formula probably needs to do this for all the vlookups as some headings may drop off in future pivot tables.
I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)
View 2 Replies View RelatedI need code for a search box function, that returns the information recorded in a cell for example, "Barcelona" or "London" etc), instead of the location of the cells.
I will need to narrow it down to search only the information in the following columns:
Sheet2
I2:J10932
I am totally new to VBA coding and have stumbled my way through a few things, but everything I have searched for so far has had at least one error when transposing to Excel.
I am running Excel 2007.