I just started venturing into VBA. I'm trying to pull the data from a different workbook that will have the previous month as the sheet's name. I can't get the dynamic part of the code.
[Code] .....
I have a challenge with Vlookups!
I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02
I then have another workbook which has employees in column B and the client code in C3.
The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.
I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.
I am currently trying to use a variable instead of an absolute name for sheets name in VLOOKUP, but it wont work:
Original formula:
=VLOOKUP($D6,ABG!$B$9:$O$12,3,FALSE)
What I would like:
=VLOOKUP($D6,$A$6!$B$9:$O$12,3,FALSE)
with A6 = ABG
Im have a vlookup which is specifying a table array from another tab on my workbook i.e. =VLOOKUP(J21,'Norwich CR'!$B:$AZ,6,FALSE)
I have another lookup which is feeding back the names of each worksheet (in this example its "Norwich CR". The range will be the same always $B:$AZ but i want the formula above to vary depending on whether my other look up feeds back "Norwich CR" or "Ipswich CR". how i can get =VLOOKUP(J21,'Ipswich CR'!$B:$AZ,6,FALSE) instead just by reading from another column of lookup data????
I have an Active sheet which is
MySName = ActiveSheet.Name
Here MySName gives the active sheet name.
I require 1 more variable to take this MySName and assign it to a workbook name and make it is as .xls as i required this workbook name to be used later to close the workbook
For ex:
MySName = ActiveSheet.Name
this gives MySName =aaaa
Now i want like MyWBook = aaaa.xls
But aaaa i want it from variable as we cannot hardcode it.
I've created a chart in sheet 1 in a workbook. I want to copy and paste that chart into another sheet (lets take sheet 2) of the same workbook. I am using Excel 2010 version.
Whenever, I try to copy a graph and want to do "Paste Special as Link picture". The problem I am facing as "Paste Link" option is inactive.
I am attaching the Excel for your reference.
what VBA is required to have a combo box in a userform look up data in a different workbook, then populate that data into the drop down list of that combo box for a user to select.
I have managed to successfully create a vlookup for a combo box in a userform that looks up data in a named range in another worksheet within the same workbook and then populate that data into a field in a worksheet within the same workbook. However, I want to change this so the combo box on a userform (in one workbook) will look up data from a named range in a second workbook without opening the second workbook. At the moment, the code I have that will 'submit' the user selected data from the combo box drop down list works.
The code that has worked so far to vlookup data for the combo box from a worksheet within the same workbook is:
Code:
Private Sub UserForm_Initialize()
Dim pName As Range
Dim ws As WorksheetSet ws = Worksheets("vlookupsheet")
For Each pName In ws.Range("ProjectName")
With Me.cboProjectName
.AddItem pName.Value
End WithNext pName
Me.cboProjectName.SetFocus
End Sub
Do I need specify the file path of the second workbook that will contain the data for the combo box as well as the name of the worksheet and named range in that second workbook?
I'm trying to do a vlookup inside of a FOR Loop where the lookup value is the name of a sheet, which I've made into a variable. When the next count of the FOR Loop changes, so does the sheet name. The formula gives me an error as the lookup value requires the sheet name to be in quotations in order for it to return a value. I've tried every possible combination without any success. Below, I've inserted the code
View 3 Replies View RelatedTrying to set a current region within a vlookup. Never done this before. Data in "test 1" sheet is somewhat dynamic: rows are variable; columns are set to 12 (A:L). Thought CurrentRegion would do the trick. And maybe it still could but referencing it into my vlookup errors out. is my syntax for the vlookup incorrect.
Set test_data = Sheets("test1").Range("A1").CurrentRegion
Sheets("Test2").Range("T2").Formula = "=VLOOKUP($F2,test_data,4,false)"
The two sheets are...
1. Sheet1.xlsx (regular excel sheet as the extension is .xlsx)
2. Consolidated.xlsm (macro enabled sheet as its extension is .xlsm)
1. Copy both the files on your system under a particular folder.
2. Now make 9 replica's of Sheet1.xlsx and name them as Sheet2, Sheet3, Sheet4, .......... Sheet10
3. Open the sheet Consolidated.xlsm and see the button i have added called "Pull Data".
Now write down the code by adding a Module in that excel sheet from the code window.
4. Write a code that will open each of these sheets (Sheet1, Sheet2, Sheet3, Sheet4, .......... so on) one at a time and will copy the data from these sheets to the Consolidated.xlsm sheet.
5. Make sure that the data is appended(and not overwritten) from the multiple sheets (Sheet1, Sheet2, Sheet3). that is once you paste the data from sheet1 to Consolidated.xlsm then the Sheet2 data will be pasted at the end and after that sheet3 data will be pasted and so on..
6. In this way at the end we will have all the data from Sheet1, Sheet2, Sheet3...in the consolidated sheet.
7. The Division column in the Consolidated sheet will have the value of first row in these multiple sheets. So after making the replicas of Sheet1, please change the value in first row (Range A1) to any other value to avoid the confusion.
8. I have highlighted the data for two sheets in yellow and grey color in the consolidated sheet.
Basically have a spreadsheet to track an athletic competition going of for the purposes of a fantasy game (like fantasy football). The scores from each event are being copied and pasted into a data pages and then other pages pull from that for calculations. I'm using rankings (rank.eq equation) on a calculation tab, and then using those rankings on a leader-board tab find placement via the VLookup function. The issue I'm running into is ties, when two people are ranked the same. I've been playing with this:
=IF(ISERR(VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE)),VLOOKUP($J17,Men!$A$1:$G$43,7,FALSE),
VLOOKUP($J16,INDIRECT("Men!A" & LOOKUP(J16,Men!A1:A43)+1):$G$43,7,FALSE))
Where it checks for an error in the Vlookup, if its not an error then it does the VLookup, if it is then if looks up the previous ranking and the VLookup array uses Lookup to find the position of the last rank, increments it by one and starts the new Vlookup there.
Adjust this piece of code:
[Code]....
The lookup is for 00.2014, but this is to fixed. Want to use the same code next year to. So I already defined the variable 'jaar' which the user can choose with a validation. (and next year they set it to 2015).
I thought this code would do it but no luck:
[Code] ....
So what would be the correct way? Been shifting with the " " but its only make more and more mess.
I'm working on a project where I need to populate a column with vlookups taken from different workbooks, I have for the most part got it working. However currently, everytime it populates a cell it prompts the user to select the sheet from the workbook as there are two. The sheet name is always the same so I tried to add it into the code so it would avoid having to repeatedly click okay while it populated the table.
This is the original code where it asks for the user to select the sheet every time.
[Code].....
This is what is produced when it is run and the user selects the worksheet each time (which works perfectly fine):
[Code] ...........
I tried to enter the Full Costs sheet name into the code like so:
[Code] ......
However this produces the following:
[Code] .....
The issue I have found is that the square brackets that are around the filename are generated automatically, they aren’t in the actual filename and I haven’t put them in, so I am struggling to work out how to add the sheet name in where it wont be included within the square brackets, as that is what's breaking the lookup.
Effectively I need to concatenate two columns in Sheet1, and then vlookup each concatenated value from Sheet1 in another concatenated column in Sheet2 and return the value from the next column
Now that is quite straight forward, but I am new to coding, and I was wondering if there was a way of doing it that doesn't involve having the concatenated values written to a column as a formula. Ideally what I would like it to do is put the concatenated value of Sheet1 together as a variable (I think thats what you call them) and then look up that variable in Sheet2 without Sheet2 creating a concatenated column too.
I'm of the understanding that this might be slower than writing the columns, but there is a reason for my madness.
Let me know what you think, or if i'm barking. Any suggested reading would also be handy, I don't like to bug you guys for the full code straight out.
I have a huge list of data where in column A there are customers names. Column B i can see products they are renting and column C-Z are uinits that they are renting on a given date. (lets say c is August 1st, D is August 2nd and so on). What I want is that if i a new tab in A1 enter a given date and A2 a given model, I want in A3 a formula which would provide me with Totals of that product on specified date by all customers.
Customer name
Product
Aug 1
Aug 2
Aug 3
A
Ice
2
6
4
[Code] .......
So lets say If I ente August 2 and as product Blow, I would like to know how many products of Blow are rented on that day. In this case 3 .......
I am trying to do a vlookup that currently looks as follows:
Range("C2").Formula = "=VLOOKUP(A2,SAP!A$2:AA$42,2,FALSE)"
However, the selection of the table_array (SAP!A$2:AA$42) is a set value. Instead I would like to select all the data that is on sheet SAP. The idea is that the data set will not always be A2:AA42, so I need to make this part variable.
Normally I would use
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
to select the entire data set.
I have an Excel File with 2 worksheets.
Sheet1 contains column A as "Product Number", Column B as "Product Family". Rows contain about 20,000 lines of data. A Range has been created to cover both columns, and named "ProdRange"
Sheet2 contains Column A (blank), Column B with "Product Number"
What I am trying to do is start at the top of Sheet 2 "Product Number" (B2 is first cell ref), and use this value to do a Vlookup on Sheet 1, to extract the "Product Family" code on the Offset 1 - and insert it back into Sheet 2 Column A.
I have this below :
For i = 2 To LastRow
Range("A" & i).Value = "=VLookup("B" & i, ProdRange, 2)"
Next i
BUT for some reason it doesn't like the [ "B" & i ] part of the equation.
I write quite a few macros that require a vlookup formula. The table array is often dynamic which means I must change my program. I am trying to find a way to create the formula to accomodate the growth of the table array. I can count the number of rows in the table by the counta function, but then when I try to use that variable in the formula it bombs.
View 7 Replies View RelatedI need to lookup a value in one of 20 lookup tables. Each table has a range name that is stored in a cell. In the formula below, U79 contains the number I want to lookup and cell P79 has the variable range name. Both formulas give me an N/A error. When I enter the actual range name in the formula, it works. how to use a variable range name?
=INDIRECT(VLOOKUP(U79,P79,2,FALSE))
=VLOOKUP(U79,P79,2,FALSE)
I'm trying to create a macro that will open automatically generated report outputs and manipulate the data.
The macro will run from it's own workbook (Report Macro.xls), sheets / cells within this workbook are available as 'helpers'.
So far what I have working ok is;
Via a browse dialogs in a userform in Report Macro.xls I select 5 different report outputs. I'm storing the full filename & path in Sheet Data, Cells B1:B6 (B1 being the filename & path to the macro file). In column C I have just the filename
The first file is opened and manipulated as required. The next thing I need to do with it is to open the second file (filename / path in B3) and put a Vlookup in cell 02 of the first file which will look for data in the second (the second file will only ever have a single sheet).
As a formula I'd be using
Code:
=VLOOKUP(A2,'Email 22FEB12.XLS'!$B$7:$C$13120,2,FALSE)
In VBA if I use;
Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],'Email 22FEB12.XLS'!R7C2:R13120C3,2,FALSE)"
It works great. However, the second filename will change and I need to take it from the macro file, sheet 'Data', cell 'C3'.
How can I use the contents of sheet Data, cell C3 in the VBA code above instead of the filename being hard-coded?
I have a large amount of data Range A1:DA6000 on Sheet2 in the following format:
120200 1885 | 42.43 | 130112 1992 | 49.65
120200 1886 | 45.95 | 130112 1993 | 48.08
120200 1887 | 47.65 | 130112 1994 | 49.77
120200 1888 | 45.97 |
120200 1889 | 48.34 | 130133 1887 | x
120200 1890 | 49.42 | 130133 1888 | 41.6
120200 1891 | 48.85 | 130133 1889 | 45.45[/list]
Cell A1 Sheet1 will be variable and I need to find the same value on Sheet2 in all that data and copy the number in the cell to the right to the clipboard. For example:
If Sheet1 A1 = 120200 1885 I want to find 120200 1885 on Sheet2 and copy 42.43 to the clipboard
Or if it's 130133 1887 I want "x" copied to the clipboard and so on.
I think I want to have a macro "Find" the cell and copy the one next to it, but I don't know the syntax to do that. The macro recorder only takes me part way.
I can't figure out how to make a VLOOKUP formula work, as there's way more than 65,000 lines to make a lookup table out of the data.
Aged Debts & Comments
Comments consists of the following
Customer number, Date, Comment, Date, Comment, Date, Comment, Etc Etc....
Aged Debts is
Customer number, Current Outstanding, 30,60,90,120.. Comments.
I would like to bring back the last comment which would be the furthest right comment in "comments".
Ex. of COMMENTS
A B C D E F G
0001 17/10/08 Called Cust. 20/10/08 Payment Promised
0002 15/10/08 No Answer 17/10/08 No Answer 20/10/08 letter Sent
0005 15/10/08 Payment Promised
Ex of Aged Debt..........
VLOOKUP($L4,'[mail.xls] new'!$A$6:$AB$261,6,FALSE)
Which gives me proper result...but the problem i m facing is i have many columns after L4 like M4, N4, O4 and so on....where i have to get the result.
So for that every time i have to paste the above formula and manually change the col_index_num i.e. 6 then 7,8,9 in every column where i use the above vlookup. So is there anyway that it will append the col_num_index by 1....i.e.
=VLOOKUP($B$54,'R:OperationsPerformance ReportingSales ReportsFTW North - INT2006Daily7 July7-02-2006[Sales TL Base Report.xls]MAXimize Summary'!$A:$BA,E$3,FALSE)
I would like to make date in the table array (07 July7-02-2006) a variable, but keep getting an #NA with my limited experience. From what I can gather the single quotes are causing the problem?
I am trying to create a macro so that when i save an entry the form sheet looks in the tavela master sheet to see if there is a match on the record number (in the A column) and saves there as an update if it matches and then if there is no match, saves it in the last empty row. this is a column to row copy so it has to be paste special to transpose. also i have a password on the sheet which i use to unprotect then reprotect. i have successfully done these two things separately but cannot combine them! i know not very much about visual basic code just what i have read here and so i am trying to make this code work.
Code:....
I used VLookup function in macro. Lookup value is a variable name. I don't know how to use variable name inside of VLOOKUP function.
Use VLOOKUP function in macro with Variable Name for Lookup value
Function CC(CName As String)
ActiveCell.FormulaR1C1 = "=VLOOKUP(cname,'[Structure File.xlsx]Sheet1'!C1:C3,2,FALSE)"
End Function
How to use CName variable inside of Vlookup function.
I'm working on a project in Excel (for a restauraunt) and I basically need to make a formula to work out what an item is, using it's ID number and referencing the certain menu that it is in.
as you can see the table array is defined in cell E3, but i still get a #N/A result, and when I replace the "E3" in the formula with "Deserts" it produces a result.
i am looking to create a vlookup but with the ability to easily change the column number index so that i can use different columns.
As an example. In a worksheet i have a table with the names of cars in column A starting row 3. Column B to m Row 1 is headed Jan to dec, row 2 same columns is a country name eg UK. Column N to Y row 1 is Jan to Dec again and row 2 of these columns is a diff country say Germany. This repeats for a few more countries. The data within Row 4 for these columns i.e per car is all prices. The table therefore shows the prices of cars per country per month.
I then have a seperate worksheet for each country where the cars are again listed in column A and Jan to Dec is in column b to M but the data is hard coded being the number of cars. I would like to use column N to link to 1 of these months hard coded counts dependant on what month i decide to forecast on. The easy way being that if i wanted to use Jan count number i would link the count for that car type to =b4 etc. Is there an easy way to allow me to change the link should i decide i want Feb ?
The second question is within each countries worksheet i want to bring into column p the countries related car price for a month i select. It may be that the count number differs from the price i select.
I have a spreadsheet where there are multiple records for each person. Each subsequent record contains information about one year prior to the one above it. So the first records contains info for 2005, the next 2004 and so on. I have the following formula in a cell in sheet 1: VLOOKUP($A2,'Sheet1 (2)'!$A$1:'Sheet1 (2)'!$DD$8796,11,FALSE)
I want to copy the VLOOKUP formula to the cell next to my original cell and have it begin searching on the first row after the first record and continue for each cell to the right. For example, if the lookup in my first cell (say A1) find the result on row 20, I want the lookup formula in B1 to start with row 21. So the change on the formula above would have to change the $A$1 above to $a$row below the first row found. I have a formula which tells me about the first instance of the vlookup finding the first row with the unique identifier.
I am pasting vlookup formulas into a spreadsheet using a macro, and want to change the reference column number based on a variable generated within the code eg
= vlookup(RC1,table,i,false) where i is a predetermined variable in the code
for i=23, I need the result to be of the form
=vlookup(a1,table,23,false)