[Resolved] Applying a formula to a highlighted region

[PhalSe]

I hope this makes sense. I'd like to write code to apply the round function to each cell in a user selected region. For example if i highlight with my mouse from A1 to C3 i want all of those cells to be rounded. I found some code in here that uses hard coded values for the region, but that's not exactly what i'm looking for.

I thought i could do it like this:

Dim CellValue As Double

For Each c In ActiveSheet.CurrentRegion.Cells
CellValue = ActiveCell.Value
ActiveCell.Value = Round(CellValue, 0)
Next

But it only rounds the first Cell in the region.

Thanks

[RobDog888]

This will help you by showing you some different ways to do what you want.

Private Sub cmdRoundRegion_Click()
 
    Dim i As Integer
    Dim ii As Integer
    
    areaCount = Application.Selection.Areas.Count
    If areaCount <= 1 Then
        MsgBox "The selection contains " & Application.Selection.Columns.Count & " columns."
        MsgBox "The selection contains " & Application.Selection.Rows.Count & " rows."
    Else
        For i = 1 To areaCount
            MsgBox "Area " & i & " of the selection contains " & Application.Selection.Areas(i).Columns.Count & " columns."
            MsgBox "Area " & i & " of the selection contains " & Application.Selection.Areas(i).Rows.Count & " rows."
        Next i
    End If
    Set xRange = Application.ActiveCell.CurrentRegion
    c = xRange.Columns(xRange.Columns.Count).Column
    c = Chr(64 + c)
    b = xRange.Columns(1).Column
    b = Chr(64 + b)
    MsgBox "First column = " & b
    MsgBox "First row = " & xRange.Rows(1).Row
    MsgBox "Last column = " & c
    MsgBox "Last row = " & xRange.Rows(xRange.Rows.Count).Row
    For i = xRange.Rows(1).Row To xRange.Rows(xRange.Rows.Count).Row
        For ii = xRange.Columns(1).Column To xRange.Columns(xRange.Columns.Count).Column
            b = xRange.Columns(ii).Column
            b = Chr(64 + b)
            CellValue = Worksheets("Sheet1").Range(b & i).Value
            If IsEmpty(CellValue) = False Then
                Worksheets("Sheet1").Range(b & i).Value = Round(CellValue, 0)
            End If
        Next
    Next
 
End Sub

HTH

[RobDog888]

After running the macro....

[PhalSe]

Thank you, I don't fully understand all of that code, but i've got an idea how it works. If i'm reading this right it will only work if the selected region is in "Sheet1". I can't be certain that I will always be working in "Sheet1" or that it won't have been renamed to something else. Can i replace "Worksheets("Sheet1")" with "ActiveSheet"?

[RobDog888]

Yes you can. I was just trying to show some different ways to do the same thing.
Updated code fragment.

'...
'...
'...
CellValue = ActiveSheet.Range(b & i).Value
If IsEmpty(CellValue) = False Then
    ActiveSheet.Range(b & i).Value = Round(CellValue, 0)
End If
'...
'...
'...

[PhalSe]

I'm having some problems with the code. I was doing some tests and it seems to behave funny if there is anything in the cells before it. Attached is a zip file with a before and after example. I don't have hosting to post teh images inline, sorry.

This is the code i'm using, it isn't changed very much at all.

Sub cmdRoundRegion_Click()
'
' cmdRoundRegion_Click Macro
' Macro Written by RobDog888 on VBForums, Alterred by 
'
 
    Dim i As Integer
    Dim ii As Integer
    
    MsgBox "Now starting cmdRoundRegion_Click Macro"
    areaCount = Application.Selection.Areas.Count
    If areaCount <= 1 Then
        MsgBox "The selection contains " & Application.Selection.Columns.Count & " columns."
        MsgBox "The selection contains " & Application.Selection.Rows.Count & " rows."
    Else
        For i = 1 To areaCount
            MsgBox "Area " & i & " of the selection contains " & Application.Selection.Areas(i).Columns.Count & " columns."
            MsgBox "Area " & i & " of the selection contains " & Application.Selection.Areas(i).Rows.Count & " rows."
        Next i
    End If
    Set xRange = Application.ActiveCell.CurrentRegion
    c = xRange.Columns(xRange.Columns.Count).Column
    c = Chr(64 + c)
    b = xRange.Columns(1).Column
    b = Chr(64 + b)
    MsgBox "First column = " & b
    MsgBox "First row = " & xRange.Rows(1).Row
    MsgBox "Last column = " & c
    MsgBox "Last row = " & xRange.Rows(xRange.Rows.Count).Row
    For i = xRange.Rows(1).Row To xRange.Rows(xRange.Rows.Count).Row
        For ii = xRange.Columns(1).Column To xRange.Columns(xRange.Columns.Count).Column
            b = xRange.Columns(ii).Column
            b = Chr(64 + b)
            CellValue = ActiveSheet.Range(b & i).Value
            If IsEmpty(CellValue) = False Then
                ActiveSheet.Range(b & i).Value = Round(CellValue, 0)
            End If
        Next
    Next
 
