Double Lookup (lookup Variable Row And A Variable Column)
I have a file that I would like to lookup variable row and a variable column. I have tired vlookup and hlookup but these do not work because you have to specify a given column or row versus having that column or row be variable. Is there a way to do this.
Think of a set of times tables. I would like to input 8 and 9 and get 72 as an output. How could I go about doing this?
Or in the attached file, I would like to say A and 15 and get A15.
View Complete Thread with Replies
Related Forum Messages:
Index Match Lookup, Variable Return Column
I would imagine that the title of this thread made little sense, so let me try to explain:
Sheet A has columns A-C populated with data. Column D needs to search Sheet B columns A-C for a match. (ie, see if the data on sheet A is also on sheet B) if this is true, then it needs to return the value of the LAST column of the matched row in Sheet B.
So, if Sheet A R1 A-C is 1,2,3. Sheet A R1 D needs to search Sheet B for 1,2,3. and when it finds it, (for example on row 9) return the value of the last column of Sheet B row 9. The issue is, the last value could be in column R or S or AA, there is just no way of knowing.
ODBC Lookup To Populate Variable
I have no problem setting ODBC connections in vba, that return the results to a selected Range destination, but short of then setting txtVariable = Range("A1") is there any way to directly tell the ODBC connection to set the result of the query to txtVariable?
Lookup Function For Variable Amounts
I have a worksheet where I only want to pull over details for rows that have a amount listed in the variance column. Is there some type of macro I can create to perfrom this for me? There will be many rows that I do not need to pull, but for the ones that have an amount listed in variance, I will want to pull over certain cells from those rows.
Is there any way excel can do this for me?
Lookup Based On Variable/Changing Sheet Name
I'm trying to figure out a way to lookup a value based on a conditional sheet name. I'm finding it difficult to explain in words what I'm trying to do, so I'm going to try and describe it in a miniature example of my spreadsheet. I apologize if this is going against forum rules, this seems like the quickest way to get my question across:
The following is the way my spreadsheet's first page is set up:
ID Apr-08 Mar-08 Feb-08
The column labels depend on another table, which changes monthly.
The rest of the sheets (24 of them, labled "200804", "200803", etc) look like this:
ID $ amount Date paid
I have another table upon which the na
I'm trying to return the "Date Paid" value for each ID for each month. Each month, however, more data arrives, and the labels change. I have several dozen spreadsheets formatted in this way, and I'm trying to avoid having to change the vlookup (or other formula) manually each month for each sheet. Is there a way to make the vlookup depend on a table?
This is essentially what my formula looks like right now for cell A2:
=VLOOKUP($A2,'200804'!$A$1:$G$10000, 3, FALSE)
If I could replace '200804' with a cell reference, that would be ideal. I haven't been able to figure out how (if possible); I tried experimenting with index sheets but that went nowhere.
Set Range Variable Using Variable Row Number
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
Double Lookup For Name/Value
I have a list like this:
xxxxxxxxxx1-jan | 2-Jan | 3-Jan
Bob Smith ----B2--- C2 ---- D2
Frank Thomas-B3---- C3---- D3
Bill Clinton----ETC ALL
And another sheet, a similar list, (just names and randomly sorted) but populated with the values I want,
xxxxxxxxxxx1-jan | 2-Jan | 3-Jan
Bob Smith --- 2 -----5------ 1
Frank Thomas 3----- 1 ------2
Bill Clinton ----4----- 5 ------2
George Clinton 4----- 4 ----- 4
I want to check the name in A2 on the first sheet against all of column A on the second sheet, then find the number value for the correct day for that name.
How can I write a formula that will look up these values for me? I looked at OFFSET but unfortunately I don't have a lot of excel experience (yet)
=INDEX('sqft - PC each bldg'!D2:D1705,MATCH(Sheet5!B3&Sheet5!F2,'sqft - PC each bldg'!A2:A1705&'sqft - PC each bldg'!C2:C1705,0))
my result is N/A
I wonder if it has to do with both lookup ranges being columns?
Variable For Row / Column Selection
I need to create a macro that clears the contents of a range of cells. However, that range of cells is variable.
I know my starting cell (C9) and I know the selection goes over to column Z every time but the number of rows I want to clear after that may change.
Also, whatever that first cleared range is, I would need to skip the row below it then clear the same number of rows below that. (not entire row of course, just the ones from column C to column Z)
Pass Row Number Of Last Used Column Row To Variable
This line in my code is causing an invalid qualifer error message:
lngNew = wsNew. Range("B65536").End(xlUp).Row.Offset(1, 0)
Dim wbBook As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim rngNew As Range
Dim rngUnit As Range
Dim rngUnitPaste As Range
Dim lngNew As Long
Dim Cnt As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Set wbBook = ThisWorkbook
Cnt = 0
'Dump To New File
'/Define new workbook
Set wbNew = Application.Workbooks.Add.....................
Double Lookup Values
I am trying to lookup the value that corresponds to three sets of data. The formula I have been using is but I am still getting #NA. I’ve attached the spreadsheet
I am looking to search Vendor name 1, Actual and A to return the value of 1.
Lookup Value In Row And Column
I am trying to create a formula that will look up the data in Sheet 2, A3 and if found in Sheet 1 to then look up Sheet 2, C1 in Sheet 1 and then enter the data if found into C3. Is there a way to do this?
I have attached a sample workbook.
Double Lookup From Two Dimensional Table
I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table.
For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". I have attached what I have so far with further notes and cells highlighted.
Lookup Row In Column & Heading In Row
I'm trying to enter info in a cell based on what is entered in two other cells.
So I have a dropdown box for the species on another sheet as well as a dropdown box for rail sizes. Then I have to crossreference them to come up with an upcharge number. Sorry for my basic lingo. I hope it's understandable.
Use Variable Row/Column Number In Formula Via VBA
I want to create a custom updating formula that finds the last data element on a different sheet and then updates it's own formula. I have to do this because I want to find all of the blank cells and count them up (for missing data purposes). How can I go about putting the last row number in the formula?
This below is the best I can get but it 1004 errors out, I'm assuming because of my "Row" variable. Should I try a concatenate and custom build the formula each time within the macro?
Row = Selection.Row
Sheets(" Chart Data").Select
Range("B2").FormulaR1C1 = "=COUNTIF(CleanData!R[-1]C:R[row]C,"""")"
Lookup Using Row And Column Names
I have two workbooks used for periodically updating product pricing to wholesale customers, one of which has all the product computation info and the other all the customers and products they buy. There is one main sheet in the product information book that has all the products going down the first column with a code for each item. The headings of many of its columns represent variations in packaging. The combination of product and package variations result in the customer's unique price.
I am seeking to find the intersection of product and packaging using names rather than cell numbers. Right now I am able to use VLOOKUP with the product name and named range from lookup sheet but I seem forced to use the column number rather than column heading name. As there are a lot of columns it's easy to lose track or make mistakes. Column name would be much preferred. We are still using Excel 2000.
Example of current reference:
I would like to use something other than 24 for the lookup column, like 'packagename' instead. I have reviewed the INDEX and MATCH functions but haven't figured out how to accomplish what I am after. Incidentally, 'mainlist' is a named range in a separate workbook but referencing as shown above has worked without a hitch.
Lookup Value Then Return Value In Row 1 Of That Column
I have a list of values in column A (part numbers)
In columns B to E I have lists of similar values (part numbers, with row 1 representing their location e.g warehouse, office, etc).
In column F2 I would like to search for the value in A2 in the range B2:E100 and return the value in B1:E1 if the value appears... if it doesnt appear in any of the columns I would like 'not located' to appear in F2.
I've been trying to do this for a while but am making no progress at all.
Lookup Value Using Row Match From Another Column
i'm trying to figure out how i could get the sumproducts or anyway to get this forumla work basically i'm trying to have the cost column to vary depending on the variable set on column d & e on the top basically ...
* if d7 is between d2 and e2 use cost 1 for the multiplication to generate H1.
* if d7 is between d3 and e3 use cost 2 for the multiplication to generate H2.
Lookup On Column And Row Header
I need a formula to lookup and retrieve data from a table of values. Given an EMP-ID in column A of the row that contains the formula and given a column name in row 1 of the column that contains the formula, I need to do a lookup. So, in effect, I am doing a lookup based on two values .. the column header and the row header.
I have attached a small model to illustrate what I need. Cell C-3 in the first worksheet will contain the formula to lookup and retrieve the value in cell C-7 in the second worksheet, based on the values in cell C-1 (column header = “Database Col 2”) and in cell A-3 (row header = “257”) in the first worksheet.
Macro: Concatenate Text, Variable & Double Quotes
I need to write a line of code which puts the following into a cell:
=+C4 & " - " & 1
The cell reference at the beginning will always be C4, however the "1" at the end will be variable named "GroupNo"
So my code will be something like:
Range("B46").Value = "=+$C$4 & " & "" - "" & "GroupNo"
This isn't working. I've tried every variation of what it should be but I keep getting errors like "type mismatch" or just the wrong thing being put in the cell.
Double Lookup And Pull Adjacent Cell
I need is spread across various rows. In a separate sheet I want to be able to lookup two identifiers and pull the number needed in the adjacent row.
The first identifier is a 3 letter character, the second is CURREVO and I need the number to the right of CURREVO. My problem is CURREVO is not always in the same column, but in relation to the 3 letter character is always in the same row.
A B C D E F GDMGCURREVO52011.25YTDREVO243085.00DTYCURREVO11892.50YTDREVO59783.50ECUCURREVI1943.00YTDREVI 5,541.25CURFRQI4.00EEGCURREVO32864.75YTDREVO205426.75EICCURREVO658761.26YTDREVO3507022.68EILCURREVO335741.70YTDREVO1720830.72ENTCURREVI161242.39YTDREVI638681.84CVLCURREVO796266.21YTDREVO4816890.98ENDCURREVI34479.19YTDREVI44074.54EYECURREVI11880.12YTDREVI 108,007.02(Null)MICCURREVO1098694.15YTDREVO5766072.54NEUCURREVI25251.90YTDREVI 158,236.60CURREVO207.00
in a seperate sheet, I want to pull NEU, in that row I want to find CURREVO and bring back the adjacent number = 207.00
Set Variable To Row & Active Cell Column
I have a sort procedure I have been working on. Sort By Active Cell Column
Now I would like to make sure the row of the activecell.column is row 7. I tried
Private Sub comp_myMonthlyReport_SortAscend()
Dim rng As Range
rng = .ActiveCell(7, .ActiveCell.Column)
Selection.Sort Key1:=rng, _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
But I receive this error: Run-time error '91': Object variable or With block variable not set
Lookup Value In Column & Lookup Value For Offset Column
I am trying to find a formula that would pick out a row from a matrix based on the values in the first column and then return to me each of the subsequent values from that row. I can't do a vlookup because there are a lot of blank cells and which columns have data changes. I've attached an example. The text in A1 will change and cells A3:B5 will show the corresponding values from that row along with the column title. I would prefer a formula to type in and leave in cells A3:B5 but if that can't be done VBA is okay too.
Paste A Value Within A Row Based On Column Lookup Containing Dates?
I'm looking to create a small section of VBA code on Excel 2007 that looks for a specific date on a column and then pastes a value on the correct row.
I have weekly dates in columns along the top of my spreadsheet on row 3:
C3 = 07/11/08
D3 = 14/11/08
E3 = 21/11/08
The row further down the spreadsheet that I wish to use is selected by previous code and could be any row number within column B.
I need a snippet of code that searches for a particular date along row 3, and then once it finds the correct column, it pastes the value in the corresponding cell on my active row.
For example, if my active cell/row happens to be on B18, and the date I'm looking for happens to be on L3, I need to paste a value into cell L18.
I imagine there needs to be some form of HLookup, but I've no idea how to implement this in VBA code.
Lookup Column Header Value When First Cell Is Populated In A Row
i have a manpower chart with months along the top and names down the far left. People are going to start work in different months so i want to know at the side of each persons name the month they start. Does that make sense? Heres a simple example
I have column headers starting at B1 thru F1, with the following values Month1,Month2,Month3,Month4,Month5
In cell D2 i have the value 1
I want Cell A2 to return the value Month3
Whats the formula i need to input in A2
How To Lookup Row # Based On Content Of Another Column's Cell
I have a 3 column table with 20 rows. The first two columns contain travel
expense data. The cells in the third column are blank exept for whichever
cell I enter an "x" in. Please let me what formula or worksheet function do
I use to calculate the travel expenses based on the data in the first two
columns that correspond to the row of the cell with an "x" in the third
Lookup Row & Column Heading To Return Corresponding Header
I have a list of names and the chores they need to do on a certain day. I need a formula that returns the chore the person needs to do when the date is filled in.
Ex: Sheet2 Col:A has names filled in A2:11 , row B1:J1 has dates that you would input.
formula goes in B2:J11
Sheet1 is the master with all the data the formula would be pulling from.
column A2:A11 has the names , row B1:S1 has the chore that needs to be done.
B2:S11 has the dates already filled in.
Lookup Row And Column As Criteria But Data Is Spread Across Columns
I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).
Double Lookup Function: Find Total Invoices Billed In February For ABC Consulting Company
I have a spreadsheet in which I am trying to track invoices billed according to month in question. For example, I need to find total invoices billed in February for ABC Consulting Company. I have a database in the same spreadsheet that contains all invoices billed for an entire year for all companies. How do I pull invoices for a particular month only, in this case for the month of February? I have attached an example of spreadsheet in question. Included is a tab which indicates desired results.
Macro Error Message (object Variable Or With Block Variable Not Set)
When i try to run the code below i get the error message - object variable or with block variable not set-
Selection.AutoFilter Field:=1, Criteria1:="1"
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Find Dates Macro: Object Variable With Block Variable Not Set
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
Run Time Error 91 Object Variable Or With Block Variable Not Set
I am having a lot of trouble finding out why I am getting error. I believe the error is because it can't find the number. In cells C115:C314 i have the numbers 1 to 200 in order. when someone types in 1 to 9 in the text box it works, but on 10 and over i get the error ???? here is the code I have
Selection.Find(what:=CInt(txtPackageID), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
x = ActiveCell.Row
shCalculator.Range("ProposedMeter").Value = Cells(x, 7).Value
shCalculator.Range("Package").Value = Cells(x, 12).Value
shCalculator.Range("ProposedMeterAmount").Value = Cells(x, 30).Value
shCalculator.Range("Term").Value = Cells(x, 62).Value
shCalculator.Range("Discount").Value = Cells(x, 67).Value
shCalculator.Range("Equipment").Value = Cells(x, 72).Value
Find Method Code: Object Variable Or With Block Variable Not Set
I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow
Here is my
Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long
'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
'for anyof the two lines down I get the message "object variable OR block variable not set"
lRow = rFoundCell.Row
lCol = rFoundCell.Column
Run Time Error 91, Object Variable Or With Block Variable Not Set
Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range
Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False
Set c = .Find( _
MatchCase:=MatchCase, _ ........................
Using the message box I see that sheet 1 opens but then I receive an error message Run Time Error 91, Object Variable or With Block Variable not set. I tried declaring and using set on "project" but got nowhere. I also need to have a message indicating project not found. Once this part is solved I will loop all of my other workbooks
2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
Runtime Error 91, Object Variable Or With Block Variable Not Set
This works fine in Excel 07 but when ran in 03 it doesn't work and I get that error message.
Debugging Error: Object Variable Blocked Variable Not Set
I am getting an error at this line:
Set rng5 = Range(.Cells(celle2.Row, celle4.Column), .Cells(celle2.Row, celle4.Column))
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim celle1 As Range
Dim celle2 As Range
Dim celle3 As Range
Dim celle4 As Range
Dim celle5 As Range
Dim flag1 As Long
Dim flag2 As Long................
Object Variable Or With Block Variable Not Set Error With Macro
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.
Find Method: Object Variable Or With Block Variable Not Set
I have a spreadsheet form with all kind of values and what I want to do is to find a data in a worksheet named "Config" based on the spreadsheet activecell value. With the find row adress I return data of another column but same row (in the worksheet) in two diferent label captions. If the spreadsheet activecell value it was found in the worksheet everything is ok but if the value is not found I'm getting the message "Object variable or with block variable not set"....
Error: Object Variable Or With Block Variable Not Set
I've set up a userform (frmCoC) to read and write info to the "contractor database". Within that userform is a listview control that reads off a second, "induction database".
When the user selects and clicks on one of the displayed entry in listview, another form (frmInd) opens to edit data on the induction database.
There's a command button along side the original form (frmCoC) to create a new entry in the induction database. The click event for the button is as follows:
Error 91 Object Variable Or With Block Variable Not Set
At a high-level: My code fails in the sub-routine that is called (SecondSub). The first line of code on the SecondSub is where I get the Error 91 Object Variable or with block variable not set.
Dim ie As Object, iebody As String, strURL As String, strUsername As String, strPassword As String
Dim lRow As Long
Dim abc As String
Dim striEst As String
Dim ele As Object
Dim LinkHref As String
Dim a As String
Dim b As String
Dim c As String
a = DateAdd("d", -1, Now)
b = DateAdd("d", -2, Now)
c = DateAdd("d", -3, Now)
Set ie = CreateObject("InternetExplorer.Application")
strURL = "website"
strUsername = "xxxxxxxxxxx"
strPassword = "xxxxxxxxxxx"
String Variable To Call Defined Range Variable
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.