First cell in range

[mucah!t]

I tried the following for finding the first cell in range, it seems to work but still gives an error. Does any of you have an idea of what I did wrong

 Set FirstCl = Range("a1").Start(xlDown).Offset(1, 0)

 

[VoG]

Try

Set FirstCl = Range("a1").End(xlDown).Offset(1, 0)

 

[mucah!t]

Hello VoG,

I try to get the values from the first row to be shown in the textboxes.
The code below doesn't work. When I change End(xlDown) into Start(xlDown), it works, but gives an error. I guess "Start" is an unknown property.
Can you see where I go wrong?

ListBox1.ListIndex = 1

    Dim FirstCl As Range

    'first data Entry
   Set FirstCl = Range("a1").End(xlDown).Offset(1, 0)    'allow for rows being added deleted above header row

    With Me
        .cmbAmend.Enabled = False
        .cmbDelete.Enabled = False
        .cmbAdd.Enabled = True
        .TextBox1.Value = FirstCl.Value
        .TextBox2.Value = FirstCl.Offset(0, 3).Value
        .TextBox3.Value = FirstCl.Offset(0, 4).Value
        .TextBox4.Value = FirstCl.Offset(0, 5).Value
        .TextBox5.Value = FirstCl.Offset(0, 6).Value
    End With
End Sub

 

[VoG]

Perhaps remove the offset

Set FirstCl = Range("a1").End(xlDown)

 

[mucah!t]

It now gives the values of the last row?

 

[VoG]

Can you post a picture of your data using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php and indicate which value you want to find.

 

[mucah!t]

Here it is:

Excel Workbook
	A	B	C	D	E	F	G	H
1	RAND	NO	HLP	CHPTR	ANSWER	QUESTION	REMARK	DATE
2	0,670239843	258	1	1	C'est trs cher.	It is very expensive.		2-2-2011 18:08
3	0,440037516	127	0	1	a peut commencer maintenant.	It can start now.		2-2-2011 18:08
4	0,871719726	256	2	1	cher ami	dear friend		2-2-2011 18:08
5	0,897647779	232	0	1	Je voudrais le savoir.	I would like to know it.		2-2-2011 18:08
6	0,378716301	76	0	1	Voulez-vous venir un peu plus tard?	Will you come a little later?		2-2-2011 18:08
7	0,06922636	37	0	1	Voulez-vous manger la mme chose?	Do you want to eat the same thing?		2-2-2011 18:08
8	0,297510025	29	0	1	O voulez-vous aller manger?	Where do you want to go eat?		2-2-2011 18:08
9	0,46371057	84	0	1	Voulez-vous voir autre chose?	Do you want to see something else?		2-2-2011 18:08
10	0,652011157	222	0	1	Vous n'allez pas le faire.	You are not going to do it.		2-2-2011 18:08
DATA

Cell Formulas
Range	Formula
A2	=RAND()
A3	=RAND()
A4	=RAND()
A5	=RAND()
A6	=RAND()
A7	=RAND()
A8	=RAND()
A9	=RAND()
A10	=RAND()
C2	=IF(AND(B2>=WERKBLAD!$N$2,B2<=WERKBLAD!$O$2+1),MAX($C$1:C1)+1,0)
C3	=IF(AND(B3>=WERKBLAD!$N$2,B3<=WERKBLAD!$O$2+1),MAX($C$1:C2)+1,0)
C4	=IF(AND(B4>=WERKBLAD!$N$2,B4<=WERKBLAD!$O$2+1),MAX($C$1:C3)+1,0)
C5	=IF(AND(B5>=WERKBLAD!$N$2,B5<=WERKBLAD!$O$2+1),MAX($C$1:C4)+1,0)
C6	=IF(AND(B6>=WERKBLAD!$N$2,B6<=WERKBLAD!$O$2+1),MAX($C$1:C5)+1,0)
C7	=IF(AND(B7>=WERKBLAD!$N$2,B7<=WERKBLAD!$O$2+1),MAX($C$1:C6)+1,0)
C8	=IF(AND(B8>=WERKBLAD!$N$2,B8<=WERKBLAD!$O$2+1),MAX($C$1:C7)+1,0)
C9	=IF(AND(B9>=WERKBLAD!$N$2,B9<=WERKBLAD!$O$2+1),MAX($C$1:C8)+1,0)
C10	=IF(AND(B10>=WERKBLAD!$N$2,B10<=WERKBLAD!$O$2+1),MAX($C$1:C9)+1,0)
G2	=IF(COUNTIF(E$2:E$441,E2)>1,"Duplicated","")
G3	=IF(COUNTIF(E$2:E$441,E3)>1,"Duplicated","")
G4	=IF(COUNTIF(E$2:E$441,E4)>1,"Duplicated","")
G5	=IF(COUNTIF(E$2:E$441,E5)>1,"Duplicated","")
G6	=IF(COUNTIF(E$2:E$441,E6)>1,"Duplicated","")
G7	=IF(COUNTIF(E$2:E$441,E7)>1,"Duplicated","")
G8	=IF(COUNTIF(E$2:E$441,E8)>1,"Duplicated","")
G9	=IF(COUNTIF(E$2:E$441,E9)>1,"Duplicated","")
G10	=IF(COUNTIF(E$2:E$441,E10)>1,"Duplicated","")

 

[VoG]

Try

Set FirstCl = IIf(Range("A2").Value = "", Range("A1").End(xlDown), Range("A2"))

 

[mucah!t]

Thanks VoG! This one works fine