Fill Column with word of choice referencing keyword from worksheet name

[Neveidas]

Hello, I am trying to fill column A with either Lost(Breach) or Update(Breach) based on the name of the worksheet. One thing to note is that the worksheets are all of the same names, so they are all labelled as Lost (Breached)(Number) and Update(Breached)(Number) so I can't really fill the columns of all the worksheets that I need

Is there like a string or variable VBA that i can tie the keywords "Lost" and "Updates" to so that the code will just reference either one of these 2 keywords and fill Column A with the words that i want it to? And how do i go about coding it?

Thank you!

 

[My Aswer Is This]

You said: fill column A

Column A has More then 1 million cells. Do you really need the entire column filled?

 

[Neveidas]

You said: fill column A

Column A has More then 1 million cells. Do you really need the entire column filled?

Oh no, not the entirety of Column A, Its just until where the data ends

Private Sub Fill()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim i As Long
Dim r As Range

    For Each ws In ActiveWorkbook.Worksheets

        i = ws.Range("B" & Rows.Count).End(xlUp).Row
        Set r = ws.Range("A2:A" & i)
        
        Select Case ws.Name
            Case "Update(Pre)", "Update XB(Pre)": r.Value = "Update(Pre)"
            Case "Update(Breach)", "Update XB(Breach)": r.Value = "Update(Breach)"
            Case "Lost(Pre)", "Lost XB(Pre)": r.Value = "Lost(Pre)"
            Case "Lost(Breach)", "Lost XB(Breach)": r.Value = "Lost (Breach)"
        End Select
        
        
        

    Next ws
    
    Set ws = Nothing
    Set r = Nothing
    
    
Application.ScreenUpdating = True

End Sub

I had used this for a separate data set, but it seems almost similar so i am thinking if i could do it something like this, but with Keywords instead

 

[My Aswer Is This]

Oh no, not the entirety of Column A, Its just until where the data ends

If there is already data in column A you want it overwritten with:
Lost(Breach) Or Update(Breach)
Is that what your saying.

 

[Neveidas]

So

If there is already data in column A you want it overwritten with:
Lost(Breach) Or Update(Breach)
Is that what your saying.

Sorry for not clarifying,

Column A will always be empty until the data has been imported, my current idea was to Call a subroutine (Which would be this) to fill Column A to the last data row, with either "Lost (Breach)" or "Update (breach)" depending on the worksheet name after the import code is done

 

[My Aswer Is This]

Well here is a script that looks at the sheet names and will enter the value in the first 5 cells in column A with the value you wanted. Since I have no more info to work with as to how to enter data in column A if there is data already in column A you see if this will help you for now

Sub Instr_Lost()
'Modified  10/25/2021  4:13:33 AM  EDT
Application.ScreenUpdating = False
Dim i As Long

    For i = 1 To Sheets.Count
        If InStr(Sheets(i).Name, "Lost") > 0 Then Sheets(i).Cells(1, 1).Resize(5).Value = "Lost(Breach"
        If InStr(Sheets(i).Name, "Updates") > 0 Then Sheets(i).Cells(1, 1).Resize(5).Value = "Update(Breach)"
    Next
Application.ScreenUpdating = True
End Sub

 

[Neveidas]

Well here is a script that looks at the sheet names and will enter the value in the first 5 cells in column A with the value you wanted. Since I have no more info to work with as to how to enter data in column A if there is data already in column A you see if this will help you for now

Sub Instr_Lost()
'Modified  10/25/2021  4:13:33 AM  EDT
Application.ScreenUpdating = False
Dim i As Long

    For i = 1 To Sheets.Count
        If InStr(Sheets(i).Name, "Lost") > 0 Then Sheets(i).Cells(1, 1).Resize(5).Value = "Lost(Breach"
        If InStr(Sheets(i).Name, "Updates") > 0 Then Sheets(i).Cells(1, 1).Resize(5).Value = "Update(Breach)"
    Next
Application.ScreenUpdating = True
End Sub

Thank you for this!

There isn't any data in Column A at all, it is an empty Column

 

[My Aswer Is This]

Thank you for this!

There isn't any data in Column A at all, it is an empty Column

Well my script entered data into column A first 5 cells correct?

 

[Neveidas]

Well my script entered data into column A first 5 cells correct?

Yupp, it did, although i forgot to mention there is a Header too hahaha

Sub Instr_Lost()
'Modified  10/25/2021  4:13:33 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim r As Range
Dim ws As Worksheet


i = Range("B" & Rows.Count).End(xlUp).Row
Set r = Range("A2:A" & i)


    For Each ws In ActiveWorkbook.Worksheets
        If InStr(ws.Name, "Lost") > 0 Then ws.Range("A2:A" & i).Resize(i).Value = "Lost(Breach"
        If InStr(ws.Name, "Updates") > 0 Then ws.Range("A2:A" & i).Resize(i).Value = "Update(Breach)"
    Next ws
    
Application.ScreenUpdating = True
End Sub

I tried changing some things to fill to last row, it filled to last row on one worksheet, but the other worksheets are filled to the first worksheet instead

 

[My Aswer Is This]

So now you have written your own script by modifying my script because you do not like mine. And now you want me to modify your script??
What was wrong with my script?