Don't know how to loop this

[Alex Piotto]

Greetings!
I need to make a loop to copy the values of almost 300 rows (from D6 to D300) and 3 columns (A, B, C) from the ActiveSheet into a yesno message box. To give the user a chance, you know...
I really don't know where to start. Never really used loops before...

Here is my code to give you the idea...

Sub INSERIRE_ENTRATE()

Dim QUA6 As String
Dim UNITAPRO6 As String
QUA6 = ThisWorkbook.Sheets("ENTRATE").Range("D6").Value
UNITAPRO6 = " " + ThisWorkbook.Sheets("ENTRATE").Range("B6").Value + " " + ThisWorkbook.Sheets("ENTRATE").Range("A6").Value

Dim QUA7 As String
Dim UNITAPRO7 As String
QUA7 = ThisWorkbook.Sheets("ENTRATE").Range("D7").Value
UNITAPRO7 = " " + ThisWorkbook.Sheets("ENTRATE").Range("B7").Value + " " + ThisWorkbook.Sheets("ENTRATE").Range("A7").Value

Dim QUA8 As String
Dim UNITAPRO8 As String
QUA8 = ThisWorkbook.Sheets("ENTRATE").Range("D8").Value
UNITAPRO8 = " " + ThisWorkbook.Sheets("ENTRATE").Range("B8").Value + " " + ThisWorkbook.Sheets("ENTRATE").Range("A8").Value

ILTUTTO = QUA6 + UNITAPRO6 + vbNewLine + QUA7 + UNITAPRO7 + vbNewLine + QUA8 + UNITAPRO8 + vbNewLine


'LOOOOOOOOPPPPPPP ?????? HOW? 300 rows down!!!


Dim answer As Integer
answer = MsgBox("TUTTO GIUSTO? SICURO? RICONTROLLA..." + vbNewLine + vbNewLine + ILTUTTO, vbYesNo)
 
If answer = vbNo Then

Exit Sub

Else

'DO SOME STUFF

ThisWorkbook.Sheets("DETTAGLI-ENTRATE").Protect AllowFiltering:=True

ActiveSheet.EnableSelection = xlNoSelection

Application.CutCopyMode = False

Application.ScreenUpdating = True

End If

End Sub

The code works... but obviously for 3 rows only...

And I am unable to put the values of the 3 columns together as one because I get a Mismatch error...

 

[Rick Rothstein]

I don't see how this would work?
If you are combining strings, you need to use "&" not "+"

The + sign does the same thing as the & sign when the two items are text Strings. Because of this, you have to be very careful if you are using TextBoxes to receive user entered numbers with the intention to do math with them. For example, put two TextBoxes and a CommandButton on a UserForm. Put this code in the CommandButtons click event...

MsgBox TextBox1.Value + TextBox2.Value

Since the user is entering numbers into both TextBoxes, you would expect the MessageBox to display their sum. It doesn't. Since TextBoxes return text (Strings), the plus sign will concatenate the two numbers, not add them.

 

[davesexcel]

Ah, I thought you wanted to omit D if it was blank.

If D is blank then it will go to the next cell.

Sub Button1_Click()
    Dim sh As Worksheet
    Dim s As String
    Dim spac As String
    Dim LstRw As Long, x As Long
    
    spac = vbNewLine
    
    Set sh = Sheets("ENTRATE")
    
    With sh
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        For x = 6 To LstRw
            If .Cells(x, 4).Value <> "" Then
            s = s & .Cells(x, 4).Value & " " & .Cells(x, 2) & " " & .Cells(x, 1).Value & spac
                End If
        Next x
    
    End With
    
    MsgBox s
    
End Sub

 

[Alex Piotto]

Thanks a lot davesexcel. All is working now.

 

[Rick Rothstein]

Sub Button1_Click()
    Dim sh As Worksheet
    Dim s As String
    Dim spac As String
    Dim LstRw As Long, x As Long
  
    spac = vbNewLine
  
    Set sh = Sheets("ENTRATE")
  
    With sh
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
      
        For x = 6 To LstRw
            If .Cells(x, 4).Value <> "" Then
            s = s & .Cells(x, 4).Value & " " & .Cells(x, 2) & " " & .Cells(x, 1).Value & spac
                End If
        Next x
  
    End With
  
    MsgBox s
  
End Sub

Some comments on your code...

1) The vbNewLine constant evaluates to two characters on a PC, a Carriage Return followed by a Line Feed (it used evaluate to a single Carriage Return character on old Macs, but I am not sure if that is still the case any more). Usually, "new lines" within Excel only need a single Line Feed character (the vbLf constant evaluates to this).

2) The line of code I highlighted in red in your code above will guarantee the text in the "s" variable ends with a vbNewLine. I am not sure if that will matter to the OP or not, but I thought I should point it out. To get rid of the trailing vbNewLine, you would need this additional line of code...

s = Left(s, Len(s) - Len(vbNewLine))

 

[Norie]

In VBA you should always use & as the concatentate operator, + will work sometimes but not always.

P.S. This is not a regional issue/setting.