object variable with block when try select sheet from combobox on userform

[KalilMe]

Hello
I try copying data from userform based on combobox to selected sheet but it gives error when press commandbutton1
object variable or with block in this line

Set myrange = sh.Columns(2).Find(ComboBox1, LookIn:=xlValues, lookat:=xlWhole)

the whole code

Private Sub CommandButton1_Click()
Dim Name As String
Dim sh As Worksheet
Dim myrange As Range
Dim i As Long
For i = 2 To Sheets.Count
Me.ComboBox1.AddItem Sheets(i).Name
If Me.ComboBox1.Value = "" And Me.ComboBox1.ListIndex = -1 Then Exit Sub
If Me.ComboBox1.Value <> "" Then
Set myrange = sh.Columns(2).Find(ComboBox1, LookIn:=xlValues, lookat:=xlWhole)
If Not myrange Is Nothing Then
With myrange
 .Offset(, 1) = TextBox1.Value
.Offset(, 2) = TextBox2.Value
End With
End If
End If
Next
End Sub

Private Sub UserForm_Initialize()
Dim i As Long
For i = 2 To Sheets.Count
Me.ComboBox1.AddItem Sheets(i).Name
Next i
End Sub

any help guys,please?

 

[Micron]

You declare the variable sh as a worksheet object but you didn't SET it to anything before you try to use it.

 

[KalilMe]

Hi,
do you mean like this

Set sh = Worksheets(i)

if it's so this doesn't work "error subscript ou of range "
if it's wrong , what the right,please?

 

[Alex Blakenburg]

Did you put that line immediately before your "Set myrange =" line ?

 

[KalilMe]

@Alex Blakenburg thanks for correct me , but it doesn't copy to the sheet when fill textboxes . the header start from row 4 , should copy start from row5 .

 

[Alex Blakenburg]

I don't have visibility of your worksheet.
If your find it looking for the heading, then myrange is your heading cell & row in Column 2.
With your code below you are going first 1 column to the right and then 2 columns to the right.
If this is means to be 1 row down then 2 rows down just remove the comma ","

With myrange
 .Offset(, 1) = TextBox1.Value
.Offset(, 2) = TextBox2.Value
End With

 

[KalilMe]

code seems to need more arranging .
the structure of data for each sheet like this

test.xlsm
	B	C	D
4	CODE	NAME	PLACE
5	ATT-100
6	ATT-101
7	ATT-102
8	ATT-103
9	ATT-104
10	ATT-105
11	ATT-106
12	ATT-107
13	ATT-108
14	ATT-109
15	ATT-110
16	ATT-111
17	ATT-112
18	ATT-113
sep

should match item in column B for specific sheet is selected from combobox1 and when fill textbox1,2 then should fill next adjacent cells
like this

test.xlsm
	B	C	D
4	CODE	NAME	PLACE
5	ATT-100	kal1	ppl1
6	ATT-101
7	ATT-102
8	ATT-103
9	ATT-104
10	ATT-105
11	ATT-106
12	ATT-107
13	ATT-108
14	ATT-109
15	ATT-110
16	ATT-111
17	ATT-112
18	ATT-113
sep