Eliminating "Selects"

[Guest]

I am a VBA novice and posted some code like:

Sheets("Input").select
Range("A1").select
ActiveCell.FormulaR1C1 = "=IF(R2C2>0,""Yes"",""No"")"
ActiveCell.Offset(1,0).Select
ActiveCell.Value = 1

The groans were almost audible over the Internet! While this code gets the
job done, I get the distinct impression that this is the slowest and
clunkiest way to do things. How else do I indicate the sheets and cell I
want to work in without selecting it?

Thanks

 

[Otto Moehrbach]

Sub DoIt()
With Sheets("Input")
.Range("A1").FormulaR1C1 = "=IF(R2C2>0,""Yes"",""No"")"
.Range("A1").Offset(1) = 1
End With
End Sub

HTH Otto

 

[Earl Kiosterud]

Adam,

While not the most efficient, there's nothing wrong with using selects to do
stuff to a worksheet in a macro. And they're more fun to watch, too, if you
want to impress your friends there at work. Otto's code is the direct
approach, runs faster, and is more professional. There are some cases
where code is made TOO compact, and is not easy to modify later, or to
debug.

Here are some general suggestions:

move data sheet 1 to Sheet2, with cell formatting:
Sheets("Sheet1").Range("A1").Cut Destination:=Sheets("Sheet2").Range("A1")

Copy data to Sheet2, with cell formatting
Sheets("Sheet1").Range("A2").Copy Destination:=Sheets("Sheet2").Range("A2")

Without formatting:
Sheets("Sheet2").Range("A3") = Sheets("Sheet1").Range("A3")