With block does not work on inactive worksheet?
 

Hi All,

I have these lines inside a function -

Dim ws As Worksheet
Set ws = Sheets(SheetName)
ws.Activate
colCount = ws.UsedRange.Columns.count
With ws.Range(Cells(1, 1), Cells(1, colCount))
Set c = .Find("C", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=True)
'.......
End With

Until ws.Activate was added, it jumped out of a function on With statement.
Weird....

With block does not work on inactive worksheet?
 

You have to qualify all objects

With ws.Range(ws.Cells(1, 1), ws.Cells(1, colCount))
Set c = .Find("C", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=True)
'.......
End With

--
HTH

Bob Phillips

"Vik" wrote in message
...
Hi All,

I have these lines inside a function -

Dim ws As Worksheet
Set ws = Sheets(SheetName)
ws.Activate
colCount = ws.UsedRange.Columns.count
With ws.Range(Cells(1, 1), Cells(1, colCount))
Set c = .Find("C", LookIn:=xlValues, LookAt:=xlWhole,
MatchCase:=True)
'.......
End With

Until ws.Activate was added, it jumped out of a function on With
statement.
Weird....