PDA

View Full Version : Insert colums and fill down - Variables



KatSmith
03-24-2012, 01:56 PM
Hello,

I have searched your forum and can't dins anything that specifically addresses the two macro challenges that I have.


Challenge 1

I have a sample file attached Tab 1 is Sample1 and the second tab is Sample 2.

Sample 1 shows the initial file, and Sample 2 a short exampl of what I am trying to achieve.

What I need is a macro that will insert two columns to the left of column A, then copy Agent's Name in (D) and the Date in (H) into the new columns. Now the challenge is that not every sequence is the same number of rows. Some have comments, some don't, so I can say to copy say 10 rows and move the the next evaluation. The macro will have to read and identify the original column A cell where is says "Agent Name:" as the breaking point to then pick up and copy & past the name and date in the next evaluation. There is a possibility that the file can be over 500 rows, so I would need this to loop until the macro did not identify the trigger

Challenge 2

This needs to be a separate macro, and not part of the first one. I would like a input box to pop up and ask me the the first 5 to 10 characters of a row that I want to delete. So, if I wanted to delete the row that begins with " NC05 __________ Call Control/Time Management" them I could input 'NC05" into the box and the the macro would loop through the file and delete all instances of this. I know I can do this with a replace, but that still leaves the empty row.

Can someone help me with this? I would greatly appreciate any assistance you can provide to me.

:dunno :banghead: :help

mikerickson
03-25-2012, 10:03 AM
This should take care of part 1
Sub AddColumns()
Dim outArray() As Variant
Dim maxRow As Long, i As Long
Dim curName As String, curDate As Variant

With Sheet3: Rem adjust
maxRow = .Cells(.Rows.Count, 1).End(xlUp).Row
ReDim outArray(1 To maxRow, 1 To 2)

.Range("A1").Resize(maxRow, 2).Insert shift:=xlShiftToRight

For i = 1 To maxRow
If Application.Trim(LCase(CStr(.Cells(i, 3)))) Like "agent name*" Then
curName = Application.Trim(CStr(.Cells(i, 4)))
curDate = .Cells(i, 8)
Else
outArray(i, 1) = curName
outArray(i, 2) = curDate
End If
Next i
outArray(1, 1) = "Agent": outArray(1, 2) = "Date"
Range("A1").Resize(maxRow, 2).Value = outArray
End With

End Sub

About part 2,
"Search through the file and delete all instances of this" does this mean find any cell in any sheet in the workbook that begins with the search term and delete that row?
or
does "the row that begins with ..." mean that one should look only through column A of (all? / a single?) sheet and delete those rows.

Is the second macro to be applied before or after the first macro?

This second part could easily be done with AutoFilter if most of your cells didn't have a leading space.