Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loops | Excel Discussion (Misc queries) | |||
Loops | Excel Discussion (Misc queries) | |||
Using For - Next Loops in VB | New Users to Excel | |||
help with loops | Excel Programming | |||
Loops | Excel Programming |