We help IT Professionals succeed at work.

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

118 Views
Last Modified: 2019-03-22
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
Comment
Watch Question

Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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 CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
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 HensonFinance Analyst
CERTIFIED EXPERT

Commented:
@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.
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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

Group Finance Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Andreas HermleTeam leader

Author

Commented:
dear both, thank you very much for the overwhelming support. Will test the solutions tomorrow. Thank you very much indeed
Andreas HermleTeam leader

Author

Commented:
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
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Pleased to help
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
Glad to help as always.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions