Find Cell From Another Workbook
Dec 6, 2013
I have to open another work book to get a certain cell with a value.
ex. in my source file (prod plan) i have columns A,B,C,D. in column B it contains Line1 up to Line10. In column C it contains Model and Column D it contains Quantity.
Prod Plan workbook
Sample:
A-----B-----C------D
---------------------
AAA--Line1--M1----87
BBB--Line2--L2-----45
CCC--Line3--X1----0
up to Line 10
I need to find all Lines (column b) as my reference to get the column D records using Excel Macro and paste or copy to another worksheet. Is this possible to use the IF to get the data from Column D or Looping. How to make this in Excel Macro.
Desired Result:
Line1|87
View 3 Replies
ADVERTISEMENT
Jun 20, 2008
I'm trying to figure out a way to find a specific sheet in a workbook that does not contain the macro. Within the macro I have a cell which holds the name of the specific sheet I would like to find but I can't get it to work for some reason...
'Dim officen As Integer
'Dim thiswb As Workbook
officen = Range("A2").Value
Set thiswb = ActiveWorkbook
' Open the Active Info file
Workbooks.Open "C:My DcoumentsActive 20080616.xls", , , , "xxxxxx"
' Dim sourcewb As Workbook
Set sourcewb = Workbooks.Open"Active 20080616.xls"
Sheets("officen").Select
RowCount = ActiveSheet.UsedRange.Rows.Count
Range("B2").Select.............................
View 8 Replies
View Related
Jun 17, 2008
I have Quote Master.xls open
I have a value in AA1 that carries a number
I want to open Quote Log.xls
I want to find the cell in Column A (Quote Log.xls) that AA1 (Quote Master.xls) directly corresponds to
I want to offset from that found cell 3 cells to the right and insert from T7 (Quote Master.xls) into this offset cell. Here is the code so far
Private Sub InputIntoQuoteLog7_Click()
Dim CostSheetBook As Workbook
Dim QuoteLogBook As Workbook
Set CostSheetBook = Workbooks("Quote Master.xls")
Set RFQQNumber = CostSheetBook. Sheets("RFQ").Range("AA1")
Set RFQQStartDate = CostSheetBook.Sheets("RFQ").Range("T7")
Workbooks.Open ("\ACT3ENGVAULT EngineersLsheriffDocuments (2008)Quote System MashQuoteLog.xls")
Set QuoteLogBook = Workbooks("QuoteLog.xls")
Set vOurResult = .Find(What:=RFQQNumber, After:=[A1], _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3)
vOurResult.Value = RFQQStartDate.Value
End Sub
I get an un-numbered error on the .Find
View 8 Replies
View Related
Aug 12, 2008
I am trying to complete these steps:
Take a value from AK59 from the "EQF" tab in a file called "QuoteMaster.xls"
Use that value to find match in column "A" in a Seperate workbook "ITMSTR.xls" on the "Sheet1" tab
Offset to the right 1 cell of the found value, take back to the "QuoteMaster.xls" and put in cell "AN59"
My code comes up with no errors, it opens the file and closes is as shown, but it doesn't copy the value over for whatever reason...
View 3 Replies
View Related
Mar 16, 2013
I am trying to simplify process for the users of a workbook. This workbook has about 20 tabs and I would like the users to manually paste a value in C1 of sheet1 and have a macro button that will find that value in the whole workbook.
View 3 Replies
View Related
Nov 6, 2013
I am needing a macro that will search all excel files in directory C: and open the workbook with cell F4 value equal to "Checklist".
View 9 Replies
View Related
Feb 14, 2008
What I have is a collection of about 500 .xls files that I want to import into a single worksheet in a new workbook, but I only want to bring in specific cells from the source sheets which are unfortunately not always in the same row numbers.
I have found this link on loops:
http://www.ozgrid.com/VBA/loop-through.htm
Which looks like it will do the work of digging through each workbook in a certain folder, but do I need to further specify a sheet within the workbook once it's opened in that loop code? Even if there's only one sheet in each source workbook? The sheets are named differently in each workbook, so that may be a problem.
Now the really complicated bit (I think) is the conditional stuff. Each source sheet is three columns (A, B and C) and I want to import the string (some will be numbers, some will be text) from the C column to a single row of the destination sheet, but only if the string in the source's B column matches what I'm looking for. There will actually be a few dozen of these conditional searches based on different strings from the B column, but if someone could just show me how to do it for a single string, I can expand that to all the other searches I need to do.
Let's say the search term is 'Application ID,' how do I look through the source worksheet for that string in column B and if I find it bring the string from column C on the same row into my destination sheet?
View 9 Replies
View Related
Jun 23, 2012
what I am trying to do but I'll give it my best shot with examples.
A1 = date
B1 = drivefolder[A1.xls]sheetname'!cell
View 1 Replies
View Related
Aug 12, 2011
I've scoured the internet for this answer and have not found a formula that works.
I have a list of domain names/URLs in column A of workbook 1:
jhjh.com
hajfh.co.nz
123.123.12.12
I have a list of domain name extensions in workbook #2:
.com
.co.nz
.net
I want to determine if the cells in column A workbook one contain any of the strings in workbook 2 - note that I need to look at the string, not the entire cell in workbook one. If there is a match, true, if not, false for that cell within workbook one.
It's sort of a reverse array lookup with text. It seems that cross workbook and text is what causes the challenge.
View 9 Replies
View Related
May 22, 2007
I have a main workbook that is meant to summarize data from other workbooks
In Row 6 from column H on I have workbook names in each cell
Column G in all workbooks (including the main one) contains our branch #'s for our offices
For each workbook listed in row 6 , I need to open that workbook (I have that setup with the code below---notice there is an AX.xls that is appended to the file names listed in row 6 in order for the names to match what is in the windows directory)
In the newly opened workbook, I need to, for each value in column G, copy the value from adjacent cell in column H (the dollar value) then search column G of the main workbook for a matching branch and paste the value to the appropriate row under the workbook name column (remember workbook names are in row 6)
The trouble is, for each branch in column G in the newly opened workbook that cannot be found in the main workbook, I need to paste the new branch # at the bottom row of/in column G and the $ value (H column value from the newly opened workbook) to the corresponding row under the workbook name column
Sub OpenWBs()
Dim Rng As Range
Dim WB As Workbook
Dim MyPath As String
Dim lastCol As Integer
Dim newRange As Range
lastCol = Cells(6, Columns.Count).End(xlToLeft).Column
Set newRange = Range(Cells(6, 1), Cells(6, lastCol))
MyPath = "F:AccountingAPAdvertising AccountsLA TimesAgentExtractorCompletedLIST"
For Each Rng In newRange '
View 9 Replies
View Related
May 17, 2014
I want to find the same value in another workbook
In A2 I have the number 112233 I want to know if this number is found in the other workbook in the entire workbook to search it in all the worksheets . if it is found then true if not falls
View 2 Replies
View Related
Oct 7, 2007
I found the error a little further down the line, actually a few errors conspired together. Sorry to waste time, Moderators please delete this if possible. I have no idea how to improve on the thread title.
Windows("Race Strengths.xls").Activate
Sheets(TLocation).Activate
TRRowCount = Range("J1"). CurrentRegion.Rows.Count
Stop
TheRace = Class & NewTrackClass & MeetDay & AgeRest & HorseRest
Set look = Range(Cells(2, "J"), Cells(TRRowCount, "J")). Find(TheRace)
If Not look Is Nothing Then
h = look.Row
RaceRating = Cells(h, "I").Value
Windows("PastTenStarts.xls").Activate
Cells(m, " AI").Value = RaceRating
Goto 155
End If
when I run the whole code without the STOP comand the corresponding ratings are wrong, when I run it and STop it and then continue with F8 the code produces the right results. Everything else is the SAME the only thing I change is I make the Stop a comment of not to enabel it or disabel it.
View 2 Replies
View Related
Apr 1, 2008
I have a short vb code to delete a list box value, but i now need to add a few lines to also delete the list box value IF it exists in cell J59 on a worksheet.
View 12 Replies
View Related
Jul 28, 2009
I'm running this code to find a text in a workbook but i need to know if this code find nothing .
View 12 Replies
View Related
Feb 9, 2010
Open the workbook called get_status.xls then run the user form. Enter 123456 into the textbox then click the Get Status button. I want to get info about the item from the workbook called object_status.xls which is on a network drive (known location) and is not open. Do I have to open this workbook to look for data in it?
So I have to find the matching text string, then get the value of the adjacent cell. If the text string is found in cell B5, then return the value from C5. Always look in the B column for the matching string. After the info is found, close object_status.xls and write the value to the label in the user form (lblResults) in the get_status.xls workbook. If 123456 does not exist in the object_status.xls workbook, then return "Unknown" to the user form.
View 2 Replies
View Related
Mar 27, 2007
I need a Macro that will find every instance of 0 within a workbook and then replace that value with an average of the values in the two cells above it and the two cells below it?
View 9 Replies
View Related
Mar 14, 2008
It's been a while since I've visited MrExcel, but I've been busy learning C# & ASP.Net & Visual Studio & SQL Server - work doesn't want too much from me :p
Anyway, I'm writing a procedure that plays around with lists in Excel (i.e. the type of list that would show up in CurrentRegion.
They're not connected to any outside data source, or as a pivot table - just columns of data that will have a header row in a different format than the data body.
Is there any way of cycling through each sheet in the workbook and identifying each occurrence of a list?
I've been playing with the code at the bottom of the post, and it finds the boundaries of each list providing (at the moment) that each list starts on row 1. There are other problems with it however.
And to identify the values in the header row? .....
View 9 Replies
View Related
Feb 11, 2007
What I'm looking for is to copy information from certain cells, G9 G11 G13 G16 G19 G21 in book1 into A B C D F and K into book2, like so G9 = A G11 = B G13 = C G16 = D G19 = F G21 = K
I tried running the macro and copying and pasting it in but what i need it to do is to find the next empty row down and enter new data rather than copy over the old.
The second, although really before copying is I'd like to the data in G11 to search collumn B and find if there are any existing entries containing that data and, if so, maybe pop up a window that says "Entry alreadyexists" or even loop it round and enter it in G11 saying the same thing and doesn't copy anything, but if the data doesn't exist in G11 then data in all cells on book1 is copiesd as aforementioned
View 6 Replies
View Related
Feb 11, 2007
I have a number of files which I am opening, manipulating and copying the result to a master workbook
I am attempting to use code like this
Selection.EntireRegion.Copy Destination:= Workbooks(Master). Range("A" & LastRow)
Is it posible to define the value of LastRow without making Master the active workbook?
View 3 Replies
View Related
May 28, 2008
I have a workbook with multiple sheets (one total and one for each pay period, 27 in all) to track time and attendance. From the main worksheet where my totals are, I want to be able to enter a date and then jump to the worksheet that date is found on.
For example, if I want to enter time for June 25, 2008, instead of searching for the right worksheet to find that date, I would like some way to type 6/25/2008 (US date) and enter, and that entry will take me to the worksheet that 6/25/2008 is found on so that I may then key my data. My dates are formatted in US style with the first date hard typed and the rest are =Date +1.
View 9 Replies
View Related
May 23, 2006
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies
View Related
Jul 31, 2014
I have a document (unfortunately I cannot attach it) in Excel 365 that each time I open it I get the usual prompt that it is calling an external link. As much as i'd love to hide the popup I need to find the link to solve to problem but I'm at a loss.
Simply breaking the links doesn't work for some reason, so I've tried updating the source to reflect the current file. No luck there.
I've searched the workbook for the name of the link in question, searching for all formula for name itself as well as variants of "[" and ".xlms".
I don't see anything in the name manager referencing that external file.
I don't recall if I copied from that particular document, I may have as it was a duplicate file. I tried looking over the cells I believe I copied from it but didn't see the reference.
I've installed Kutools and Bill Manville (MS MVP) FindLink Tool. Both of which says there is no external link.
I've looked at the compatibility checker which basically just says, yes there is an external link but give no insight on how to address.
I've tried a couple of macros, some of which crashed excel, some of which simply said that there was an external link but not how to find it. In my frustration I forgot which one that was but I'm about to try that approach again and look for that code.
external links.png
View 5 Replies
View Related
Aug 13, 2014
I'm in a workbook, and I want to look up a name. CTRLF or clicking on the binoculars both bring up the expected dialog box, but when I populate "Find What" and either hit enter, click Find All or Find Next, nothing happens.
The only thing I can think of is that I created a macro to function in one sheet only, then saved this workbook as .xlsm. But that doesn't seem right.
View 1 Replies
View Related
Mar 18, 2008
The table to the right is actually in another workbook, both workbooks will be linked. What I'm trying to achieve is a vlookup formula that will count the items in the left table that have numbers entered into them. (This table will be located within a different workbook).
View 9 Replies
View Related
May 13, 2009
I want is when a user enters a time into a specific cell I want Excel to find that time in another sheet (which is in the same workbook) and give out the details of the cell which is next to it, see attached image file.
View 3 Replies
View Related
May 30, 2014
I am trying to find text in one workbook and paste it into another. I've tried a Vlookup, and now I'm trying a Find, and neither is working.
This is what I have so far...
Sub FindAddress()
Dim GCell As Range
Dim Page$, Txt$, MyPath$, MyWB$, MySheet$
Txt = "N.A.V."
MyPath = "T:01862a7R228 Reports2039067"
[Code] ...........
View 9 Replies
View Related
Jun 24, 2014
Sometimes I will copy a macro to a different workbook and then modify it to do what I want instead of writing it new.
If it has a reference to a different macro, I have to go through each module in the "VBA Project" to fine it and see what it does.
I was wondering is there a way to fine a sub routine in a project without scrolling through each module to fine it? (without going back to the workbook and clicking on the view macro button)
Also is there a way to alphabetize each sub routine in a module so it can be found easier
I've started to group my subs better so I can find them easier, but there are a lot of old ones.
I just learned that I can name each module.
View 5 Replies
View Related
Dec 6, 2008
I've heard that you can use SQL to query another worksheet in your workbook, but can not find anything on syntax or how to get started.
View 9 Replies
View Related
Apr 10, 2009
How could I find the next .xls file in the current folder using VBA?
View 9 Replies
View Related
Mar 2, 2010
I am creating a macro that will open another workbook and take all the contents off a page in it and paste the contents to itself.
The problem is that sometimes those workbooks will only have one page and it will have the data I need, but sometimes there will be two or three pages in the workbook, but again, with only one sheet that has data on it. How can I have the macro find the one page with data on it?
View 9 Replies
View Related
