Using Cells property to represent the active range

[bearcub]

I have the following VBA Code Snippet and I've trying to learn how to use the Cells object.

How would I replace the Activecell object with the Cells object in the Do Loop?

In this instance, the Active cell would be Cells(2,2).

What confuses me is how to replace the activecell object with the cells object in a Do while loop as opposed to a For next loop.

    Range("B2").Select

    'While the activecell isn't blank...

    Do While ActiveCell.Value <> ""
        
        'The logical test...
        'If the value of the activecell is "Male", AND column E DOES NOT EQUAL "Brown"...

        If ActiveCell.Value = "Male" And ActiveCell.Offset(0, 3).Value <> "Brown" Then
            
            '...then write "It's a brown haired MAN!" in column G

            ActiveCell.Offset(0, 5).Value = "It's a MAN...but he DOESN't have brown hair!"
        

        End If
    
    'Move down a cell
    ActiveCell.Offset(1, 0).Select

   Loop

Thank you for your help in advance

 

[mikerickson]

Look at this structure. It doesn't use the .Cells property, but it avoids Selection and use of ActiveCell

Dim oneCell as Range

Set oneCell = Range("B2")

Do while oneCell.Value <> ""
    If oneCell.Value = "Male" And OneCell.Offset(0,1).Value <> "Brown" Then
        oneCell.Offset(0,5).Value = "Its a MAN...but he Doesn't Have Brown hair"
    End IF
    Set oneCell = oneCell.Offset(1,0)
Loop

 

[Fluff]

How about

Sub bearcub()
   Dim i As Long
   
   Do While Cells(2, 2).Offset(i) <> ""
      With Cells(2, 2)
         If .Offset(i).Value = "Male" And .Offset(i, 3).Value <> "Brown" Then
            .Offset(i, 5).Value = "It's a MAN...but he DOESN't have brown hair!"
         End If
      End With
      i = i + 1
   Loop
End Sub

 

[bearcub]

Very interesting, they both work, thank you very much, Mike and Fluff

Fluff,
In you code, how does the row increment - is it the i variable in the Offset property (the Offset(i) piece - i represents the row?

Thank you both again for the tips, great stuff!

I take it then that using the Cells property in this instance isn't the best approach?

 

[DanteAmor]

Try this with for

Sub test1()
    For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
        If Cells(i, "B").Value = "Male" And Cells(i, "E").Value <> "Brown" Then
            Cells(i, "G").Value = "It's a MAN...but he DOESN't have brown hair!"
        End If
    Next
End Sub

Try this too without loop

Sub test()
    With Range("G2:G" & Range("B" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=IF(AND(RC[-5]=""Male"",RC[-2]<>""Brown""),""It's a MAN...but he DOESN't have brown hair!"","""")"
        .Value = .Value
    End With
End Sub

 

[Rick Rothstein]

Try this too without loop

Sub test()
    With Range("G2:G" & Range("B" & Rows.Count).End(xlUp).Row)
        .FormulaR1C1 = "=IF(AND(RC[-5]=""Male"",RC[-2]<>""Brown""),""It's a MAN...but he DOESN't have brown hair!"","""")"
        .Value = .Value
    End With
End Sub

Another way without a loop...

[table="width: 500"]
[tr]
	[td]Sub MalesWithoutBrownHair()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  Range("G2:G" & LastRow) = Evaluate(Replace("IF((B2:B#=""Male"")*(E2:E#<>""Brown""),""It's a MAN... but he DOESN'T have brown hare!"","""")", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[bearcub]

Thank you Rich and Dante. How do these work with using a loop? How does the code go from the end of the sheet all the way up to the second row?

Really cool and mind blowing!

Michael

 

[DanteAmor]

Your welcome

What this line does is to cover the range from G2 to G(last row with data) and in that range it puts the formula with the conditions.

[COLOR=#0000ff]With Range("G2:G" & Range("B" & Rows.Count).End(xlUp).Row)[/COLOR]

Then put in that same range of cells the resulting value of the formula

[COLOR=#0000ff].Value = .Value[/COLOR]

 

[bearcub]

Thank you for the explanation. I was a little confused by the concatenation but once I looked at it closely I say that you were referencing the last cell on the spreadsheet.

Michael

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.