PDA

View Full Version : [SOLVED] Use .Range("A1:A10000") to end of records?



GhostofDoom
12-08-2019, 12:59 AM
Hello,

how can we use to get the range endof records of the cells without to use this last part of code below




.Range("S8:S100000".Find(What:=Me.ListBox1.Column(17), LookIn:=xlValues, LookAt:=xlWhole))



is there an solution to use it so we don't have to add :S100000 of rows?

because i believe if there are many more records it will not be able to search them all.

Thanks

mana
12-08-2019, 01:37 AM
Range("S8:S" & Rows.count).Find


or


Columns("S").Find

GhostofDoom
12-08-2019, 01:51 AM
Hello Mana,

i get an error if i use this



Set findvalue2 = Sheet2.Range("S8:S" & Rows.Count).Find(What:=Me.ListBox1.Column(17), LookIn:=xlValues, LookAt:=xlWhole)
MsgBox (findvalue2.Offset(0, 1).Value)


'Run-time error 91
Object variable or with block variable not set'

SamT
12-08-2019, 07:15 AM
Try Range("S:S").Offset(8)...

GhostofDoom
12-08-2019, 09:30 AM
Hi SamT,

didn't work :(

Paul_Hossler
12-08-2019, 09:43 AM
@GhostOfDoom --- what is it EXACTLY are you wanting to accomplish, not how you think you want to do it

GhostofDoom
12-08-2019, 10:18 AM
Hi Paul_Hossler,


Well i like to .find all the rows without to use a default number of rows




Sheet2.Range("S8:S100000")'S100000



like




Set findvalue2 = Sheet2.Range("S8:S").Find(What:=Me.ListBox1.Column(17), LookIn:=xlValues, LookAt:=xlWhole)



so when i search for something and i use the offset it will show me the correct record




MsgBox (findvalue2.Offset(0, 0).Value) ' display the record i looking for from the columns A till AI i use the 0,0 and search the correct cell

Paul_Hossler
12-08-2019, 10:47 AM
Well, .Find will only return the first occurrence.

Have you looked at .Filter?

GhostofDoom
12-08-2019, 10:59 AM
filter?

what do you mean Paul_Hossler with filter

Paul_Hossler
12-08-2019, 05:24 PM
put together and attach a sample workbook with the macro

don't need the userform, just the value from the combobox and in another sheet maybe the results

SamT
12-08-2019, 08:20 PM
.Find did not find anything
Or
Listbox column 17 of that list row is empty (Null)



.Range("S8:S" & Rows.Count is perfect.

ListBox1.Column(17) I have a doubt about

GhostofDoom
12-10-2019, 06:23 AM
Hello SamT,

just to let you know and the others that helpme out
that i have solved it

indeed it worked with the S"Rows



Dim Lastrow As Integer
Lastrow = Sheet2.Cells(Rows.Count, "S").End(xlUp).Row
Set findvalue = Sheet2.Range("S9:S" & Lastrow).Find(What:=Me.ListBox1.Column(17), LookIn:=xlValues, LookAt:=xlWhole)



Thank you all very much :thumb