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]

In that VLookup formula:

- what is cell? If it is a variable, please take another name for the variable, as cell is also internally used in Excel
- why do you use "If Not ..."? Is the result of the VLookup a boolean variable like True or False? Is it a value smaller than or bigger than 0? What are the contents of the 2 column in bRange?
- More important, the VLookup is in range bRange, which consists of 1 column. How are you going to get the results from the 2nd column in this case? Did you test the VLookup in Excel?

 

[Rick Rothstein]

...what is cell? If it is a variable, please take another name for the variable, as cell is also internally used in Excel

In what way is it used internally by Excel? I ask because I have used a variable named Cell (declared as type Range) in past code and never noticed any untoward results from doing so.

 

[wigi]

Apologies, my mistake. I read (thought I read, that is) that Cells in plural was used. I must have mixed up Cell and Cells.

Cell should be fine, indeed. Apologies again.

Though it would be better to use rngCell to indicate it is a Range variable. But that's the choice of the developer.

 

[BIGTONE559]

With the Vloookup formula i would like true or false as a result. I've really been stumped as far as how to use the vlookup within vba because 2 is really not necessary. I just would want to check to see if the value in Cells(cell.row,1) can be found in the range and if the value in the 2nd column of that range matches the 2nd column of data on the active sheet.


Sheet 1 Sheet 2
brange halldb

3 columns of data are on each sheet. Hope that info helps.

 

[BIGTONE559]

Apologies, my mistake. I read (thought I read, that is) that Cells in plural was used. I must have mixed up Cell and Cells.

Cell should be fine, indeed. Apologies again.

Though it would be better to use rngCell to indicate it is a Range variable. But that's the choice of the developer.

my "developmental" ways are primitive so I appreciate any insight that is shared. Never heard of rngCell and i'm not fully aware of the many object properties. Just bought an ebook and i'm going to really start reading up on this stuff.

 

[Rick Rothstein]

Never heard of rngCell and i'm not fully aware of the many object properties.

There is nothing magical going on here... rngCell, like Cell, is just a proposed name for a variable. I like using descriptive text only for my variables (like Cell, ColumnA, GrandTotal, etc.) so that my code tends to read like sentences. There is a school of thought, though, that thinks variables should carry the declared data type as an abbreviation in front of the name... that is what rngCell is suppose to be doing. The abbreviation "rng" is short for Range (the variable's data type as declared in a Dim statement) and the "Cell" part is the descriptive name of the variable. Another example to make this clear... let's say you want to have a variable for the total of some values. I would prefer to simply name it Total where as I'm guessing wigi would prefer dblTotal (where the "dbl" part refers to the variable's data type of Double as declared in a Dim statement).

 

[BIGTONE559]

There is nothing magical going on here... rngCell, like Cell, is just a proposed name for a variable. I like using descriptive text only for my variables (like Cell, ColumnA, GrandTotal, etc.) so that my code tends to read like sentences. There is a school of thought, though, that thinks variables should carry the declared data type as an abbreviation in front of the name... that is what rngCell is suppose to be doing. The abbreviation "rng" is short for Range (the variable's data type as declared in a Dim statement) and the "Cell" part is the descriptive name of the variable. Another example to make this clear... let's say you want to have a variable for the total of some values. I would prefer to simply name it Total where as I'm guessing wigi would prefer dblTotal (where the "dbl" part refers to the variable's data type of Double as declared in a Dim statement).

Golden Rick simply Golden. . . it makes a lotta sense (Your way and the general rule of thumb). I'm learning a lot about these variables and there abilities and limitations. I just always run into syntax issues. This vlookup for instance. . . i cannot for the life of me understand why if i dim the range. . . why can't i use that range within the Application.Vlookup function.

it's like i can only use ranges that are define on that particular sheet in Excel i.e. Range("Checklist") as oppose to just bRange