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

[arkusM]

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-comfficeffice" /><o></o>
<o></o>
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></o>
Below is the code as I have it <o></o>
(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></o>
<o> </o>
Thanks in advance.<o></o>
<o> </o>

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

 

[arkusM]

Bump.

 

[Jonmo1]

Perhaps like this

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

 

[arkusM]

Jonmo,

That worked to define the range. Thank you.

With respect to the preceeding 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?

 

[Jonmo1]

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

 

[arkusM]

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.

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?

 

[arkusM]

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

 Set InventoryVol = .Range(.Cells(Inventory, 5), .Cells(NumtoFill, 12))

in a With statement for it to work?

 

[Jonmo1]

Yes...

PS, the end code tag should look like this

[/code]

 

[Jonmo1]

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..

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

 

[arkusM]

I see why it was not working before. Thanks for setting me straight!
This should keep me going for a while!!

Cheers,

Mark