Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Eliminating "Selects"
I am a VBA novice and posted some code like:
Sheets("Input").select Range("A1").select ActiveCell.FormulaR1C1 = "=IF(R2C20,""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 |
#2
|
|||
|
|||
Sub DoIt()
With Sheets("Input") .Range("A1").FormulaR1C1 = "=IF(R2C20,""Yes"",""No"")" .Range("A1").Offset(1) = 1 End With End Sub HTH Otto "Adam1 Chicago" wrote in message ... I am a VBA novice and posted some code like: Sheets("Input").select Range("A1").select ActiveCell.FormulaR1C1 = "=IF(R2C20,""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 |
#3
|
|||
|
|||
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") -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Adam1 Chicago" wrote in message ... I am a VBA novice and posted some code like: Sheets("Input").select Range("A1").select ActiveCell.FormulaR1C1 = "=IF(R2C20,""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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mouse selects area | Excel Worksheet Functions | |||
Eliminating Single Quote Before Equals Sign | Excel Worksheet Functions |