Consulting

Results 1 to 4 of 4

Thread: Solved: How to refer to a cell in VBA

  1. #1

    Solved: How to refer to a cell in VBA

    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:
    [VBA]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[/VBA]

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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

    [VBA]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[/VBA]

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,728
    Location
    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

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


    [VBA]
    '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
    [/VBA]

    Paul

  4. #4
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •