VB Define Dynamic Ranges, Use The Range For Find Or Vlookup
Jul 9, 2008
I have an worksheet that I import a csv into, each day a new csv is added to the bottom of the previous csv data. I have some code that extracts the date and month # from the cell and places them in helper columns. The code find the last used cell in the helper column and the imported data column to find the first and last row of the new day.
This part works fine. However, I assigned a variable name to the first and last variables and would like to uses these row number to define a range in order to use the range for a vlookup or find operation. This is where I get stuck. I want to use column x and row (variable from first bit of code) to column y and row (variable from first bit of code). Then use a vlookup/find whatever works to find the text I need and get the data. Tips on looping the code would be welcome as well.
I have searched for answers to the problem, on the board and web, but have not found a solution that works for me. (at least that I could get to work!!)
Below is the code as I have it
Public Sub Enter_Date()
Dim DateA As Date
Dim DateB As Date
Dim Cnt As Integer
Dim End_of, Beg_of As Integer
Dim Count As Integer
Dim NumtoFill As Integer
View 9 Replies
ADVERTISEMENT
Nov 3, 2008
I'm trying to define a dynamic column range call 'Cost'
If it starts in Column C, row 4 [C4]. I want the range that to go down to the Row I have defined as 'subtotal'
The user will be able to insert new rows above 'subtotal'
How can I define Column C4 so that any new row added will be including in the defined row range 'Cost'?
View 9 Replies
View Related
Feb 26, 2008
I have been tring to define a dynamice range in VBA. At the moment, I use following method to Define the range,
Sub OTC_Future_Total()
Dim DynamicRange As Range
Worksheets("FinalReport").Select
Range("e9").Select
Set DynamicRange = Range(Selection, Selection.End(xlDown))
DynamicRange.Select...
View 5 Replies
View Related
Dec 22, 2009
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
View 9 Replies
View Related
Oct 28, 2008
I want to create dependent lists using data validation. The lists need to be created from ranges that will be growing as users add more data. I think the best way to create the list is a VLOOKUP. However, I am not sure how to use the VLOOKUP when the range is changing.
I have attached an example. I have a list on sheet "Vlookup" called "FRUIT" with "apple", "orange", "banana". Then to make the depedent lists I have created three other lists called: "APPLE", "ORANGE", "BANANA". I want to pull the COST from sheet "VALUES" into the lists "APPLE", "ORANGE", "BANANA". Users will be adding costs next to the FRUIT they purchase.
View 5 Replies
View Related
Feb 24, 2012
I am trying to create a vlookup to get a count of trouble tickets techs completed daily within a table I created, I am using a table since its dynamic. For example I need to see how many tickets Joe completed in a day. See below...
Tech Ticket# Comments Status
Joe 1234 Replaced HD Closed
John 3212 Replace Motherboard Closed
Joe 5678 Installed OS Closed
Above is just an example (my table has 40 columns). I need to vlookup joe and get the count of the closed tickets.
View 6 Replies
View Related
Feb 26, 2014
is there a simple way to do a vlookup (or any other more appropriate formula) using the value of another cell to define the named range relevant to that lookup.
eg I will have two different ranges for fitness performance levels, each of these will be named "Male" and "Female".
I would like to do a lookup based on a formula as follows: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =vlookup(D4,NAMED RANGE,2,false)Â
the Named range would be a value in the same row ie B4 - being Male or Female
so I am hoping for something like Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =vlookup(D4,B4.Value,2,false)Â
otherwise I could just use an If and do something like: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas).
Many more Excel Formulas  Z100 =if(B4="Male",vlookup(D4,Male,2,false),vlookup(D4,Female,2,false) )Â
View 2 Replies
View Related
Apr 3, 2008
I am trying to merge data from two worksheets onto a 3rd for a mail merge. The COLLECTIONS sheet contains the acct #, name and amount owed. The ADDRESSES sheet contains the acct #, name and all of the address information. The 3rd sheet is the MERGE sheet that I'm using as the reference point for my word document and the mail merge. Upon reflection the MERGE sheet is probably not necessary, but made sense to have it when I started out.
Never the less...
On the ADDRESSES sheet I am trying to name a dynamic range "AddressList" (I'm trying to go dynamic because the list of addresses will change from month to month). I am using the following formula in the refers to field when I name the range:
=Offset(Addresses!$A$5,0,0,CountA(Addresses!$A:$A),CountA(Addresses!$1:$1))
I think this is what is giving me my problem, because the named range does not show up in the list when I try to go to the named range.
Just in case that is not the problem, the #Ref is showing up when on the MERGE sheet I type a vlookup formula referencing the acct # on the ADDRESSES sheet. That formula looks like this:
=VLOOKUP(A4,AddressList,3,0)
which seems simple enough. Before trying to make the named range dynamic the formula worked fine, which is why I'm thinking the named range is what's giving me the problems.
View 9 Replies
View Related
Mar 7, 2009
the dynamic name ranges I had created did not "adjust" in their position references when I added new name ranges later.
My workbook (see attachment) Master sheet has several name ranges that are based on their position to a key name range I set up..."Unit". Example below:
"Unit"
View 4 Replies
View Related
Aug 20, 2014
I'm trying to find a way to perform Vlookups against a dynamic range of data, where the number of rows/values in Col B is always subject to change.
The way that the workbook is structured is as follows:
Sheet1: Except for Cols A & B, this is a blank sheet. This is the sheet in which the vlookup values will need to fall into based on the number found in its' Column B
Sheet2: The sheet that contains the data that will be passed into Sheet1 via vlookup, and all of its Columns contain data.
So, basically, Sheet1 is a shell that needs to be filled with data copied over from Sheet2 based on vlookups against Col B.
The vlookups will need to be shifted back 1 column.
And examples of how the Vlookups need to work is:The value in Sheet2 [Col E], needs to get passed into Sheet1 [Col D]Along those lines:
The value in Sheet2 [Col F], needs to get passed into Sheet1 [Col E]
The value in Sheet2 [Col G], needs to get passed into Sheet1 [Col F]so on and so forth
So basically, all of the values passed into Sheet1 from Sheet2 need to be shifted back by 1 column until we reach the last Col (Col M in this file).
Where
The value in Sheet2 [Col M], needs to get passed into Sheet1 [Col L]
The number of columns will remain fixed, but again the number rows will vary week to week in both of the two sheets, so I would need to have a way of creating a Vlookup through VBA that accounts for the dynamic range in Col B.
View 4 Replies
View Related
Oct 14, 2011
The workbook I'm working on has several sheets: Totals, Monday, Tuesday, etc set into a SheetArray.
The problem is that I'm trying to perform a VLOOKUP on data in worksheet "Monday" against column A in worksheet "Totals". However, the size of column A will change as the codes iterates through the days. Therefore, I need the range of column A in the Totals worksheet to be dynamic.
Code:
'Find any new projects in the daily data that are not in the Totals.
DayProjCount = Range(Sheets(SheetArray(A)).Range("AA2"), Sheets(SheetArray(A)).Range("AA2").End(xlDown)).Cells.Count
ProjCell = 2
With Worksheets("Totals")
[Code].....
During this process, the code should only find 1 error in ValidProject. But, each are throwing errors. I think it's because the VLOOKUP isn't searching through the correct range, but I don't know how to check it.
View 2 Replies
View Related
Apr 10, 2012
I have a workbook with 100 worksheets. Each worksheet is setup the same way with dates down column A, and data in column B. In another sheet, I need to run a vlookup on the dates and data, but I need it to adjust for whatever spreadsheet name I give it. Is there any way to have the vlookup table array change as I change the worksheet it should reference?
View 2 Replies
View Related
Sep 30, 2013
I have this code which looks information in a table. The problem is that the table is getting every time bigger.
Here the code:
VB:
With Worksheets("Sheet10").Cells(9, 3).Resize(, LastColumnf - 2)
.Formula = "=IFERROR(CONCATENATE(VLOOKUP(Sheet10!C2,Sheet12!$A$10:$C$550,3,FALSE),""."",
VLOOKUP(Sheet10!C2,Sheet12!$A$10:$C$550,2,FALSE)),"""")"
.Value = .Value
End With
How can I write $A$10:LastNewRow... Another problem rises here, LastNewRow might end up being the oldLastNewRow, i think. But maybe since it is getting bigger, there is no problem, I don't know.
Instead of having a static reference like $C$550 I would like to have a dynamic one.
View 3 Replies
View Related
Oct 10, 2005
I'm looking up a value from another workbook. Simple enough in itself, but
heres the thing.
I'd like to write a dynamic vlookup that checks the value of another cell to
find the table array for it to look up from.
Basically I have a table of data, the column headers are dates. I have a
report that is run daily that I need to look up from. Rather than implicitly
referencing each sheet I'd like to tell the vlookup to check the date in the
column header and use that for the sheet. The range inside of the sheets is
always the same.
View 15 Replies
View Related
Aug 27, 2009
I'm trying to create a dynamic Named Range using VLOOKUP in place of a sheet name. (Using Excel 2003 & Win XP Pro SP2).
To illustrate:-
Employees.xls contains employee's details on separate sheets for each department, e.g. Production, Admin, Sales, Personnel, etc. Each sheet is 12 cols. and 1 header row. Sheet 1 of this book contains a 2-column Master List of all employees and their departments. MyBook.xls has an employee's name in A1.
I can get his department by using =VLOOKUP(A1,MasterList,2,FALSE) and what I want to do is incorporate that into the following in place of 'Admin' so that the range will refer to the correct department for whoever's name is in A1.
=OFFSET('[Employees.xls]Admin'!$A$2,0,0,COUNTA('[Employees.xls]Admin'!$A:$A)-1,12).
View 9 Replies
View Related
Jul 12, 2006
I am having problems getting VBA code to work on command buttons. I have recorded a macro and pasted it into a command button - but it always results in an error message when I click the command button. I am trying to copy and paste values from one group of cells to another thus;
Sub CommandButton1_Click()
Sheets(" Graph Data").Select
Range("E45:F47").Select
Application.CutCopyMode = False
Selection.Copy
Range("E46").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
End Sub
View 5 Replies
View Related
Mar 13, 2007
I'm trying to create a user defined function to drop into dblookup functions that will always reference the first cell in an associated column and the first cell in the row of an associated column.
I came up with the following, however the problem is that the udf always returns the values for the active cell's, row and columns.
Function SRCref2()
Application.Volatile
myfield1 = ActiveCell.Column
Dim1 = Columns(myfield1).Range("a1").Value
SRCref2 = Dim1
End Function
Function SRCref3()
Application.Volatile
myfield2 = ActiveCell.Row
Dim2 = Rows(myfield2).Range("a1").Value
SRCref3 = Dim2
End Function
I would nest these in a formula such as "=srcsum(srcref2(), srcref3())".
Ideally I could then use this same funtion anywhere instead of having to use and worry about the accuracy of "=srcsum($a$2,$b$1)"."=srcsum($a$3,$b$1)"."=srcsum($a$4,$b$5)".etc.
It seems like there should be a current cell method or something but I can't find it and I'm not smart enough it seems to use vb to designate some kind of floating intersect as a base for a row and column reference.
View 8 Replies
View Related
Jun 21, 2009
I would like to replace the blue bit of this Dynamic Named Range (DNR) with an INDIRECT formula in order to modify the starting point for the DNR: =OFFSET(DATA!$C$60,0,0,20,1). Unfortunately, I can’t seem to get my INDIRECT formula to work in order to use it to replace the blue bit above: INDIRECT("'"DATA"'!&ADDRESS(MATCH('SHEET1'!AC8,DATA_Date,0)+22,3)"). The orange bit of the formula above returns a value of 60, therefore the ADDRESS formula should return $C$60, that can then feed into the INDIRECT and act as the equivalent of DATA!$C$60. But it doesn’t.
View 2 Replies
View Related
Feb 10, 2010
I need to run a vlookup to find some data. But I have a lot of data about 600,000 lines. Currently this list is spread over several columns (as the limit is something like 50000). How can I make my reference range access multiple columns?
View 5 Replies
View Related
Apr 28, 2014
I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.
However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.
What part of code do I need to change and to what to make it work for just 2 named ranges ?
View 6 Replies
View Related
Jul 7, 2009
I'd like to Find and replace blank cells in columns O and P, with the word "BLANK CELL" but only = to lowest data row in col D and then stop.
View 2 Replies
View Related
Mar 14, 2013
I have 10 worksheets. I would like to create a macro to find all the "2" values on worksheet 1, and have the actual range that the cell is, compiled into a list -example: I would like the list to be similar to this= (A1,B15 ,C8)
I even tried to record it but it doesnt show me the actual range that the data is found in.
View 9 Replies
View Related
Feb 26, 2010
definitions for static ranges and dynamic ranges?
View 9 Replies
View Related
Aug 18, 2009
I have a Vlookup which I want to modify so that it can become dynamic as the table array part of the vlookup will change.
So the basic vlookup is as follows:
=VLOOKUP($R$3,ATTRIBUTION_FACTSET!$M$60:$P$73,2,0)
but the data I am looking for wont always be in the range M60:P73.
So I tried to make it dynamic by doing the following:
=VLOOKUP($R$3,INDIRECT("ATTRIBUTION_FACTSET"&"!M"&U1&":P"&V1),2,FALSE)
The idea being that U1 and V1 would be numbers that can change so in this case U1 would equal 60 and V1 would equal 73
This vlookup is giving me #N/A and no matter how I modify it I cannot get it to work.
View 3 Replies
View Related
Jan 4, 2009
=+IF(G6=0.25, VLOOKUP(F6,Maple,2), IF(G6=0.375,VLOOKUP(F6,Maple,3),"-"))
This function determines where on the "Maple" named range to get the data based on the thickness of the material (.25 or .375 from G6 dropdown). What I'm wondering is if VLOOKUP's 2nd operator (in this case "Maple") can be filled in dynamically based on another drop-down menu. I have 8 different species and I'd like to avoid a lengthy IF/THEN statement. OR, is there a completely different function that I should be looking at here?
View 3 Replies
View Related
Jul 17, 2009
= IF(AND(MATCH($B8, [AXS.xls]Sheet1!$E$1:$E$400,0) - 2 = $C8,
INDEX([AXS.xls]Sheet1!$B$1:$B$400, MATCH(CX$2, INDEX([AXS.xls]Sheet1!$B:$B, $C8 + 16):INDEX([AXS.xls]Sheet1!$B:$B, $C9), 0) ) ),
INDEX([AXS.xls]Sheet1!$A$1:$E$400, MATCH(CX$2, INDEX([AXS.xls]Sheet1!$B:$B, $C8 + 16):INDEX([AXS.xls]Sheet1!$B:$B, $C9), 0), 3), " ")
i have this formula, and i think it will work if i find a way to correct the blue areas. they are trying to define the match array. i tried using the address and cell functions with no luck. i just want the array to return something like B2:B15 (from the AXS sheet) so the match function works. in the current sheet, C8 and C9 have values that refer to rows in the AXS sheet
View 2 Replies
View Related
Jan 23, 2007
I want to define a Range() in VBA. have cell1 and Cell2 in the format of rows and cols. ie. Cell1 = Row 1, Col 2. Cell2 is dynamic, can be sometimes row100, Col200, or Row23, Col 1000. May i know how to define it in VBA?
View 2 Replies
View Related
Jan 21, 2010
I have two columns containg the arrival and departure hours of workers. From these columns I must define the workshift by specifiyng the time range for each shift. AZ contain the arrival hours while BA the departure.
The formula I use doesn't return the results correctly because some shifts are almost the same. i.e: if shift one starts (arrival hours) between 05:00 and ends at 12:59 and shift 1-2 starts at 08:00 and ends at 23:59 then it will go with the first shift even though the times in range belong second shift.
View 8 Replies
View Related
Feb 23, 2013
why this won't work.
Code:
Dim DRange As String
Dim ERange As String
Dim SRange As String
EndRow = Range("A65536").End(xlUp).Row
DRange = Range("D1", "Z" & EndRow)
ERange = Range("E1", "Z" & EndRow)
SRange = DRange
View 6 Replies
View Related
May 19, 2008
how to define the end of a range using a variable determined by a rng.Rows.Count command (or anything else)? Is this even possible? Here is what I currently have:
Set rng = Range("A1:A10000")
For i = rng.Rows.Count To 1 Step -1
If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete
Next
I'd like to define the end of the range with more accuracy than I'm currently doing.
View 9 Replies
View Related
