Help with Simple Excel Loop

[mixsingh]

Hi,

Could someone please provide some sample vba code in excel to do the
following:

Loop for n rows:
Check data in cell x
if cell x > 0 then copy contents of cell x and cell y to an array

Then have one string variable which has the contents of array. For
example,

1 Test
2 Test Two
0 Nothing
1 Test Four

Each number on the left hand side is in a cell - the text is also in a
cell next to the number.

So String would output:
1 Test
2 Test Two
1 Test Four

Any help will be much appreciated.

Thanks,

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim tmp As String

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, TEST_COLUMN).Value > 0 Then
tmp = tmp & .Cells(i, TEST_COLUMN).Value & " " & _
.Cells(i, TEST_COLUMN).Offset(0, 1).Value & vbNewLine
End If
Next i

End With

MsgBox tmp

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[mixsingh]

Superb! Thanks!

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim tmp As String

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, TEST_COLUMN).Value > 0 Then
tmp = tmp & .Cells(i, TEST_COLUMN).Value & " " & _
.Cells(i, TEST_COLUMN).Offset(0, 1).Value & vbNewLine
End If
Next i

End With

MsgBox tmp

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)