Dynamic msgbox base on blank cell

[virakbot]

I've designed excel file for showing pending task. I want msgbox dimension to fit with only rows which have blank cell on column "F", but msgbox dimension is fit with 100 as i in my code

Sub Pending()
Dim r As Long
Dim msg As String, i As Long, a As Variant
msg = ""
For i = 2 To 100
For Each a In Array("F")
If Range("E" & i) <> "" And Cells(i, a).Value = "" Then
msg = msg & Range("D" & i) & " " & Range("E" & i) & Cells(i, a).Value & vbTab
End If
Next a
msg = msg & vbCrLf
Next i

MsgBox msg

End Sub

 

[JLGWhiz]

For Each a In Array("F")

I am not sure what you are trying to do here, but this statement does nothing. I was expecting it to error but it did not even do that. If you intend to loop values in column F, then you need to defing the range you want to loop, eg.

Dim rng As Range, a As Range
Set rng = Range("F2":F100")
    For Each a In rng

 

[Fluff]

Hi & welcome to MrExcel.
Is this what you're after

Sub Pending()
Dim r As Long
Dim msg As String, i As Long, a As Variant
msg = ""
For i = 2 To 100
   If Range("E" & i) <> "" And Cells(i, 6).Value = "" Then
      msg = msg & Range("D" & i) & " " & Range("E" & i) & vbLf
   End If
Next i

MsgBox msg

End Sub

 

[virakbot]

Dear Fluff ,

It is working perfectly. thanks you very much...

 

[virakbot]

Dear JLGWhiz,

Thanks you very much. It is solved now.

 

[virakbot]

Dear Fluff ,

May you advise how to un-limit number of character in msgbox? I have tried below codes but msgbox can not show all texts. Thanks you in advance sir


Sub Pending()
Dim r As Long
Dim msg As String, i As Long, a As Variant
msg = ""
For i = 13 To 100000
If Range("F" & i) <> "" And Cells(i, 14).Value = "" Then
msg = msg & Range("E" & i) & " " & Range("F" & i) & " " & Range("G" & i) & " " & Range("K" & i) & vbLf
End If
Next i


MsgBox msg


End Sub

 

[Fluff]

You cannot "Un-Limit" a message box.
One option would be to look at using a textbox on a userform, but with the amount of data you are looking at, you may hit problems further problems.

 

[virakbot]

Dear Fluff ,

Thanks you very much

 

[Fluff]

You're welcome