Help to clean up code and reduce number of "select"s

[Carlos Ruano]

Hi, I am trying to clean up some of my code. I am just starting out with VBA, and read that it is a best practice to eliminate the use of "Select" if possible. I have had great success with this in some areas of my code such as:

Old:

Sheets("Reference Values").Select
Range("A2:S4").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Albumin").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlLast

New:

Sheets("Reference Values").Range("A2:S4").Copy Sheets("Albumin").Range("A1")

But my luck has not been so good in other sections of my code. The next piece of code that I am working on is this:

Sheets("Albumin").Select
Range("Q4").Select
ActiveCell.FormulaR1C1 = "Spacer"
Range("R4").Select
ActiveCell.FormulaR1C1 = "Test Result N"
Range("S4").Select
ActiveCell.FormulaR1C1 = "Result"
Range("Q5:S5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"
Columns("Q:Q").ColumnWidth = 2
Range("R5").Select
ActiveCell.FormulaR1C1 = "=VALUE([@[Test Result]])"
Range("S5").Select
ActiveCell.FormulaR1C1 = "=IF([@[Test Result N]]<+R1C19,""Review"","""")"
Range("S1").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
End With
Range("t5").Select

I made the following attempt to clean it up, but it does not function as expected:


Sheets("Albumin").Range("Q4") = "Spacer"
Sheets("Albumin").Range("R4") = "Test Result N"
Sheets("Albumin").Range("S4") = "Result"
Sheets("Albumin").Range("Q5:S5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"
Columns("Q:Q").ColumnWidth = 2
Sheets("Albumin").Range("R5") = "=Value([@[Test Result]])"
Sheets("Albumin").Range("S5") = "=IF([@[Test Result N]]"<+R1C19,""Review"","""")"
Sheets("Albumin").Range("t5").Select

In sheet "Albumin" I have an existing table from an earlier part of my macro. The above macro adds new columns to the existing table in columns Q, R, & S. The first problem that I encounter is with the next section that is supposed to format columns Q : S to number format. The format does not change at all with the above code. If I add Sheets("Albumin").Select at the top, then the formatting occurs. Why is this? If I add that line, it defeats my goal of getting rid of "Select".

The next problem is with the 9th line (Sheets("Albumin").Range("S5") = "=IF([@[Test Result N]]<+R1C19,""Review"","""")"). I cannot understand why the 8th line would work, and the 9th will not. They seem the same to me!

Finally, the last line has a "Select" that I do want to keep.

Thank you for any help!

 

[Hui]

Sheets("Albumin").Range("Q4") = "Spacer"
Sheets("Albumin").Range("R4") = "Test Result N"
Sheets("Albumin").Range("S4") = "Result"
Sheets("Albumin").Range("Q5:S5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"
Columns("Q:Q").ColumnWidth = 2
Sheets("Albumin").Range("R5") = "=Value([Test Result N])"
Sheets("Albumin").Range("S5").FormulaR1C1 = "=IF([Test Result N]<R[1]C[19],""Review"","""")"
Sheets("Albumin").Range("t5").Select