Method Range of Object Worksheet Failed

[hmk]

hi there
when excution get to this level

With ws
    .Range(r & Exp).Value = tot.Value
    .Cells(r & Exp) = tot.Value
 End With

then it gives me Method Range of Object Worksheet Failed
any idea why ?

The whole code is :

Private Sub cmdAdd_Click()
    Dim r As Long, Exp As Long
    Dim ws As Worksheet
    Set ws = Worksheets("sheet2")

r = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'Exp Name
Exp = ThisWorkbook.Sheets(2).Cells.Find(what:=ComboBox1.Value, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns,  _SearchDirection:=xlNext).Column

With ws
    .Range(r & Exp).Value = tot.Value
    .Cells(r & Exp) = tot.Value
 End With

DocNo.Value = ""
ComboBox1.Value = ""
tot.Value = ""

Unload Me

End Sub

 

[Norie]

Is the Find actually finding anything?

Have you tried stepping through the code with F8 to check?

 

[hmk]

yes the Find does find what it should find.

yes i tried with F8 many times

 

[erik.van.geit]

Hi, helmekki,

simply look to the syntax

Range("A1")
Cells(1, 1)

r is a number (row)
Exp is a number (column)
let's assume r = 100 and Exp = 10

Range(10010) doesn't exist
Cells(10010) neigther
you need .Cells(r, Exp)
which is Cells(100, 10)

kind regards,
Erik

 

[Norie]

hesham

Erik's right.

 

[hmk]

erik.van.geit, Norie Thank u very much

I got my mistake throgh your help