blanket "if null" statement

synergy16

Active Member
Joined
Mar 17, 2016
Messages
405
good morning all. after a bunch of calculations i narrow down a single row based on user choice and populate textboxes with related data. some of the cells are blank tho. is there a way to, perhaps with a loop, to tell the program if any of those cell locations are null to put a "N/A" in the textbox or do i have to a if/then statement for each one?


Code:
 serialNumTB = Cells(stripPartRow, 7)    partQtyTB = Cells(stripPartRow, 5)
    partRevTB = Cells(stripPartRow, 4)
    lotBatchTB = Cells(stripPartRow, 6)
    jobProOrderTB = Cells(stripPartRow, 8)
    supplierTB = Cells(stripPartRow, 9)
    shipOutDateTB = Cells(stripPartRow, 10)
    origPoDate = Cells(stripPartRow, 11)
    expDockDateTB = Cells(stripPartRow, 12)
    customerTB = Cells(stripPartRow, 2)
    relatedPrtTB = Cells(stripPartRow, 13)
    notesTB = Cells(stripPartRow, 14)
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,329
Office Version
  1. 365
Platform
  1. Windows
If the textboxes are on a userform try
Code:
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("serialNumTB", 7, "partQtyTB", 5, "partRevTB", 4)
   For i = 0 To UBound(Ary) Step 2
      If Cells(stripPartRow, Ary(i + 1)) = "" Then
         Me.Controls(Ary(i)) = "N/A"
      Else
         Me.Controls(Ary(i)) = Cells(stripPartRow, Ary(i + 1))
      End If
   Next i
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,177
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top