Help with checking cells and entering data from VBA to cells

[kharmaz]

Hello all!

I haven't been working in VBA in a LONG time and trying some simple checks in a workbook and making changes. When I had my first part of the code in, it was fine. When I added the Subject line string creation, I started getting errors. Any help please?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ST_1 As String
    Dim ST_2 As String
    Dim ST_3 As String
    Dim ST_4 As String
    Dim ST_5 As String
    Dim ST_6 As String
    
    Dim Object As String
    Dim Operation As String
    Dim Location As String
    Dim Reference As String
    Dim Job As String
    Dim Spacer As String
    Dim Wording As String

    Spacer = " - "
    Wording = Spacer & "Status Update" & Spacer
    
'ETA Update, Show Times, Special Update

    If Target.Column = 3 And Target.Row = 14 Then
        If Target.Value = "ETA Update" Then
            Application.Rows("36:47").Select
            Application.Selection.EntireRow.Hidden = True
        ElseIf Target.Value = "Show Times" Then
            Application.Rows("36:47").Select
            Application.Selection.EntireRow.Hidden = False
        ElseIf Target.Value = "Special Update" Then
            Application.Rows("36:47").Select
            Application.Selection.EntireRow.Hidden = True
        End If
    End If

' Beginning of Subject Line Creation
    
    ST_1 = "Update" & Spacer & Operation & Spacer & Object & Spacer & Location & Spacer & Reference & Spacer & Job
    
    If Range(B5).Value = "ABC" Then
        ST_1 = Reference & Spacer & Object & Spacer & Location & IrvingWording & Job
    ElseIf Range(B5).Value = "123" Then
        ST_1 = Reference & Spacer & "Update" & Spacer & Operation & Spacer & Object & Spacer & Location & Spacer & Job
    ElseIf Range(B5).Value = "A1B2" Then
        If Range(B6).Value = "them" Then
            ST_1 = "Update" & Spacer & Operation & Spacer & Object & Spacer & Location & Spacer & Reference & " / " & Range(B6).Value & Spacer & Job
        End If
     End If
            
    Range(I26) = ST1

    
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

I haven't tested your code but from a quick look ..

Your ranges are not specified correctly. Example

If Range(B5).Value = "ABC" Then

should be

If Range("B5").Value = "ABC" Then

Also, I am not sure if it would be affecting this particular code but 'Object' is not a good variable name as that word has its own special meaning in the vba language. You will notice in your code in post 1 that it is coloured blue along with other vba 'special' words.

 

[Fluff]

Along with Peter's comments, you have declared these variables

   Dim Object As String
    Dim Operation As String
    Dim Location As String
    Dim Reference As String
    Dim Job As String

but you have not given them any value.