Concatenate Specific Cell Data with Excel VBA

Question

I want to have a MsgBox pop up with a list of names that have missing info (either phone number or address). So far I have:

Dim missing As String
Do While Cells(i, 1).Value <> ""
    If StrComp(Cells(i, 1), "Need address") = 0 Then missing = Cells(i - 2, 1)
Loop
MsgBox missing

The spreadsheet is formatted to have all client info in Column A in the order of:

Name

Phone Number

Address

So far, I just a 1004 Runtime Error

Whats is the value of i?

Peter
– Peter

2015-06-22 11:50:08 +00:00
Commented Jun 22, 2015 at 11:50 — Peter
– Peter, Commented Jun 22, 2015 at 11:50

Gary's Student · Accepted Answer · 2015-06-22 11:53:36Z

1

You must initialize and increment i

Sub dural()
    Dim missing As String, i As Long
    i = 1

    Do While Cells(i, 1).Value <> ""
        If StrComp(Cells(i, 1), "Need address") = 0 Then missing = Cells(i - 2, 1)
        i = i + 1
    Loop
    MsgBox missing
End Sub

without initializing i, it starts at 0 and that is the source of the error.

answered Jun 22, 2015 at 11:53

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Indole · Accepted Answer · 2015-06-22 11:53:03Z

0

Where is 'i' starting? because (i - 2) can be -2 which is not a row # in excel.

answered Jun 22, 2015 at 11:53

Indole

451 gold badge1 silver badge6 bronze badges

Collectives™ on Stack Overflow

Concatenate Specific Cell Data with Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related