Trouble with Range & Loops(Not Looping Through Data)

[BIGTONE559]

I'm having problem with the code going through the loop. it looks like it sets the range. the msgbox says 494,937 but it does not loop through each cell correctly.

It appears that it only enters value for the 494937 row. any thoughts?

Sub AGQtest()



Dim fDate As Date
Dim dDate As Date
Dim MystRange As Range

Sheets("HallDb").Select

Set MystRange = Worksheets("Halldb").Range("A2").End(xlDown).Rows

MsgBox (MystRange)

dDate = 5 - 1 - 11
fDate = 5 - 22 - 11
Sheets("Halldb").Activate

Application.ScreenUpdating = False

For Each cell In MystRange

    If Cells(cell.Row, 3) > dDate And Cells(cell.Row, 3) < fDate Then 'this tests whether or not the cells in the apropriate dates within the given prompt

    Cells(cell.Row, 6).Value = 5

    Else

    Cells(cell.Row, 7).Value = 0
    
    End If

Next cell

End Sub

 

[wigi]

Sub AGQtest()

Dim fDate As Date
Dim dDate As Date
Dim MystRange As Range

With Sheets("HallDb")
    .Select
    Set MystRange = .Range("A2", .Range("A2").End(xlDown))
End With

MsgBox MystRange.Rows.Count

...
End Sub

 

[Rick Rothstein]

This statement...

Set MystRange = Worksheets("Halldb").Range("A2").End(xlDown).Rows

is not doing what you think it is. This part of it...

Worksheets("Halldb").Range("A2").End(xlDown)

finds the single last filled cell at the bottom of your current region. Adding the Rows property simply gives you all the rows in that range which, being a single cell is just that single cell. I would try something like this to set the range of cells you are looking for...

Set MystRange = Worksheets("Halldb").Range("A2:A" & Range("A2").End(xlDown).Row)

 

[BIGTONE559]

Thanks Wigi. . .

being new to vba, the with concept seem relatively easy however, i always get confused with the syntax.


Rick Thanks for the explanation. . . very helpful and in this particular case i think i'm going to go with the method you provided.

again thank you both Appreciate the help.

 

[wigi]

i think i'm going to go with the method you provided.

Fair by me, but I guess you'll also ant to qualify that:

Worksheets("Halldb").

also applies to the last part in the line of code:

Set MystRange = Worksheets("Halldb").Range("A2:A" & Worksheets("Halldb").Range("A2").End(xlDown).Row)

 

[Rick Rothstein]

Fair by me, but I guess you'll also ant to qualify that:

Worksheets("Halldb").

also applies to the last part in the line of code:

Set MystRange = Worksheets("Halldb").Range("A2:A" & Worksheets("Halldb").Range("A2").End(xlDown).Row)

Good catch! Thanks for noticing it.

 

[BIGTONE559]

now in defining these ranges. I've always received errors when trying to reference those ranges using:

Application.Vlookup(Cells(cell.row,1),MystRange,2,False)

Am i calling the range incorrectly? should i have not defined the range in this manner in which you all showed me?

 

[Rick Rothstein]

Your original code did not show VLookUp being used. Based on your first posting, it looks like you might be iterateing the MystRange range one cell at a time doing a VLookUp for each of those cells within that very same range. It would be easier for us to see what might be going wrong if you should us all of your code so we could see the statement in context. A brief description of what you are expecting this code to be doing would also be a help to us.

 

[BIGTONE559]

My Apologies. . . Am i the only who thinks things and is under the impression they wrote the down????


With the code i'm attempting to find data in "Halldb" by seeing if the data in the cell is > ddate and < fdate then i want to see if that value is located in my "BankDB" if the records meet those conditions then i would like for the data to be copied to the "BCresolve" sheet.

Sub AGQtest()



Dim destrow1 As Long
Dim fDate As Date
Dim dDate As Date
Dim MystRange As Range
Dim bRange As Range


destrow1 = 5



Sheets("HallDb").Select


Set MystRange = Worksheets("Halldb").Range("A2:A" & Worksheets("Halldb").Range("A2").End(xlDown).Row)


Set bRange = Worksheets("BankDb").Range("D2:D" & Worksheets("Bankdb").Range("D2").End(xlDown).Row)






dDate = #5/1/2011#
fDate = #5/22/2011#





Application.ScreenUpdating = False

    


For Each cell In MystRange



    If Cells(cell.Row, 3) > dDate And Cells(cell.Row, 3) < fDate Then 'this tests whether or not the cells in the apropriate dates within the given prompt
    
        If Not Application.VLookup(Cells(cell.Row, 1), bRange, 2, False) Then
        
        

        Range(Cells(cell.Row, 1), Cells(cell.Row, 3)).Copy
        Sheets("BCresolve").Cells(destrow1, 1).PasteSpecial Paste:=xlPasteValues
        
        
        destrow1 = destrow1 + 1
        
        

        Else

        End If
    Else
    
    
    Cells(cell.Row, 7).Value = 0
    
    

    End If






Next cell



End Sub

 

[BIGTONE559]

Sub AGQtest()

   
        If Not Application.VLookup(Cells(cell.Row, 1), bRange, 2, False) Then
    

End Sub

[/QUOTE]

this is the only line of code of significance that was added to the original code.