simple excel vba macro

[Andrew Slentz]

Hi! I am trying to create a VBA macro which will copy the contents of
column B to column C if column B = "group" and column C is blank. I
would also like to insert "EMPTY" into column C only if column B is
blank and is not "group". I am lost on this. Any ideas???

Thanks!!!


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[mudraker]

Andrew

Please explain by what you mean by if column B = "group"

Does the word "group" appear in a particular cell, Is "group" a name
Range

 

[chris]

At what point does it need to stop? there can be a lot of blank cells in column B: 65000

----- Andrew Slentz wrote: ----

Hi! I am trying to create a VBA macro which will copy the contents o
column B to column C if column B = "group" and column C is blank.
would also like to insert "EMPTY" into column C only if column B i
blank and is not "group". I am lost on this. Any ideas??

Thanks!!


*** Sent via Developersdex http://www.developersdex.com **
Don't just participate in USENET...get rewarded for it

 

[Greg Wilson]

I responded to your post on May 8th on this subject,
although your request at that time wasn't exactly the
same. I repeat my May 8th post below. Did this not
work? If so, what was the problem?

***** Repeat of May 8th post *****

Assuming there arn't any gaps in the data then perhaps this
simplification:

Sub Group()
Dim C As Range, NumRows As Integer
Dim Rng1 As Range, Rng2 As Range
NumRows = Range("A65536").End(xlUp).Row
Set Rng1 = Range("D2:I" & NumRows)
Set Rng2 = Range("B2:B" & NumRows)
For Each C In Rng1
If LCase(Trim(Cells(C.Row, 2))) = "group member" Then
C.Value = C.Offset(-1).Value
End If
Next
For Each C In Rng2
If LCase(Trim(C.Value)) = "group" Then
C.EntireRow.Delete
End If
Next
Columns(2).EntireColumn.Delete
End Sub

Regards,
Greg

 

[Bob Phillips]

cLastRow = Celle(Rows.Count,"B").End(xlUp).Row
For i = 1 To cLastRow
If lcase(Cells(i,"B").Value) = "group" Then
If Cells(i,"C").Value = "" Then
Cells(i,"C").Value = "EMPTY")
Else
Cells(i,"C").Value = Cells(i,"B").Value
End If
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)