VBA to display predefined value automatically

[David_8055]

Is there a way to write a vba code to automatically display certain text in column C (for eg: “2021a”) if column B contains any value (regardless of text or number). I can write this is excel formula by using isText and IsNumber commands with IF and OR clause. Can some VBA experts give me a solution please? So basically what I want to display at C1 is “Data” C2, C3, C4,….. is “2021a” Regardless of the value at B.

Also how can this command run automatically every time I open this spreadsheet?

 

[hrayani]

Hi David,

is this what you want

Private Sub Workbook_Open()

Dim rng As Range, cell As Range
Set rng = Range("C2:C10")

For Each cell In rng

If cell.Offset(, -1) <> "" Then
cell.Value = "2021a"

    Else
    cell.ClearContents

End If
    Next cell

End Sub

 

[David_8055]

Hi Hrayani,
Thanks much. It works.
Just 1 question. How will I run it automatically every time I open my spreadsheet?

 

[David_8055]

Hi Hrayani,
Thanks much. It works.
Just 1 question. How will I run it automatically every time I open my spreadsheet?

I figured out and problm solved. thank you

 

[bebo021999]

Use worksheet_activate event
Using specialcells may help code run faster, in case you data expands to thousand of rows.

Option Explicit
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Range("B1:B100000").Copy ' adjust to actual possible range
Range("C1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Selection.SpecialCells(xlCellTypeConstants).Value = "2021a"
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[hrayani]

I figured out and problm solved. thank you

You are welcome