Select Case Statements........

[MAButler]

I have this set up and it works fine........ but the value that it puts into column 8 (1940**) changes from time to time, and i have to be there to change the file. Can the reference to a cell (Sheet2 (Info) C6) be put in there and then all the idiots have to do is type the new number in that cell.......

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Select Case Target.Cells.Value
        Case 4505
            Target.Offset(0, 8).Value = 194069
        Case 4513
            Target.Offset(0, 8).Value = 194079
        Case 4521
            Target.Offset(0, 8).Value = 194089
        Case 4548
            Target.Offset(0, 8).Value = 194099
        Case Else
            Target.Offset(0, 8).Value = 0
        End Select
    End If
End Sub

 

[mole999]

would something like

Target.Offset(0, 8).Value = sheets("sheet1").Range("A1").value

work ?

I haven't actually tested it

 

[MAButler]

No comes up with error message

Run time error 9

Sub script out of range

 

[Dryver14]

I tried everything with this and struggled to get anything to show untill I
changed to an if statement, then it seems to work !!! I don't know why

  If Target.Column = 1 Then
      If Target.Value = 4505 Then
            Target.Offset(0, 8).Value = Sheet2.Cells(1, 1)

 

[djreiswig]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Cells.Count = 1 Then
        Select Case Target.Cells.Value
        Case 4505
            Target.Offset(0, 8).Value = Sheet2.Cells(1, 1)
        Case 4513
            Target.Offset(0, 8).Value = Sheet2.Cells(2, 1)
        Case 4521
            Target.Offset(0, 8).Value = Sheet2.Cells(3, 1)
        Case 4548
            Target.Offset(0, 8).Value = Sheet2.Cells(4, 1)
        Case Else
            Target.Offset(0, 8).Value = Sheet2.Cells(5, 1)
        End Select
    End If
End Sub

This works for me. I have sheet2 set up with the 5 numbers in column A. I added the second condition to the if to keep it from running if more than one cell is selected, like if you are trying to delete a block of cells.

 

[Dryver14]

Nice one Djreiswig,

Was that the issue with the first macro then?

 

[djreiswig]

Not sure what was up with it.

 

[MAButler]

Thanks, it works a treat. Made a slight adjustment.......

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Cells.Count = 1 Then
        Select Case Target.Cells.Value
        Case Sheet2.Cells(5, 3)
            Target.Offset(0, 8).Value = Sheet2.Cells(6, 3)
        Case Sheet2.Cells(5, 4)
            Target.Offset(0, 8).Value = Sheet2.Cells(6, 4)
        Case Sheet2.Cells(5, 5)
            Target.Offset(0, 8).Value = Sheet2.Cells(6, 5)
        Case Sheet2.Cells(5, 6)
            Target.Offset(0, 8).Value = Sheet2.Cells(6, 6)
        Case Else
            Target.Offset(0, 8).Value = Sheet2.Cells(6, 7)
        End Select
    End If
End Sub

 

[djreiswig]

Could throw in a little With statement action.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Column = 1 And .Cells.Count = 1 Then
            Select Case .Cells.Value
            Case 4505
                .Offset(0, 8).Value = Sheet2.Cells(1, 1)
            Case 4513
                .Offset(0, 8).Value = Sheet2.Cells(2, 1)
            Case 4521
                .Offset(0, 8).Value = Sheet2.Cells(3, 1)
            Case 4548
                .Offset(0, 8).Value = Sheet2.Cells(4, 1)
            Case Else
                .Offset(0, 8).Value = Sheet2.Cells(5, 1)
            End Select
        End If
    End With
End Sub