CODEWITHSUNDEEP
vbaexcel-2007
Tim
Tim

Reputation: 111

Retrieve a column number from a range

How can I retrieve a column number from a range of cells so I can perform a column based calculation.

    Set rng = regSheet.Range("F" & i & ":K" & i)
    For Each cell In rng.Cells
        Select Case rng.Column
            Case 6
                sum = sum + (cell.Value * 5)
            Case 7
                sum = sum + (cell.Value * 15)
            Case 8
                sum = sum + (cell.Value * 5)
            Case 9
                sum = sum + (cell.Value * 5)
            Case 10
                sum = sum + (cell.Value * 10)
            Case 11
                sum = sum + (cell.Value * 20)

        End Select

Upvotes: 0

Views: 701

Answers (3)

bnlake
bnlake

Reputation: 11

I think I understand your question but your code is quite vague and a bit static. This method of addressing cells is sloppy since the columns could move. I would recommend you use tables.

Either way, give this code a shot and see if it helps.

Public Sub test()
Dim wsSheet As Worksheet
Dim Rng As Range
Dim i As Long   'Not sure the purpose of this var?
Dim sum As Long 'Not sure the purpose of this var?

Set wsSheet = Worksheets("regSheet")
Set Rng = wsSheet.Range("F" & i & ":K" & i)
i = 1 'Not sure the purpose of this variable?

With Rng
    Dim xCol As Range
    For Each xCol In Rng.Columns
        Dim xCell As Range
        For Each xCell In xCol
            Select Case xCell.Column
                Case 6
                    sum = sum + (xCell.Value * 5)
                Case 7
                    sum = sum + (xCell.Value * 15)
                Case 8
                    sum = sum + (xCell.Value * 5)
                Case 9
                    sum = sum + (xCell.Value * 5)
                Case 10
                    sum = sum + (xCell.Value * 10)
                Case 11
                    sum = sum + (xCell.Value * 20)
            End Select
        Next xCell
    Next xCol
End With

End Sub

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33474

=((F10 + H10 + I10) * 5) + (G10 * 15) + (J10 * 10) + (K10 * 20)

EDIT: The value of i is constant. I have assumed it to be 10 for the code above. Does this formula help?

Let's say you don't want to use formula & use VBA. In that case, you don't need to write a for loop because you are traversing the cells of a single row.

Sub test()
Dim total As Double
Dim i As Integer
Dim Rng As Range

i = 10
Set Rng = Sheet1.Range("F" & i & ":K" & i)

total = total + (Rng.Cells(1).Value * 5)
total = total + (Rng.Cells(2).Value * 15)
total = total + (Rng.Cells(3).Value * 5)
total = total + (Rng.Cells(4).Value * 5)
total = total + (Rng.Cells(5).Value * 10)
total = total + (Rng.Cells(6).Value * 20)

MsgBox total
End Sub

Upvotes: 0

guitarthrower
guitarthrower

Reputation: 5834

Try changing this line:

Select Case rng.Column

To

Select Case cell.Column

Upvotes: 2

Related Questions