Macro - Ignore Cells with a value in?

[revenge4ash89]

Hi there,

I'm currently using a macro to determine what information is missing. However, the macro I'm using is overwriting all values. Is there anyway I can get it to ignore cells that have a value in? I've attached an example spreadsheet with the code I'm using.

Below is the code I'm using:

Sub Dave()
 Dim ws As Worksheet
 Dim rng As Variant
 Dim LR As Long
        Application.ScreenUpdating = 0
        
            Set ws = Worksheets("Sheet1")
            LR = ws.UsedRange.Rows.Count
            rng = ws.Range("A2:T" & LR).Value
            
                With ws
                  .Cells(2, 2).Resize(LR).FormulaR1C1 = "=IF(RC[-1]<>"""",""Information Required"","""")"
                  .Cells(2, 3).Resize(LR).FormulaR1C1 = "=IF(RC[-2]<>"""",""Information Required"","""")"
                  .Cells(2, 4).Resize(LR).FormulaR1C1 = "=IF(RC[-3]<>"""",""Information Required"","""")"
                  .Cells(2, 5).Resize(LR).FormulaR1C1 = "=IF(RC[-4]<>"""",""Information Required"","""")"
                  .Cells(2, 6).Resize(LR).FormulaR1C1 = "=IF(RC[-5]<>"""",""Information Required"","""")"
                  .Cells(2, 7).Resize(LR).FormulaR1C1 = "=IF(RC[-6]<>"""",""Information Required"","""")"
                  .Cells(2, 8).Resize(LR).FormulaR1C1 = "=IF(RC[-7]<>"""",""Information Required"","""")"
                  .Cells(2, 9).Resize(LR).FormulaR1C1 = "=IF(RC[-8]<>"""",""Information Required"","""")"
                  .Cells(2, 10).Resize(LR).FormulaR1C1 = "=IF(RC[-9]<>"""",""Information Required"","""")"
                  .Cells(2, 11).Resize(LR).FormulaR1C1 = "=IF(RC[-10]<>"""",""Information Required"","""")"
                  .Cells(2, 12).Resize(LR).FormulaR1C1 = "=IF(RC[-11]<>"""",""Information Required"","""")"
                  .Cells(2, 13).Resize(LR).FormulaR1C1 = "=IF(RC[-12]<>"""",""Information Required"","""")"
                  .Cells(2, 14).Resize(LR).FormulaR1C1 = "=IF(RC[-13]<>"""",""Information Required"","""")"
                  .Cells(2, 15).Resize(LR).FormulaR1C1 = "=IF(RC[-14]<>"""",""Information Required"","""")"
                  .Cells(2, 16).Resize(LR).FormulaR1C1 = "=IF(RC[-15]<>"""",""Information Required"","""")"
                  .Cells(2, 17).Resize(LR).FormulaR1C1 = "=IF(RC[-16]<>"""",""Information Required"","""")"
                  .Cells(2, 18).Resize(LR).FormulaR1C1 = "=IF(RC[-17]<>"""",""Information Required"","""")"
                  .Cells(2, 19).Resize(LR).FormulaR1C1 = "=IF(RC[-18]<>"""",""Information Required"","""")"
                End With
            ws.Range("A2:T" & LR).Value = ws.Range("A2:T" & LR).Value
                    With ws.Range("D2:T" & LR).Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=$T$3:$T$8"
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .InputTitle = ""
                        .ErrorTitle = ""
                        .InputMessage = ""
                        .ErrorMessage = ""
                        .ShowInput = True
                        .ShowError = False
                    End With
        Application.ScreenUpdating = 1
End Sub

Thank you in advance for the help!

[wamp]

read the rules, then alter your post accordingly

[revenge4ash89]

Sorry, my brain is fully functioning today - Friday feeling I guess. I've corrected the post

[arlu1201]

You can use -

 if cells(2,2)<>"" then ....

While looking through your code, i find that you have repeated the same line over and over again and incremented by 1. Your code can be structured better if you explain your requirement...and we can help you out.

[revenge4ash89]

Hi Arlu,

I think I've over complicated it, but basically I just wanted it to post "information required" in column B - S if there is a value in column A. However, I don't want "information required" to overwrite a cell if it already has a value in.

Sorry if that's unclear, I'm very new to this programming language as you can tell.

Thanks for the help!

[arlu1201]

I changed your code to the below - Only the initial loop.

Sub Dave()
 Dim ws As Worksheet
 Dim rng As Variant
 Dim LR As Long
        Application.ScreenUpdating = 0
        
            Set ws = Worksheets("Sheet1")
            LR = ws.UsedRange.Rows.Count
            LC = ws.UsedRange.Columns.Count
            rng = ws.Range("A2:T" & LR).Value
                      
            With ws
                For i = 2 To LR
                    For j = 2 To LC - 3
                        If .Cells(i, j).Value = "" Then .Cells(i, j).FormulaR1C1 = "=If(RC[-1]<>"""",""Test"","""")"
                    Next j
                Next i
               
                End With
            ws.Range("A2:T" & LR).Value = ws.Range("A2:T" & LR).Value
                    With ws.Range("D2:T" & LR).Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=$T$3:$T$8"
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .InputTitle = ""
                        .ErrorTitle = ""
                        .InputMessage = ""
                        .ErrorMessage = ""
                        .ShowInput = True
                        .ShowError = False
                    End With
        Application.ScreenUpdating = 1
End Sub

[revenge4ash89]

Arlu that works absolutely perfectly!!! Thank you so much for the help, I can't tell you how much I appreciate it!