Help with a VBA Loop please

K

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,291
Office Version
  1. 365
Platform
  1. Windows
I am trying to create a message box that thats shows the contents of a row.

A1 = the customer number, refered to as Loc

B1 is a date
C1 is text
D1 is a date
E1 is text repeat on and on....

I want to display in a message box.
B1: C1
D1: E1

until there are is no date value entered.

Without a loop / null value test I have got it working with.

Code: 
 comment1 = Loc.Offset(0, 1) & ": " & Loc.Offset(0, 2)
 comment2 = Loc.Offset(0, 3) & ": " & Loc.Offset(0, 4)
 comment3 = Loc.Offset(0, 5) & ": " & Loc.Offset(0, 6)
 comment4 = Loc.Offset(0, 7) & ": " & Loc.Offset(0, 8)
 MsgBox comment1 & vbCrLf & comment2 & vbCrLf & comment3 & vbCrLf & comment4

but obvously this only shows 4 comments - or if expanded gives a trail of
:
:
:


Thanks in advance.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Try this:
Code: 
Dim Loc As Range
Dim comment1 As String
Dim Limit As Long, c As Long
Set Loc = Cells(1, 1)
Limit = Cells(Loc.Row, Columns.Count).End(xlToLeft).Column
For c = 2 To Limit Step 2
    If c = 2 Then
        comment1 = Loc.Offset(0, c - 1) & ": " & Loc.Offset(0, c)
        Else
            comment1 = comment1 & vbCrLf & Loc.Offset(0, c - 1) & ": " & Loc.Offset(0, c)
    End If
Next c
MsgBox comment1
 
Upvote 0
Code: 
Sub View_Comments()
     Dim Loc As Range
 
cust = ActiveCell
Set Loc = Sheets("comments").Range("a1:a10000").Find(cust)
If Loc Is Nothing Then
 MsgBox "No Comments", vbInformation, "!###!"
 Exit Sub
 End If
 
 
Dim comment1 As String
Dim Limit As Long, c As Long
Limit = Cells(Loc.Row, Columns.Count).End(xlToLeft).Column
For c = 2 To Limit Step 2
    If c = 2 Then
        comment1 = Loc.Offset(0, c - 1) & ": " & Loc.Offset(0, c)
        Else
            comment1 = comment1 & vbCrLf & Loc.Offset(0, c - 1) & ": " & Loc.Offset(0, c)
    End If
Next c
MsgBox comment1
 
 
End Sub


This hangs up on a maximum of 3 comments? it also trails :'s if there are less comments?


I changed the loc = cell(1,1) to my

Set Loc = Sheets("comments").Range("a1:a10000").Find(cust)

Is this OK?
 
Last edited:
Upvote 0
You may have a sheet reference issue. This small tweak works for me:
Code: 
Sub View_Comments()
   Dim Loc As Range, wks As Worksheet
   Dim comment1 As String
   Dim Limit As Long, c As Long
   
   Set wks = Sheets("comments")
   cust = ActiveCell.Value
   Set Loc = wks.Range("a1:a10000").Find(cust)
   If Loc Is Nothing Then
      MsgBox "No Comments", vbInformation, "!###!"
      Exit Sub
   End If
    
    
   Limit = wks.Cells(Loc.Row, wks.Columns.Count).End(xlToLeft).Column
   For c = 2 To Limit Step 2
      If c = 2 Then
         comment1 = Loc.Offset(0, c - 1) & ": " & Loc.Offset(0, c)
      Else
         comment1 = comment1 & vbCrLf & Loc.Offset(0, c - 1) & ": " & Loc.Offset(0, c)
      End If
   Next c
   MsgBox comment1
End Sub
 
Upvote 0
It doesn't stop at the 3rd line now but it does have a load of extra :'s

So many I can't see the bottom of the message box.
 
Upvote 0
That suggests to me that you have some cells that you think are blank that Excel doesn't. Do you (or did you) have any formulas returning "" in those cells (or did you write "" to them in code)?
 
Upvote 0
Yes, but I have no idea why.


Thanks for your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Subroutine to Add Cell Comments Not Updating the Contents Correctly
Replies
7
Views
617
CephasOz
CephasOz
C
  • Solved
How can I do this lookup?
Replies
5
Views
487
Fluff
Fluff
PuntingJawa
  • Solved
Nested formula with multiple criteria
Replies
7
Views
442
PuntingJawa
PuntingJawa
M
  • Solved
Help to exit VBA upon cancel on input box
Replies
3
Views
472
Domenic
D
M
  • Question Question
vba help - split value and add to dictionary items
Replies
3
Views
3K
Fluff
Fluff

Forum statistics

Threads
1,222,675
Messages
6,167,534
Members
452,118
Latest member
djjamesp

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top