Place found value to specific colm header

Closed
DAN - Sep 19, 2008 at 05:07 AM
 DAN - Oct 23, 2008 at 04:06 AM
Hello,
I have this code which looks like this
If MYF(FLD) = "012007" Then Cells(ActiveCell.Row, "J").Value = "Y"
If MYF(FLD) = "012007" Then Cells(ActiveCell.Row, "K").Value = "Y"
If MYF(FLD) = "022007" Then Cells(ActiveCell.Row, "L").Value = "Y"
If MYF(FLD) = "032007" Then Cells(ActiveCell.Row, "M").Value = "Y"
If MYF(FLD) = "042007" Then Cells(ActiveCell.Row, "N").Value = "Y"
If MYF(FLD) = "052007" Then Cells(ActiveCell.Row, "O").Value = "Y"
If MYF(FLD) = "062007" Then Cells(ActiveCell.Row, "P").Value = "Y"
If MYF(FLD) = "072007" Then Cells(ActiveCell.Row, "Q").Value = "Y"
If MYF(FLD) = "082007" Then Cells(ActiveCell.Row, "R").Value = "Y"
If MYF(FLD) = "092007" Then Cells(ActiveCell.Row, "S").Value = "Y"
If MYF(FLD) = "102007" Then Cells(ActiveCell.Row, "T").Value = "Y"
If MYF(FLD) = "112007" Then Cells(ActiveCell.Row, "U").Value = "Y"
If MYF(FLD) = "122007" Then Cells(ActiveCell.Row, "V").Value = "Y"
If MYF(FLD) = "012008" Then Cells(ActiveCell.Row, "W").Value = "Y"
If MYF(FLD) = "022008" Then Cells(ActiveCell.Row, "X").Value = "Y"
what it does is place Y to a column with specific labels 01/07, 02/07, 03/07 and so on if the value matches... the macro is to slow using IFs, need help or suggestions, to make this faster please.. thanks

2 responses

Ivan-hoe Posts 433 Registration date Saturday February 16, 2008 Status Member Last seen October 17, 2008 110
Sep 19, 2008 at 07:44 AM
Hello Dan
Using Select Case instead of many If should make your code run faster.
Try this :
Dim MyRange As Range

Application.ScreenUpdating = False
With ActiveCell
    Select Case MYF(FLD)
        Case Is = "012007": Set MyRange = Range("J" & .Row & ":K" & .Row)
        Case Is = "022007": Set MyRange = Range("L" & .Row)
        Case Is = "032007": Set MyRange = Range("M" & .Row)
        '...
        Case Is = "022008": Set MyRange = Range("X" & .Row)
        Case Else: Set MyRange = Nothing
    End Select
End With

If Not MyRange Is Nothing Then MyRange.Value = "Y"
Application.ScreenUpdating = True

I.
0
sir IVAN-HOE

cant seem to make it work... i'll try to figure things out some more...
0