conditional statements

[flyingmeatball]

Hi, I'm trying to use a macro to edit a column of data, but I'm quite unfamiliar with the syntax of VB. in cell A1 i have the number of people in a sample group. The Length of column B is the random number stored in A1 (the number of people in the group) I want to go through Column B and delete any number that is below 40 and above 80. This is how I want the loop to look, if it were written in Java

Int Cellnum = 1
While {(Cellnum < CellA1)
If (Cellnum >80)
clear cellnum
If (Cellnum <40)
clear Cellnum)
Cellnum+=1
}

Thanks in advance

[NickHK]

First, record a macro of some these actions: Tools>Macro>Record new macro.
The code will give an idea of the syntax to strat with.

NickHK

"flyingmeatball"
<[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
>
> Hi, I'm trying to use a macro to edit a column of data, but I'm quite
> unfamiliar with the syntax of VB. in cell A1 i have the number of
> people in a sample group. The Length of column B is the random number
> stored in A1 (the number of people in the group) I want to go through
> Column B and delete any number that is below 40 and above 80. This is
> how I want the loop to look, if it were written in Java
>
> Int Cellnum = 1
> While {(Cellnum < CellA1)
> If (Cellnum >80)
> clear cellnum
> If (Cellnum <40)
> clear Cellnum)
> Cellnum+=1
> }
>
> Thanks in advance
>
>
> --
> flyingmeatball
> ------------------------------------------------------------------------
> flyingmeatball's Profile:
> http://www.excelforum.com/member.php...o&userid=37302
> View this thread: http://www.excelforum.com/showthread...hreadid=573816
>

[flyingmeatball]

I already have a long and complicated macro, and i'm trying to insert this in the middle of it. The problem with this section of code is it is all conditional, and I don't know how to refer to cells in the abstract in VB. (EG: i want to select Column B, Cell1, run comparison, then go to Cell2, Cell3, ....CellX)

[Die_Another_Day]

Dim Cell as Range
For Each Cell in Range("B1",Range("A1").Address)
If Cell < 40 Or Cell > 80 Then Cell.ClearContents
Next

HTH

Charles

flyingmeatball wrote:
> I already have a long and complicated macro, and i'm trying to insert
> this in the middle of it. The problem with this section of code is it
> is all conditional, and I don't know how to refer to cells in the
> abstract in VB. (EG: i want to select Column B, Cell1, run comparison,
> then go to Cell2, Cell3, ....CellX)
>
>
> --
> flyingmeatball
> ------------------------------------------------------------------------
> flyingmeatball's Profile: http://www.excelforum.com/member.php...o&userid=37302
> View this thread: http://www.excelforum.com/showthread...hreadid=573816

[Bob Phillips]

Dim iLastRow As Long
Dim i As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "B").Value < 40 Or Cells(i, "B").Value > 80 Then
If rng Is Nothing Then
Set rng = Cells(i, "B")
Else
Set rng = Union(rng, Cells(i, "B"))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"flyingmeatball"
<[email protected]> wrote in
message news:[email protected]...
>
> I already have a long and complicated macro, and i'm trying to insert
> this in the middle of it. The problem with this section of code is it
> is all conditional, and I don't know how to refer to cells in the
> abstract in VB. (EG: i want to select Column B, Cell1, run comparison,
> then go to Cell2, Cell3, ....CellX)
>
>
> --
> flyingmeatball
> ------------------------------------------------------------------------
> flyingmeatball's Profile:
http://www.excelforum.com/member.php...o&userid=37302
> View this thread: http://www.excelforum.com/showthread...hreadid=573816
>

[Die_Another_Day]

Range("A1").Address Should be Range("A1").Text. Or if you want
determine the used rows programmatically then it should be Range("B" &
Rows.Count).End(xlUp)

Charles

Die_Another_Day wrote:
> Dim Cell as Range
> For Each Cell in Range("B1",Range("A1").Address)
> If Cell < 40 Or Cell > 80 Then Cell.ClearContents
> Next
>
> HTH
>
> Charles
>
> flyingmeatball wrote:
> > I already have a long and complicated macro, and i'm trying to insert
> > this in the middle of it. The problem with this section of code is it
> > is all conditional, and I don't know how to refer to cells in the
> > abstract in VB. (EG: i want to select Column B, Cell1, run comparison,
> > then go to Cell2, Cell3, ....CellX)
> >
> >
> > --
> > flyingmeatball
> > ------------------------------------------------------------------------
> > flyingmeatball's Profile: http://www.excelforum.com/member.php...o&userid=37302
> > View this thread: http://www.excelforum.com/showthread...hreadid=573816