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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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
@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.
Requirement is to populate against non-blank rows.
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
Hi Rob, I misread the question. Should be this, it might need some modification based on data layout.
I would usually suggest a formula or Conditional Formatting approach though.
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
I would usually suggest a formula or Conditional Formatting approach though.
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
I would usually suggest a formula or Conditional Formatting approach though.Totally agree, hence my first suggestion using AutoFilter.
dear both, thank you very much for the overwhelming support. Will test the solutions tomorrow. Thank you very much indeed
Premium Content
You need an Expert Office subscription to comment.Start Free Trial