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
CodePudding user response:
Cell
is a reserved word in Excel. You cannot use reserved words as variable names.The cases are ordered, if you have already established that something is less than a number, you don't need to say it again.
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
CodePudding user response:
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