CODEWITHSUNDEEP
vbaexcel
ERKSMTY
ERKSMTY

Reputation: 135

End Command When First Empty Cell Found

This code is used to look through a list of values, and the code needs to end at the first blank row. Currently, this code causes an error when a blank cell is encountered. Not sure how to end command when it hits a blank cell. Any suggestions are much appreciated.

Sub PMN_Update()

 Dim Month As String
 Dim Year As String
 Dim File As String
 Dim FileRow As String
 Dim x As Variant


Month = Range("G2").Value
Year = Range("G3").Value

NumFiles = Application.CountA(Sheets("PMNs").Range("A2:A200"))

ChDir "L:\management tools\PROJECT\Prog_Fcst"


For x = 1 To NumFiles
    FileRow = "A" & 1 + x

    File = Range(FileRow).Text

    Workbooks.Open Filename:="XXXXXXXXXXXXXXX" & File, _
    UpdateLinks:=0
    Sheets("Initiation").Select
    Range("Q4").Select
    ActiveCell.FormulaR1C1 = Month
    Range("Q5").Select
    ActiveCell.FormulaR1C1 = Year

    Application.DisplayAlerts = False

    Application.Run "'" & File & "'!UpdateMaterial"
    Application.Run "'" & File & "'!UpdateAMRData"
    Application.DisplayAlerts = True
    'Application.Run UpdateMaterial
    'Application.Run UpdateAMRData

    ActiveWorkbook.Save
    ActiveWorkbook.Close
Next x

End Sub

Upvotes: 0

Views: 371

Answers (2)

M--
M--

Reputation: 28825

You have different options for handling this:

First method can be using While

  While Cells(x + 1, "A").Value <> "" 'this check if it is empty
   'YOUR CODE GOES HERE
  x = x + 1
  Wend

Second is using On Error:

You can either use this On Error Resume Next or On Error GoTo 99 and add a line after next before the End Sub as this: 99 Exit Sub

Third is using an if-statement as other users provided.

I personally like the first approach.

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

Just do an Exit Sub or an End when you encounter a blank cell:

For x = 1 To NumFiles
    If IsEmpty(Cells(x + 1, "A").Value) Then End

Or you could base your test on the value of File:

    File = Range(FileRow).Text
    If File = "" Then End

Upvotes: 2

Related Questions