CODEWITHSUNDEEP
excelvba
user4616395
user4616395

Reputation:

Excel vba how to repeat an if then else condition on rows

I want to repeat my 'if then else' condition on the 30 rows that follow. This is my code (I am new to this).

Dim score As Range, result As Double
If Range("A2") = "2a" Then
Range("C2") = 20

Else
If Range("A2") = "3c" Then
Range("C2") = 23

So at the moment when I enter 2a / 3c in cell A2, the number 20 / 23 comes up in cell C2. I would like the same thing to happen in row 3, 4, 5 ... 30. When I enter 2a / 3c in cell A5, the number 20 / 23 comes up in cell C5. Is it possible with a loop or another type of code? Or will I have to copy this over and over for each row?

Any help is really appreciated. Thanks!

Upvotes: 0

Views: 8080

Answers (3)

snb
snb

Reputation: 343

Private Sub Worksheet_Change(ByVal Target As Range)
 if not intersect(target,range("A2:A32")) is Nothing then target.offset(,2)=iif(instr("2a3a",target)>0,20+3*abs(target="3c"),"")
End sub

Upvotes: 0

Davesexcel
Davesexcel

Reputation: 6984

Try this as well

  Dim score As Range, c As Range, x
Set score = Range("A2:A32").SpecialCells(xlCellTypeConstants, 23)
For Each c In score.Cells
    x = IIf(c = "2a", 20, IIf(c = "3c", 23, ""))
    c.Offset(0, 2) = x
Next c

Upvotes: 0

Porcupine911
Porcupine911

Reputation: 928

Here's a basic for-next loop that would accomplish what you ask...

For i = 2 To 30
    If Range("A" & i) = "2a" Then
        Range("C" & i) = 20
    End If
    If Range("A" & i) = "3c" Then
        Range("C" & i) = 23
    End If
Next

Here's a tutorial I picked at random from a Google search.

Upvotes: 1

Related Questions