Find value from userform on worksheet

[Agamemnon]

Hi,

On a userform I have a combobox filled with weeks. Now I want to find the rownumber in a defined range that have the value selected in the combobox.
The problem is I get an error if I use the "select property" to define the range I want to search.
Can someone help me with the code below.

Thx in advance
Aga


Dim Wk As String

Wk = UserForm1.Week.Text
Range("'INPUT'!E2:E100").Select
Selection.Find(What:=Week, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.FindNext(After:=ActiveCell).Activate
i = ActiveCell.Row

 

[Andrew Poulsom]

You need to activate the worksheet to select a range on it:

Dim Wk As String 
Wk = UserForm1.Week.Text 
Worksheets("INPUT").Activate
Activeheet.Range("E2:E100").Select 
Selection.Find(What:=Wk, After:=ActiveCell, LookIn:=xlFormulas, _ 
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False, SearchFormat:=False).Activate 
Selection.FindNext(After:=ActiveCell).Activate 
i = ActiveCell.Row

Or without any unnecessary activating/selecting:

Dim Wk As String 
Wk = UserForm1.Week.Text 
With Worksheets("INPUT").Range("E2:E100") 
   i = .Find(What:=Wk, After:=.Cells(1, 1), LookIn:=xlFormulas, _ 
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False, SearchFormat:=False).Row
   i = .FindNext(After:=.Cells(i, 1)).Row
End With

I don't know why you are using Find and FindNext, but I have left them both in.

 

[Agamemnon]

Thx Andrew.

With you code I don't get any errors anymore, but it doesn't make me do what I wanna do.

I would like to add the code below to a "Next" Button and when clicked on it should give me information that goes together with the row the "Wk" value is found on.

Eg value jan 2005 is found on row 12 than other information on row 12 is returned;
... Next
value jan 2005 is found on row 18 then other information on row 18 is returned;
... Next
repeat this procudere in a continuous loop;


Dim Wk As String
Wk = UserForm1.Week.Text
With Worksheets("INPUT").Range("E2:E100")
i = .Find(What:=Wk, After:=.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
j = .FindNext(After:=.Cells(i, 1)).Row
End With

Set NextWeek = Range("'INPUT'!E2:E100")
'Application.ScreenUpdating = False
With UserForm1
Txt1 = NextWeek(j, 7)
Txt2 = NextWeek(j, 8)
Txt3 = NextWeek(j, 9)
Txt4 = NextWeek(j, 10)
Txt5 = NextWeek(j, 1)
Txt6 = NextWeek(j, 2)
Txt7 = NextWeek(j, 15)
Txt8 = NextWeek(j, 17)
tbpicture = NextWeek(j, 18).value
PromoPicture.Picture = LoadPicture(tbpicture)
End With