Avatar of Andreas Hermle
Andreas Hermle
Flag for Germany asked on

Search for non-blank cells in Column E and if found enter a certain term in Column F on the same row.

Dear Experts:

On a worksheet named 'custom_sets' of the active workbook I would like to perform the following action using VBA.

Check for cell entries in Column E. If cell entries are found (i.e. Cell <> "") then enter the term 'Quantities chosen' in Column F on the same row.

Thank you very much in advance. Help is very much appreciated. Thank you very much in advance.

Regards, Andreas
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Rob Henson

Can be easily achieved without VBA.

Select the header row of the data and apply an AutoFilter. Using the dropdown on header of column E deselect the "Blanks" option, this will then leave the non-blanks visible. Enter the required value in the first visible row of column F and double click the bottom right corner of that cell, this will do a Fill down to the last visible row.

Remove the Autofilter and all rows will then be visible; the fill down will have affected only the visible rows.
Roy Cox

Perhaps this

Sub AddText()
    Dim rRng As Range, rCl As Range
    On Error Resume Next
    Set rRng = ActiveSheet.Columns(5).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rRng Is Nothing Then
        For Each rCl In rRng.Cells
            rCl.Offset(, 1).Value = "Quantities chosen"
        Next
    End If
End Sub

Open in new window

Rob Henson

@Roy - Somewhat confused, wouldn't that put value against the blank rows???? Or is that taken care of by the double negative on line 6?

Requirement is to populate against non-blank rows.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rob Henson

Alternative if really want VBA

Sub AutoPopulate()

Dim LR As Integer

LR = Range("E1048576").End(xlUp).Row

For Each Cell In Range("E2:E" & LR)
    If Cell.Value <> "" Then Cell.Offset(, 1).Value = "Quantities chosen"
Next Cell

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Roy Cox

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Rob Henson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Andreas Hermle

ASKER
dear both, thank you very much for the overwhelming support. Will test the solutions tomorrow. Thank you very much indeed
Andreas Hermle

ASKER
Great job from both of you. Different approaches, both work just fine. I really highly appreciate your professional support. It couldn't be better.

REgards, Andreas
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Roy Cox

Pleased to help
Rob Henson

Glad to help as always.