Help with a simple Excel macro

[Dan R.]

I have an Excel macro that is running very slow... it's very simple
but I'm new to programming so the way I'm doing it is probably the
worst way... Here's an example of what I'm trying to do:

If the value in column 8 is "apple" then column 9 = "fruit" and
column 10 = "red"
If the value in column 8 is "broccoli" then column 9 = "vegetable
and column 10 = "green"

It runs very slow, plus it only continues with the loop when all rows
are filled, so if one row is blank, it wont fill any rows below it.

Here's how I'm doing it, is there a better way?

Sub CreateSelect()
Dim R As Integer
R = 3
Do While Not (IsEmpty(Cells(R, 8)))
Select Case Cells(R, 8)

Case "apple"
Cells(R, 9) = "fruit"
Cells(R, 10) = "red"
Case "broccoli"
Cells(R, 9) = "vegetable"
Cells(R, 10) = "green"

End Select
R = R + 1
Loop
End Sub

 

[Zone]

Dan, this is probably better suited to an event procedure. Go to the code
editor. If the project explorer isn't showing, press Ctrl-R to show it.
Under Microsoft Excel Objects, double-click on the sheet you're using. This
will show that sheet's module. Copy this code and paste it in that sheet's
module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Long
R = Target.Row
If Target.Column = 8 Then
Select Case Cells(R, 8)
Case "apple"
Cells(R, 9) = "fruit"
Cells(R, 10) = "red"
Case "broccoli"
Cells(R, 9) = "vegetable"
Cells(R, 10) = "green"
End Select
End If
End Sub

HTH, James

 

[John Bundy]

Try this out.

Sub CreateSelect()
Dim R As Integer
R = 3
Do until R =Cells(Rows.Count, 8).End(xlUp).Row
Select Case Cells(R, 8)

Case "apple"
Cells(R, 9) = "fruit"
Cells(R, 10) = "red"
Case "broccoli"
Cells(R, 9) = "vegetable"
Cells(R, 10) = "green"

End Select
R = R + 1
Loop
End Sub

 

[Don Guillett]

I don't know if any faster but either of these work. I prefer the second
one.
Sub CreateSelect()
Dim R As Integer
For R = 1 To 3
Select Case UCase(Cells(R, 8))
Case "APPLE"
Cells(R, 9) = "fruit"
Cells(R, 10) = "red"
Case "BROCCOLI"
Cells(R, 9) = "vegetable"
Cells(R, 10) = "green"
Case Else
End Select
Next R
End Sub
Sub CreateSelect1()
Dim R As Integer
For R = 1 To 3
Select Case UCase(Cells(R, 8))
Case "APPLE"
x = "fruit"
y = "red"
Case "BROCCOLI"
x = "vegetable"
y = "green"
Case Else
End Select
Cells(R, 9) = x
Cells(R, 10) = y
Next R
End Sub

 

[Zone]

Dan, my solution assumed you wanted to fill in the other two cells as values
were entered into column 8. If you are wanting to fill in the other columns
based on data already existing in column 8, one of the other replies will be
better for you. Sorry if I clouded the issue. James