PDA

View Full Version : Solved: How to refer to a cell in VBA



fallendeacon
12-16-2011, 08:47 PM
Hello!

I am using Excel 2010, and I just started teaching myself VBA today. I'm working on my first script (just a test script for fun) and I can't think of how to proceed further.

I want the script to be able to count the number of cells in whatever column is active, and then I want to to go through each cell in that column (minus the header on row 1) and multiply any value below 200 by 5%.

Here is the logic I was thinking of using to do it:

**When macro is activated, find the number of non-blank cells in the active column.
** Start at row 2 of active column
** Enter a loop "Do..."
** Check with an "If" statement whether or not the cell value is below 200
** If cell value is under 200 Then increase cell value by 5%
** Increase variable count i = i +1
** Exit loop once "i = n"

I'm not very clear on how to reference a cell in this manner (using a variable for the row, etc.)
If you have a recommendation or hint, please teach me =)

This is the code I have right now, and I commented the section I have no idea how to do:
Sub Test()
Dim col As Integer, rng As Range, n#, b#, i#, r#
col = Selection.Column
If Application.WorksheetFunction.CountA(Columns(col)) = 0 Then
MsgBox "You have selected a blank column"
n = 0
Else
Set rng = Intersect(Columns(col), ActiveSheet.UsedRange)
On Error Resume Next
b = rng.Cells.SpecialCells(xlCellTypeBlanks).Count
n = rng.Cells.Count - b
i = 2
On Error GoTo 0
Do
'r = value of Active Column row i
'If r < 200 Then FormulaR1C1 = " whatever r * 1.05 is"
'i = i + 1
Loop Until i = n
End If
End Sub

mikerickson
12-16-2011, 10:46 PM
fallendeacon,
Welcome to the forum.

Please use thread titles that describe the problem and are useful to the search feature.
Titles like "Help" and "Easy" are little use to later visitors to this site.
I've changed the title of this thread, this time.
Thank you.

About your problem:
You might prefer this logic

Begin with row 2 of the selected column.

repeat until there are no filled cells below the current cell
if the current cell is < 200 then multiply by 1.05
change the current cell to the next row
end repeat

the VBA would look like

Dim currentRow as Long

With Selection.EntireColumn.Columns(1)
currentRow = 2

Do Until Application.CountA(.EntireColumn) = Application.CountA(Range(.Cells(1,1), .Cells(currentRow,1)))

with .Cells(currentRow, 1)
If .Value < 200 Then
.Value = 1.05 * .Value
End If
End With

currentRow = currentRow + 1
Loop
End With

Paul_Hossler
12-17-2011, 09:47 AM
Like Mike says, welcome to the forums and the fun (sometimes frustrating) world of VBA

In addition to Mike's, I added a few suggestions to your code that you might find useful. There is a lot of 'personal style' in these things :yes

Error checking, catching, and handling seems like it usually takes 1/2 my time at least. The rest is easy :thumb



'Suggestion: always use Option Explicit in a module to require everying to be Dim-ed
'Helps to catch tpyos (OK, I did that on purpose)
'There's a Tools, Options setting to do that each time
Option Explicit

Sub Test()
'Suggestion: always using more meaningful varable names
'OK for short, stand-alone sub, but for a complex one after 6 months
' it would make life easier
Dim iNumberOfBlankCells As Long
Dim iSelectionColNumber As Long, rng As Range, n#, i#, r#

'this assumes that what is selected is a single column range
'it only returns the first column's number
'it will fail is something else (e.g. a Textbox) is selected
'Suggestion: Don't assume
'If Not Typeof Selection is Range then ...
iSelectionColNumber = Selection.Column

If Application.WorksheetFunction.CountA(Columns(iSelectionColNumber)) = 0 Then
MsgBox "You have selected a blank column"
'BTW -- n is not used in this path
Exit Sub
End If

Set rng = Intersect(Columns(iSelectionColNumber), ActiveSheet.UsedRange)

'Suggestion: on error resume 'can' be useful, but I like to catch and explictly handle an error condition
iNumberOfBlankCells = -1
On Error Resume Next
iNumberOfBlankCells = rng.Cells.SpecialCells(xlCellTypeBlanks).Count
On Error GoTo 0

If iNumberOfBlankCells = -1 Then
MsgBox "No blank cells"
End If


'not sure why you wanted the number of blank cells, since there could be blanks
'inside the data
'Suggestion: For / Next loops always seems easier for me to use
'Suggestion: Runs faster with Screen Updating off
Application.ScreenUpdating = False
For i = 2 To rng.Rows.Count
'Suggestion: if there's a lot of activity, then display progress
Application.StatusBar = "Processing row #" & i
If Len(ActiveSheet.Cells(i, iSelectionColNumber).Value) > 0 Then
If ActiveSheet.Cells(i, iSelectionColNumber).Value < 200 Then
ActiveSheet.Cells(i, iSelectionColNumber).Value = _
1.05 * ActiveSheet.Cells(i, iSelectionColNumber).Value
End If
End If
Next i
'clear the status bar, and turn on screen updating
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Sub Test2()
Dim rData As Range, rCell As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set rData = Nothing
On Error Resume Next
Set rData = Intersect(Selection.Columns(1).EntireColumn, Selection.Parent.UsedRange)
On Error GoTo 0

If rData Is Nothing Then Exit Sub

'Suggestion: For Each is handy with objects
For Each rCell In rData.Cells
With rCell
If Len(.Value) > 0 Then If .Value < 200 Then .Value = 1.05 * .Value
End With
Next
End Sub


Paul

fallendeacon
12-17-2011, 12:41 PM
Wow!

Thanks to both of you. The two solutions are very different, but both provide me with insight to how to accomplish what I was trying to do.

I especially want to thank Paul for all the helpful comments he added in with the code. That really helps me understand the logic and internalize it (which of course means I'm more likely to retain it and use it on my own).


EDIT: According to the rules, I'm supposed to mark this thread as "solved" using the thread tools. I followed the "thread tools" link button which takes me to the panel below Quick Reply, but there is nothing regarding a "solved" option. Am I missing something obvious?