conditional statements
 

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

conditional statements
 

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

NickHK

"flyingmeatball"
<flyingmeatball.2cwdqg_1156176311.8642@excelforu m-nospam.com ¼¶¼g©ó¶l¥ó·s»D:flyingmeatball.2cwdqg_1156176311.86 ...

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

conditional statements
 

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

conditional statements
 

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

conditional statements
 

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"
<flyingmeatball.2cwgqs_1156180211.7315@excelforu m-nospam.com wrote in
message news:flyingmeatball.2cwgqs_1156180211.7315@excelfo rum-nospam.com...

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

conditional statements
 

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