Selecting a wide range of value in Excel VBA

Question

so here's I've this code in Excel VBA

Sub GetValue()

Dim rRH, rYear, r1 As Range
Dim RowIndex, ColIndex, yearRow As Integer
Dim rh1 As Integer
Dim rh1Pct As Double
Dim year As String

RowIndex = 30
yearRow = 10

Do While RowIndex < (RowIndex + yearRow)
    Set rRH = ThisWorkbook.Sheets("CF-Apar").Range("M" & CStr(RowIndex))

    If rRH.Value <> "" Then
        rh1 = rRH.Value
        year = ThisWorkbook.Sheets("CF-Apar").Range("A" & CStr(RowIndex)).Value
        Exit Do
    End If

    RowIndex = RowIndex + 1
Loop

RowIndex = 12
rh1Pct = Range("D12").Value

ColIndex = 0
Set rYear = Range("D120")
Do While ColIndex < yearRow

    If CInt(year) > CInt(rYear.Value) Then
        Set r1 = rYear
        r1.Offset(123, 0).Value = "0"
    End If

    If year = rYear.Value Then
        rYear.Offset(123, 0).Value = rh1 * rh1Pct
        Exit Do
    End If

    Set rYear = rYear.Next
    Set r1 = r1.Next
Loop
End Sub

the code is to change or move the value in current cell whenever a value in CF-Apar worksheet is changed, but the current code is only cover one range (for this case, it's M, in CF-Apar index), the question is, how do I add more range cell, for example from M30 to Q40, how is the best way do achieve this?

Answer 1

As suggested in comments you need to use nested loops. This should go as follows (proposed code below is not tested):

'beginning of your sub here

'additional loop
Dim i as Byte
For i=13 to 17 'columns M to Q

'your loop but changed inside
Do While RowIndex < (RowIndex + yearRow)
    Set rRH = ThisWorkbook.Sheets("CF-Apar").Cells(RowIndex, i)

    If rRH.Value <> "" Then
        rh1 = rRH.Value
        year = ThisWorkbook.Sheets("CF-Apar").Cells(RowIndex, i).Value
        Exit Do
    End If

    RowIndex = RowIndex + 1
Loop

RowIndex = 30
Next i
'rest of your code here

EDIT to explain questions from comments. Some alternative options for you:

A) base solution in my code

Set rRH = ThisWorkbook.Sheets("CF-Apar").Cells(RowIndex,i)
'....
rh1 = rRH.Value   'for column i

B) alternative solution for your code

Set rRH = ThisWorkbook.Sheets("CF-Apar").Cells(RowIndex, i)
Set rRH1 = ThisWorkbook.Sheets("CF-Apar").Cells(RowIndex, "N")
'....
rh1 = rRH.Value   'for column i
rh2 = rRH2.Value  'for column N

C) alternative solution for your code

Set rRH = ThisWorkbook.Sheets("CF-Apar").Cells(RowIndex, "M")
'....
rh1 = rRH.Value   'for column M
rh2 = rRH2.Offset(0, 1).Value  'for column N

and some other mixed options are available.