Suggestions on parsing from Excel

I need to write a simple macro that will do the following:

1) Figure out if there are any non-empty cells in a row that are in
column C or higher.
) If there are values, then get the value of the first non-empty cell
of the current row, starting with column C and then moving right.
2) Get the value of the second non-empty cell of the current row

Some rows may have no values (which is why I want to check if there are
any non-empty cells before I start trying to find the first and then
second non-empty values). Actually, there may be cases where there is
only a first value and not a second.

I can visualize how to say this in VB, put I don't know enough of
Excel's objects and functions to know how to best acheive this. Any
help would be much appreciated!

Hi Morau,

You have not indicated whar you wish to do with the found values. The
following code ppoulates columns A and B with the found values:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range, rng2 As Range
Dim rCell As Range
Dim aCell As Range
Dim i As Long, j As Long

Set SH = ActiveSheet '<<==== CHANGE
Set rng = SH.Range("A1:A100") '<<==== CHANGE

i = Columns.Count - 2
For Each rCell In rng.Cells
j = 0
Set rng2 = rCell.Offset(0, 2).Resize(1, i)
On Error Resume Next
rCell.Select
Set rng2 = rCell.Offset(0, 2).Resize(1, i). _
SpecialCells(xlConstants)
For Each aCell In rng2.Cells
rCell.Offset(0, j).Value = aCell.Value
j = j + 1
If j = 2 Then Exit For
Next aCell
On Error GoTo 0
Next rCell

End Sub
'<<=============


---
Regards,
Norman


<[email protected]> wrote in message
news:[email protected]...
>I need to write a simple macro that will do the following:
>
> 1) Figure out if there are any non-empty cells in a row that are in
> column C or higher.
> ) If there are values, then get the value of the first non-empty cell
> of the current row, starting with column C and then moving right.
> 2) Get the value of the second non-empty cell of the current row
>
> Some rows may have no values (which is why I want to check if there are
> any non-empty cells before I start trying to find the first and then
> second non-empty values). Actually, there may be cases where there is
> only a first value and not a second.
>
> I can visualize how to say this in VB, put I don't know enough of
> Excel's objects and functions to know how to best acheive this. Any
> help would be much appreciated!
>

> You have not indicated whar you wish to do with the found values. The
> following code ppoulates columns A and B with the found values:

==>

You have not indicated what you wish to do with the found values. The
following code populates columns A and B with the found values:


---
Regards,
Norman

Hi Morau.

Delete:

> rCell.Select

This line should have been removed after testing.


---
Regards,
Norman

Another possibility:

Sub Get_Values()
Dim row As Integer, col As Integer
Dim val(2) As Variant
Dim is_empty As Boolean
For row = 1 To 100 ' or however many rows you are checking
val(1) = ""
val(2) = ""
is_empty = True
For col = 3 To 5 ' or however many columns you are checking
If Cells(row, col).Value <> "" Then
is_empty = False
If val(1) = "" Then
val(1) = Cells(row, col).Value
Else
val(2) = Cells(row, col).Value
End If
End If
Next col
If Empty = False Then
Call do_something_with_the_values(ByVal val(1), val(2))
End If
Next row
End Sub

<[email protected]> wrote in message
news:[email protected]...
>I need to write a simple macro that will do the following:
>
> 1) Figure out if there are any non-empty cells in a row that are in
> column C or higher.
> ) If there are values, then get the value of the first non-empty cell
> of the current row, starting with column C and then moving right.
> 2) Get the value of the second non-empty cell of the current row
>
> Some rows may have no values (which is why I want to check if there are
> any non-empty cells before I start trying to find the first and then
> second non-empty values). Actually, there may be cases where there is
> only a first value and not a second.
>
> I can visualize how to say this in VB, put I don't know enough of
> Excel's objects and functions to know how to best acheive this. Any
> help would be much appreciated!
>