How to affect a value to a cell using conditions and a loop?

SorcererMickey 0
Viewed 27

I am trying to build a short program that affects a specific number to a column. This number should be determined following a comparison - I want to compare the values of another column with fixed ones - and a loop should repeat this process for the entire column.

I already wrote some lines below, and I cannot find which ones are not correct. Any help would be much appreciated!

Sub comparison()

    Dim Cell As Range
   
    For Each Cell In Range("I2", Range("I2").End(xlDown))
        If Cell > 3000000 Then
        Cell.Offset(, -2).Value = 75
        ElseIf 1000000 < Cell < 3000000 Then
        Cell.Offset(, -2).Value = 100
        ElseIf 100000 < Cell < 1000000 Then
        Cell.Offset(, -2).Value = 200
        ElseIf Cell < 100000 Then
        Cell.Offset(, -2).Value = 300
        End If
    Next Cell
        
End Sub
vba excel
2 Answers

Column Values in Range (Math)

Option Explicit

Sub PopulateColumn() ' this is also bad; make it more descriptive (personalized)
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    ' If it's in the workbook containing this code and you know its name,
    ' then reference it explicitly instead, e.g.:
    'Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Reference the source one-column range.
    Dim srg As Range
    Set srg = ws.Range("I2", ws.Cells(ws.Rows.Count, "I").End(xlUp))
    
    ' Reference the destination one-column range.
    Dim drg As Range: Set drg = srg.EntireRow.Columns("G")
    
    Dim sCell As Range
    Dim sCellValue As Variant
    Dim r As Long
    Dim dCell As Range
    Dim dResult As Double
    
    ' Reference each source cell...
    For Each sCell In srg.Cells
        ' ... and store its value.
        sCellValue = sCell.Value
        ' Increase the row index by 1...
        r = r + 1
        ' ... to reference the destination cell.
        Set dCell = drg.Cells(r)
        ' Check if the source value is a number.
        If VarType(sCellValue) = vbDouble Then ' is a number
            ' Determine the destination result.
            Select Case sCellValue
                Case Is < 100000: dResult = 300
                Case Is < 1000000: dResult = 200
                Case Is < 3000000: dResult = 100
                Case Else: dResult = 75
            End Select
            ' Write the result to the destination cell.
            dCell.Value = dResult
        Else ' is not a number
            ' Clear the contents of the destination cell.
            dCell.ClearContents
        End If
    Next sCell
        
    ' Inform.
    MsgBox "Column populated.", vbInformation
        
End Sub
VBasic20080

  1. Cell is a reserved word in Excel. You cannot use reserved words as variable names.

  2. The cases are ordered, if you have already established that something is less than a number, you don't need to say it again.

  3. Select Case has a To that can be used to cover a range of numbers

Using these principals your code looks like this:

Option Explicit

Public Sub comparison()

    Dim thisCell As Range
    For Each thisCell In Range("I2", Range("I2").End(xlDown))
        Select Case thisCell.Value
        Case Is > 3000000
            thisCell.Offset(, -2).Value = 75
        Case 3000000 To 1000000
            thisCell.Offset(, -2).Value = 100
        Case 1000000 To 100000
            thisCell.Offset(, -2).Value = 200
        Case Is < 100000
            thisCell.Offset(, -2).Value = 300
        End Select
    Next thisCell
        
End Sub
HackSlash0
Related