ActiveCell

[Serious]

What does the 'With' statement do?

Also, what is wrong with the code below?

Private Sub Inputdata_Click()
Worksheets("Input").Activate
Cells(5, 3).Select
If (ActiveCell.Value = "") Then Exit Sub
Worksheets("Sheet2").Activate
Cells(5, 4).Select
ActiveCell.Value = 1
End Sub

I get an error that says 'Select method of Range Class fails' and I dont know why. I am new to Visual Basic and I would appreciate anyone's help.
Thanks
Seri

[Al Chara]

From the help file on the "with statement"

Using With Statements


The With statement lets you specify an object or user-defined type once for an entire series of statements. With statements make your procedures run faster and help you avoid repetitive typing.

The following example fills a range of cells with the number 30, applies bold formatting, and sets the interior color of the cells to yellow.

Sub FormatRange()
With Worksheets("Sheet1").Range("A1:C10")
.Value = 30
.Font.Bold = True
.Interior.Color = RGB(255, 255, 0)
End With
End Sub

You can nest With statements for greater efficiency. The following example inserts a formula into cell A1, and then formats the font.

Sub MyInput()
With Workbooks("Book1").Worksheets("Sheet1").Cells(1, 1)
.Formula = "=SQRT(50)"
With .Font
.Name = "Arial"
.Bold = True
.Size = 8
End With
End With
End Sub

[Al Chara]

You are getting a Runtime error 1004, The error is for the following reason:

The method can't be used in the applied context. Specifically, some Range object methods require that the range contain data. If the range does not contain data, the method fails.

Change the code to:

Private Sub Inputdata_Click()
If Worksheets("Input").Cells(5, 3).Value = "" Then Exit Sub
Worksheets("Sheet2").Cells(5, 4).Value = 1
End Sub

or

Private Sub Inputdata_Click()
If Worksheets("Input").Cells(5, 3).Value <> "" Then _
Worksheets("Sheet2").Cells(5, 4).Value = 1
End Sub
_________________
Hope this helps.
Kind regards, Al.

[ This Message was edited by: Al Chara on 2002-05-23 13:15 ]

[Serious]

That cleared it all up. Thank you. I love this site.