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

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

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

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

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

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
Commented:
Hi Rob, I misread the question. Should be this, it might need some modification based on data layout.

Sub AddText()
    Dim rRng As Range, rCl As Range
    On Error Resume Next
    Set rRng = ActiveSheet.Columns(5).SpecialCells(xlCellTypeConstants)
    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


I would usually suggest a formula or Conditional Formatting approach though.
Rob HensonFinance Analyst
Commented:
Alternative that can be run from any sheet, ie not just the relevant sheet selected.

Sub AutoPopulate()

Dim LR As Integer

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

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

End Sub

Open in new window


I would usually suggest a formula or Conditional Formatting approach though.
Totally agree, hence my first suggestion using AutoFilter.
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

Commented:
Pleased to help
Rob HensonFinance Analyst

Commented:
Glad to help as always.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial