loops and if statements
 

I am so new to excel and trying to learn vba on my own. I am very
experienced in lotus macros except vba is a whole new beast to me. I
know what I want to do, and have pieces already completed. This is the
next piece of the puzzle and I would appreciate anyone that can show me
the coding so I can learn from it.

Basically what I have left at this point is two columns of information.
The first column of information is relevant to a staff member. For
each staff member I require ONE line in an excel spreadsheet. The
information in column B must take up a cell to the right leaving only
ONE instance of the name in column A.

with the following used as an example, this is what is required after
the macro is run:

ADAMSCR CRAIG ADAMS ARB Internet_Access MRA_RO
ADMIN28 VAL_Read_Only
BENDD028 DARRYL BENDER VAL_Read_Only
BRAZEARO ROBERT BRAZEAU ARB COMMERCIAL INDUSTRIAL


Can anyone help??? Please?? :(

thank you... lakey

ADAMSCR CRAIG ADAMS
ADAMSCR ARB
ADAMSCR Internet_Access
ADAMSCR MRA_RO
ADAMSCR
ADMIN28 VAL_Read_Only
ADMIN28
BENDD028 DARRYL BENDER
BENDD028 VAL_Read_Only
BENDD028
BRAZEARO ROBERT BRAZEAU
BRAZEARO ARB
BRAZEARO COMMERCIAL
BRAZEARO INDUSTRIAL


--
lakey13
------------------------------------------------------------------------
lakey13's Profile: http://www.excelforum.com/member.php...o&userid=15997
View this thread: http://www.excelforum.com/showthread...hreadid=274643

loops and if statements
 

How about something like:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oCol As Long
Dim TopRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
'get rid of empty column B cells
On Error Resume Next
.Range("b:b").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
On Error GoTo 0

FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
TopRow = FirstRow
oCol = 2
For iRow = FirstRow To LastRow
If TopRow = iRow Then
'do nothing
Else
If LCase(.Cells(TopRow, "A").Value) _
= LCase(.Cells(iRow, "A").Value) Then
oCol = oCol + 1
.Cells(TopRow, oCol).Value = .Cells(iRow, "B").Value
.Cells(iRow, "B").ClearContents
Else
oCol = 2
TopRow = iRow
End If
End If
Next iRow

On Error Resume Next
.Range("b:b").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
On Error GoTo 0
End With
End Sub

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/02/2004 by Dave Peterson
'

'
Range("B11").Select
Application.CutCopyMode = False
Selection.Cut
Range("B12").Select
Selection.Insert shift:=xlToRight
End Sub



lakey13 wrote:

I am so new to excel and trying to learn vba on my own. I am very
experienced in lotus macros except vba is a whole new beast to me. I
know what I want to do, and have pieces already completed. This is the
next piece of the puzzle and I would appreciate anyone that can show me
the coding so I can learn from it.

Basically what I have left at this point is two columns of information.
The first column of information is relevant to a staff member. For
each staff member I require ONE line in an excel spreadsheet. The
information in column B must take up a cell to the right leaving only
ONE instance of the name in column A.

with the following used as an example, this is what is required after
the macro is run:

ADAMSCR CRAIG ADAMS ARB Internet_Access MRA_RO
ADMIN28 VAL_Read_Only
BENDD028 DARRYL BENDER VAL_Read_Only
BRAZEARO ROBERT BRAZEAU ARB COMMERCIAL INDUSTRIAL

Can anyone help??? Please?? :(

thank you... lakey

ADAMSCR CRAIG ADAMS
ADAMSCR ARB
ADAMSCR Internet_Access
ADAMSCR MRA_RO
ADAMSCR
ADMIN28 VAL_Read_Only
ADMIN28
BENDD028 DARRYL BENDER
BENDD028 VAL_Read_Only
BENDD028
BRAZEARO ROBERT BRAZEAU
BRAZEARO ARB
BRAZEARO COMMERCIAL
BRAZEARO INDUSTRIAL

--
lakey13
------------------------------------------------------------------------
lakey13's Profile: http://www.excelforum.com/member.php...o&userid=15997
View this thread: http://www.excelforum.com/showthread...hreadid=274643

--

Dave Peterson