VB Define Dynamic Ranges, use the range for find or Vlookup

arkusM

Well-known Member
Joined
Apr 12, 2007
Messages
560
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.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
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!!)
<o:p></o:p>
Below is the code as I have it <o:p></o:p>
(I do realize there may be/are more efficient ways to do this and any comments are much appreciated, but not necessarily solicited for the purposes of this question as I realize people are helping out of the goodness of their heart.)<o:p></o:p>
<o:p> </o:p>
Thanks in advance.<o:p></o:p>
<o:p> </o:p>

Rich (BB code):
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
    ' To get the inventory sales number
    Dim InventoryVol As Range
    'Dim Inventory As String
    Dim Inventory As Integer
On Error GoTo Handler
        ' Grabs the first line
    Inventory = Range("A65536").End(xlUp).Offset(1, 4).Row()
        'Puts the Date from the GSO into the first cell
    DateA = Mid(Range("a65536").End(xlUp).Offset(6, 4), 14, 10)
    Range("A65536").End(xlUp).Offset(1, 0).Value = DateA
        ' Add Error Checking for Blank Line to modify where the date appears
    
        'Copies the cell down
    End_of = Range("E65536").End(xlUp).Row()
    Beg_of = Range("A65536").End(xlUp).Row()
    NumtoFill = End_of - Beg_of
        ''MsgBox NumtoFill
    DateB = Range("A65536").End(xlUp).Activate
   
    For Cnt = 0 To NumtoFill
        ActiveCell.Offset(Cnt, 0).Value = DateA
        ActiveCell.Offset(Cnt, 1).Value = Month(DateA)
    Next Cnt

    'Get the total Inventory sales for the day
    MsgBox Inventory
    MsgBox NumtoFill
    
    On Error GoTo Handler2
    'Define range This is where I am Stuck
    Set InventoryVol = Range(Cells(Inventory, 5), Cells(NumtoFill, 12))
    MsgBox InventoryVol
    
    Dim varFind As Variant
    
    On Error GoTo Handler3
    
    With Worksheets("GSO DATA").Range("InventoryVol")
        Set varFind = .Cells.Find(What:="Total Inventory Sales:", LookIn:=xlValues)
        If Not varFind Is Nothing Then Range("A1").Value = .Cells(varFind.Row, varFind.Column + 1).Value
    End With
    
    'Application.WorksheetFunction.VLookup("Total Inventory sales",InventoryVol, 6,0)
    
    Exit Sub
Handler:
        MsgBox "No New Data to Assign Date to"
        Exit Sub
Handler2:
        MsgBox "Range Failure"
        Exit Sub
Handler3:
        MsgBox "Find Failure"
        Exit Sub
End Sub
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Perhaps like this

Rich (BB code):
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
    ' To get the inventory sales number
    Dim InventoryVol As Range
    'Dim Inventory As String
    Dim Inventory As Integer
On Error GoTo Handler
        ' Grabs the first line
    Inventory = Range("A65536").End(xlUp).Offset(1, 4).Row()
        'Puts the Date from the GSO into the first cell
    DateA = Mid(Range("a65536").End(xlUp).Offset(6, 4), 14, 10)
    Range("A65536").End(xlUp).Offset(1, 0).Value = DateA
        ' Add Error Checking for Blank Line to modify where the date appears
 
        'Copies the cell down
    End_of = Range("E65536").End(xlUp).Row()
    Beg_of = Range("A65536").End(xlUp).Row()
    NumtoFill = End_of - Beg_of
        ''MsgBox NumtoFill
    DateB = Range("A65536").End(xlUp).Activate
 
    For Cnt = 0 To NumtoFill
        ActiveCell.Offset(Cnt, 0).Value = DateA
        ActiveCell.Offset(Cnt, 1).Value = Month(DateA)
    Next Cnt
 
    'Get the total Inventory sales for the day
    MsgBox Inventory
    MsgBox NumtoFill
 
    On Error GoTo Handler2
 
    'Define range This is where I am Stuck
    With Sheets("GSO DATA")
        Set InventoryVol = .Range(.Cells(Inventory, 5), .Cells(NumtoFill, 12))
        MsgBox InventoryVol.Address
    End With
 
    Dim varFind As Variant
 
    On Error GoTo Handler3
 
    With InventoryVol
        Set varFind = .Find(What:="Total Inventory Sales:", LookIn:=xlValues)
        If Not varFind Is Nothing Then Range("A1").Value = .Cells(varFind.Row, varFind.Column + 1).Value
    End With
 
    'Application.WorksheetFunction.VLookup("Total Inventory sales",InventoryVol, 6,0)
 
    Exit Sub
Handler:
        MsgBox "No New Data to Assign Date to"
        Exit Sub
Handler2:
        MsgBox "Range Failure"
        Exit Sub
Handler3:
        MsgBox "Find Failure"
        Exit Sub
End Sub
 
Upvote 0
Jonmo,

That worked to define the range. Thank you.

With respect to the preceeding code
Code:
With InventoryVol
Set varFind = .Find(What:="Total Inventory Sales:", LookIn:=xlValues)
If Not varFind Is Nothing Then Range("A1").Value = .Cells(varFind.Row, varFind.Column + 1).Value
End With
[code/]
 
should this part of the code not look for "Total Inventory Sales:" in the range defined and put the value in A1?
 
Upvote 0
should this part of teh code not look for "Total Inventory Sales:" in the range defined and put the value in A1?

Sorry, I don't understand the question. but I just saw that this line

If Not varFind Is Nothing Then Range("A1").Value = .Cells(varFind.Row, varFind.Column + 1).Value

Can be changed to

If Not varFind Is Nothing Then Range("A1").Value = varFind.Offset(0,1).Value
 
Upvote 0
Sorry, I don't understand the question. but I just saw that this line

If Not varFind Is Nothing Then Range("A1").Value = .Cells(varFind.Row, varFind.Column + 1).Value

Can be changed to

If Not varFind Is Nothing Then Range("A1").Value = varFind.Offset(0,1).Value


Sorry for the lack of clarity.
Code:
If Not varFind Is Nothing Then Range("A1").Value = [COLOR=royalblue]varFind.Offset(0,1).[/COLOR]Value[code/]
Should this but the value from the offset in A1?
 
Upvote 0
Never mind it does what I was expecting. THere was just a few blank cells around it that made it look like nothing was happening, so I populated them and discovered the answer! Thanks for you code tip.

Why did I have to encapsulate
Code:
 Set InventoryVol = .Range(.Cells(Inventory, 5), .Cells(NumtoFill, 12))
in a With statement for it to work?
 
Upvote 0
Why did I have to encapsulate
Set InventoryVol = .Range(.Cells(Inventory, 5), .Cells(NumtoFill, 12))
in a With statement for it to work?

You don't HAVE to, it just makes it easier to write and read..Here's how it would look without using With..
Code:
Set InventoryVol = Sheets("GSO DATA").Range(Sheets("GSO DATA").Cells(Inventory, 5), Sheets("GSO DATA").Cells(NumtoFill, 12))

You see what With does? Very handy tool
 
Upvote 0
I see why it was not working before. Thanks for setting me straight!
This should keep me going for a while!!

Cheers,

Mark
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top