select cells that correspond to a value in other cells
 

I work with a number of spreadsheets with one column (E) with the value
B or C nothing else. The sheet is sorted alphabetically by that
column. I want to select all the cells in column G that correspond
only to those cells that have the value of B in column E, and cut and
paste the values into column F which is blank. Each spreadsheet has
different #'s of rows with the value B


--
Paulg
------------------------------------------------------------------------
Paulg's Profile: http://www.excelforum.com/member.php...o&userid=36077
View this thread: http://www.excelforum.com/showthread...hreadid=567579

select cells that correspond to a value in other cells
 

You don't need VBA for this. You can enter =IF(E1="B",G1,"") in F1 and
copy down through all rows of data, then copy & paste in place as values. If
you wanted a macro version, here is one:

Sub AAAAAA()
Dim c As Range, LastRow As Long
LastRow& = Range("E" & Rows.Count).End(xlUp).Row
Range("F1:F" & LastRow&).Select
For Each c In Selection
c.Value = CheckColE(c)
Next c
End Sub

Function CheckColE(Rng As Range) As Variant
If Rng.Offset(0, -1).Value = "B" Then
CheckColE = Rng.Offset(0, 1).Value
Else
CheckColE = vbNullString
End If
End Function

Hope this helps,

Hutch

"Paulg" wrote:


I work with a number of spreadsheets with one column (E) with the value
B or C nothing else. The sheet is sorted alphabetically by that
column. I want to select all the cells in column G that correspond
only to those cells that have the value of B in column E, and cut and
paste the values into column F which is blank. Each spreadsheet has
different #'s of rows with the value B


--
Paulg
------------------------------------------------------------------------
Paulg's Profile: http://www.excelforum.com/member.php...o&userid=36077
View this thread: http://www.excelforum.com/showthread...hreadid=567579