Help with a simple Excel macro
 

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

Help with a simple Excel macro
 

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

"Dan R." wrote in message
oups.com...
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

Help with a simple Excel macro
 

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

--
--
-John
Please rate when your question is answered to help us and others know what
is helpful.

"Dan R." wrote in message
oups.com...
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

Help with a simple Excel macro
 

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


--
Don Guillett
SalesAid Software

"Dan R." wrote in message
oups.com...
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

Help with a simple Excel macro
 

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
"Dan R." wrote in message
oups.com...
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