Looping through a range of cells!

[drkestrel]

I want a button that would at the click of a button update a range of values (given in two cells) with original values * 1.5
i.e.
I would have values in Cell A1 and A2 defining the 'range'

Dim myA1 as String
Dim myA2 as String

myStart = Sheets(1).Range("A1")
myFinish = Sheets(1).Range("A2")

'The following doesn't work, nice try, though, isn't it!
With Sheets(1).Range(myStart:MyFinish).Cells
    .Value = .Value * 2
End With;

I guess I could use Cells(rowIndex,colIndex), but then I would have to enter 1 instead of A for start/end ranges!

 

[drkestrel]

The following doesn't work either!

Dim myRange As Range
Set myRange = Worksheets(1).Range("A1:A2")
Dim m, n As Integer
For m = 1 To myRange.Rows.Count
    For n = 1 To myRange.Columns.Count
        myRange.Cells(m, n).Value = myRange.Cells(m, n).Value * 2
    Next n
Next m

 

[Klae]

This works on Excel 2000.

Sub changeValues()
Dim rwIndex, colIndex As Integer
Dim origValue, newValue As Single

For rwIndex = 1 To 4
    For colIndex = 1 To 10
        With Worksheets("Sheet1").Cells(rwIndex, colIndex)
            origValue = .Value
            newValue = origValue * 1.5
            .Value = newValue
        End With
    Next colIndex
Next rwIndex


End Sub

 

[DarkSun]

Here is a mix of my code and Klaes code. It means you can select the range before clicking on the button then it will change the values in the highlighted area.
I will look into allowing you to define the range in two cells.

Private Sub CommandButton1_Click()
Dim MySelection As Range
Set MySelection = Selection

NumberOfColumns = MySelection.Columns.Count
NumberOfRows = MySelection.Rows.Count
FirstRow = MySelection.Row
FirstColumn = MySelection.Column

Dim rwIndex, colIndex As Integer
Dim origValue, newValue As Single

For rwIndex = FirstRow To (FirstRow + NumberOfRows - 1)
For colIndex = FirstColumn To (FirstColumn + NumberOfColumns - 1)
With ActiveSheet.Cells(rwIndex, colIndex)
origValue = .Value
newValue = origValue * 1.5
.Value = newValue
End With
Next colIndex
Next rwIndex
End Sub

 

[DarkSun]

Okay, here is the code. It is basically the same but it selects the range from within VB using the two addresses stated the two cells.

Private Sub CommandButton2_Click()
Dim myStart As String
Dim myFinish As String
Dim MySelection As Range
Dim Start As String
Dim Finish As String

Start = "A1" 'location of first address
Finish = "A2" 'location of second address

myStart = ActiveSheet.Range(Start).Value
myFinish = ActiveSheet.Range(Finish).Value

ActiveSheet.Range(myStart & ":" & myFinish).Select
Set MySelection = Selection

NumberOfColumns = MySelection.Columns.Count
NumberOfRows = MySelection.Rows.Count
FirstRow = MySelection.Row
FirstColumn = MySelection.Column

Dim rwIndex, colIndex As Integer
Dim origValue, newValue As Single

For rwIndex = FirstRow To (FirstRow + NumberOfRows - 1)
For colIndex = FirstColumn To (FirstColumn + NumberOfColumns - 1)
With ActiveSheet.Cells(rwIndex, colIndex)
origValue = .Value
newValue = origValue * 1.5
.Value = newValue
End With
Next colIndex
Next rwIndex

End Sub

 

[LoNeRaVeR]

Assuming you already created the button, and you set the range within the macro, the code is pretty simple.

Sub Change()
Dim oCell As Object
Dim sRange As String
sRange = "A1:A2"
For Each oCell In Range(sRange)
oCell.Value = oCell.Value * 2
Next oCell
End Sub

Changing the Range within the program can also be done.

LoNeRaVeR

 

[Kaah]

another one,

for iCount = 1 to yourrange.cells.count
yourrange.cells(icount).value = yourrange.cells(icount).value * 1.5
next icount

you get the idea,,