Referencing Excel cells 1

[cjac]

Hi, I seem to be having trouble referencing Excel cells from vba. I'm basically trying to run down one column picking up the content for each row until the column is blank(ie. a one column list of references). However, my code runs through the following test once and then trips out. There are at least 100 populated cells within column A on sheet1 - any ideas as to why my code trips after one pass opf the while loop? Here's what I've got:

Vcol = 1
Vrow = 0
While Not (Sheet1.Cells(Vrow & Vcol).Value = "")
VAcct = ""
VCustomer = ""
search_ran = False
VAcct = Sheet1.Cells(Vrow & Vcol).Value
Ref_Ret_Acct
If gotVCustomer = False Then
Range(Vrow, Vcol + 1) = msgdisp
else: End If
Vrow = Vrow + 1
Wend

 

[PHV]

Try something like this:
Vcol = 1
Vrow = [highlight]1[/highlight]
While Not (Sheet1.Cells(Vrow[highlight],[/highlight]Vcol).Value = "")
VAcct = ""
VCustomer = ""
search_ran = False
VAcct = Sheet1.Cells(Vrow[highlight],[/highlight]Vcol).Value
Ref_Ret_Acct
If gotVCustomer = False Then
Range(Vrow, Vcol + 1) = msgdisp
else: End If
Vrow = Vrow + 1
Wend

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[cjac]

Cheers PHV - that'll do the trick nicely. Don't know why I'd got the ampersand in there!
Have a star.