Help with Simple Excel Loop
 

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,

Help with Simple Excel Loop
 

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)



wrote in message
ps.com...
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,

Help with Simple Excel Loop
 

Superb! Thanks!

On 30 Apr, 12:50, "Bob Phillips" wrote:
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)

wrote in message

ps.com...

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,