Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Excel Macro | New Users to Excel | |||
Simple Excel macro | Excel Programming | |||
simple excel macro Q | Excel Programming | |||
simple excel vba macro | Excel Programming | |||
Simple Excel Macro - Please Help | Excel Programming |