Hi there,
This is the first time I have posted on this forum and I would like t
see if anyone can help me with a problem I am having.

I have a stream of values generated by some software that is saved as
.csv file. Each file has several sets of data in this one long list an
I wish to format it differently for ease of viewing and analysis.

Now this is what I have so far:

Columns("A:A").Select
For Each cell In Selection
If cell.Value = "Nr of samples:" Then
-*.Activate*-
ActiveCell.Offset(1, 0).Select
If Selection.Value = "0" Then
MsgBox "This cell has 0"
End If
If Selection.Value = "201" Then
MsgBox "This cell has 201"
End If
End If
Next
MsgBox "Finished looking at all cells"
End Sub

The formatting is easy to do (consists of a few cuts, offsets an
pastes) and I have already written that part (which is going to replac
the MsgBoxs which I have included just for my debugging purposes).
Likewise I am happy with plotting the graphs.

Now, I have the column selected, and I wish to search the column fo
said values (a parameter created by the third party software "Nr. o
samples:"). If these are found I want the cell to move down one fro
that cell (the one being checked), not the current active cell whic
seems to be the top of the range I selected (in this case A1). I kno
that the bold .Activate is the incorrect syntax but I put that in ther
to illustrate what I think I need to achieve (you may well have a bette
way to do it though).

I only have a couple of months self taught experience with VBA an
would greatly appreciate any comments from someone more experience
that may get me going along the right track again.

Anyways,
Thanks in advance

Dav

--
d.i.bar
-----------------------------------------------------------------------
d.i.barr's Profile: http://www.excelforum.com/member.php...fo&userid=1552
View this thread: http://www.excelforum.com/showthread.php?threadid=27092

In you code you say
"Nr of samples:"

In you narrative you say
"Nr. of samples:"

They are not the same, so just make sure you are checking for the correct
string.

But the modification I would suggest is:

For Each cell In Activesheet.UsedRange.columns(1).Cells
If cell.Value = "Nr of samples:" Then
cell.Activate
ActiveCell.Offset(1, 0).Select
If Selection.Value = "0" Then
MsgBox "This cell has 0"
End If
If Selection.Value = "201" Then
MsgBox "This cell has 201"
End If
End If
Next
MsgBox "Finished looking at all cells"
End Sub

since it is highly unlikely that you have data in all of column A, I would
suggest looping through the cells that are considered used without selecting
them. (of course it is better to avoid selecting all together, but the
above is a first step. The second might be:

For Each cell In Activesheet.UsedRange.columns(1).Cells
If cell.Value = "Nr of samples:" Then
If cell.offset(1, 0).Value = "0" Then
MsgBox "This cell has 0"
ElseIf cell.offset(1, 0).Value = "201" Then
MsgBox "This cell has 201"
End If
End If
Next
MsgBox "Finished looking at all cells"
End Sub

And the third (which would be faster) Assuming Nr of samples: only appears
once in column A.

Dim rng as Range
with Activesheet.UsedRange.columns(1)
set rng = .Find("Nr of samples:")
End With
if not rng is nothing then
if rng.offset(1,0).Value = "0" then
msgbox "This Cell has 0"
elseif cell.offset(1,0).Value = "201" then
msgbox "This Cell has 201"
end if
Else
Msgbox "Nr of samples: not found"
end if

--
Regards,
Tom Ogilvy




"d.i.barr" wrote in message
...

Hi there,
This is the first time I have posted on this forum and I would like to
see if anyone can help me with a problem I am having.

I have a stream of values generated by some software that is saved as a
csv file. Each file has several sets of data in this one long list and
I wish to format it differently for ease of viewing and analysis.

Now this is what I have so far:

Columns("A:A").Select
For Each cell In Selection
If cell.Value = "Nr of samples:" Then
-*.Activate*-
ActiveCell.Offset(1, 0).Select
If Selection.Value = "0" Then
MsgBox "This cell has 0"
End If
If Selection.Value = "201" Then
MsgBox "This cell has 201"
End If
End If
Next
MsgBox "Finished looking at all cells"
End Sub

The formatting is easy to do (consists of a few cuts, offsets and
pastes) and I have already written that part (which is going to replace
the MsgBoxs which I have included just for my debugging purposes).
Likewise I am happy with plotting the graphs.

Now, I have the column selected, and I wish to search the column for
said values (a parameter created by the third party software "Nr. of
samples:"). If these are found I want the cell to move down one from
that cell (the one being checked), not the current active cell which
seems to be the top of the range I selected (in this case A1). I know
that the bold .Activate is the incorrect syntax but I put that in there
to illustrate what I think I need to achieve (you may well have a better
way to do it though).

I only have a couple of months self taught experience with VBA and
would greatly appreciate any comments from someone more experienced
that may get me going along the right track again.

Anyways,
Thanks in advance

Dave


--
d.i.barr
------------------------------------------------------------------------
d.i.barr's Profile:
http://www.excelforum.com/member.php...o&userid=15521
View this thread: http://www.excelforum.com/showthread...hreadid=270920