End Sub

It always evaluates as if areaCount is <= 1, which seems odd to me. It also prints out the wrong rows and columns.

[RobDog888]

You don't need any hosting to post the images in line. Right click
and click View Source... and look for the code to show the image.

All I am doing is attaching the image to the post and then edit
the post after and add an IMG and point the url text to the
attachment using "http://www.vbforums.com/attachment.php?
s=&amp;postid=88888888" where 88888888 is the id of the
attachment. You can get the id after the updating of the
post. Look at your url for the id. So you actually have to edit your
post twice.

This keeps the image on vbforums webservers and i dont have to
bloat my webserver with a bunch of images.

Hope I explained it ok.

I will check out your highlight issue.

[PhalSe]

I think i figured out the problem. I wasn't aware earlier but there is a difference between a Range and a Region. Sorry for any confusion. A Region is apparantly a range bounded by empty Cells. I updated a line to remove this issue. The other issue that wasn't immediately apparant was that it was actually making changes for me offset by several columns. I made another adjustment to resolve that. Thank you for your help RobDogg, you pointed me in the right direction. I get frustrated with VBA trying to learn all the predefined objects, properties, and methods.

Here is the updated code that seems to work for me. Later i'll try updating it further so it works on multiple selected ranges, but this is good enough for now.

Edit: the change was easier than i thought because i just finished it. I've updated the code as well.

Sub cmdRoundRange()
'
' cmdRoundRange Macro
' Macro Written by RobDog888 on VBForums, Alterred by Michael Lawrence Jr.
'
 
    Dim i As Integer
    Dim ii As Integer
    Dim ac As Integer
    
    areaCount = Application.Selection.Areas.Count
    MsgBox "areaCount = " & areaCount
    If areaCount <= 1 Then
        Set xRange = Application.Selection
        c = xRange.Columns(xRange.Columns.Count).Column
        c = Chr(64 + c)
        b = xRange.Columns(1).Column
        b = Chr(64 + b)
        MsgBox "First column = " & b
        MsgBox "First row = " & xRange.Rows(1).Row
        MsgBox "Last column = " & c
        MsgBox "Last row = " & xRange.Rows(xRange.Rows.Count).Row
        For i = xRange.Rows(1).Row To xRange.Rows(xRange.Rows.Count).Row
            For ii = xRange.Columns(1).Column To xRange.Columns(xRange.Columns.Count).Column
                b = ii
                b = Chr(64 + b)
                CellValue = ActiveSheet.Range(b & i).Value
                MsgBox "CellValue of " & b & i & " is " & CellValue
                If IsEmpty(CellValue) = False Then
                    ActiveSheet.Range(b & i).Value = Round(CellValue, 0)
                End If
            Next
        Next
    Else
        For ac = 1 To areaCount
            Set xRange = Application.Selection.Areas(ac)
            c = xRange.Columns(xRange.Columns.Count).Column
            c = Chr(64 + c)
            b = xRange.Columns(1).Column
            b = Chr(64 + b)
            MsgBox "First column = " & b
            MsgBox "First row = " & xRange.Rows(1).Row
            MsgBox "Last column = " & c
            MsgBox "Last row = " & xRange.Rows(xRange.Rows.Count).Row
            For i = xRange.Rows(1).Row To xRange.Rows(xRange.Rows.Count).Row
                For ii = xRange.Columns(1).Column To xRange.Columns(xRange.Columns.Count).Column
                    b = ii
                    b = Chr(64 + b)
                    CellValue = ActiveSheet.Range(b & i).Value
                    MsgBox "CellValue of " & b & i & " is " & CellValue
                    If IsEmpty(CellValue) = False Then
                        ActiveSheet.Range(b & i).Value = Round(CellValue, 0)
                    End If
                Next
            Next
 
        Next ac
    End If
    
End Sub

[RobDog888]

CurrentRegion vs. Selection range. I see.

Glad its working correctly now.