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
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