(E-Mail Removed) wrote:
> I started this yesterday and got some help with this macro:
>
> Sub test()
> Dim Counter As Integer
> For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1
> If Cells(Counter, 1) = 1 Then
> Cells(Counter, 1).EntireRow.Insert
> Cells(Counter, 1).FormulaR1C1 = "Record Type"
> Cells(Counter, 2).Value = "Process Date/Time"
> Cells(Counter, 3).Value = "Customer Number"
> Cells(Counter, 4).Value = "Customer Name"
> Cells(Counter, 5).Value = "Enrollment Type"
> Cells(Counter, 6).Value = "Filler"
> End If
> Next Counter
> End Sub
>
> When I changed it to : If Cells(Counter,1) = 2 then
> It woudn't loop through my spread sheet. It would only put the row
> over the first 2 row found.
> (I have a spread sheet where column A is either a 01, 02, 03 or 04. I
> need to insert a header line over each one but 01 has one set of
> headers, 02 a second and so forth. Each number will happen multiple
> times so I need the macro to loop)
>
> So I tried making something new and came up with this:
>
> Sub enter_headers()
>
> Dim rangetosearch As Object
> Dim cellelement As Object
> Dim counter As Integer
> Dim Record As Integer
>
>
> counter = 1
>
> Set rangetosearch = Sheet1.Range(Cells(counter, 1), Cells(counter, 1))
> Set cellelement = rangetosearch.Cells
>
> For Each cellelement In rangetosearch
>
> Record = cellelement.Value
>
> If Record = 2 Then
> Cells(counter, 1).EntireRow.Insert
> Cells(counter, 1).FormulaR1C1 = "Record Type"
> Cells(counter, 2).Value = "Process Date/Time"
> Cells(counter, 3).Value = "Customer Number"
> Cells(counter, 4).Value = "Customer Name"
> Cells(counter, 5).Value = "Enrollment Type"
> Cells(counter, 6).Value = "Filler"
>
> End If
>
> counter = counter + 1
>
>
> Next
>
>
> End Sub
>
> However with this one my rangetosearch line won't work. I am trying to
> tell it to search all the way thorugh column A for anything beginning
> with 02. (and it will expand eventually to include all 01, 02, 03, and
> 04) Am I way off base with this one?
Hi,
If it is 01, 02, 03, or 04 shouldn't it be testing for "01" rather than
1 etc.
Also .Row was missing from the 2nd line.
You could use Select Case to do all in the one loop.
This might work...
Sub test()
Dim Counter As Integer
For Counter = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
Select Case Cells(Counter, 1)
Case "01"
Cells(Counter, 1).EntireRow.Insert
Cells(Counter, 1).Value = "Record Type"
Cells(Counter, 2).Value = "Process Date/Time"
Cells(Counter, 3).Value = "Customer Number"
Cells(Counter, 4).Value = "Customer Name"
Cells(Counter, 5).Value = "Enrollment Type"
Cells(Counter, 6).Value = "Filler"
Case "02"
Cells(Counter, 1).EntireRow.Insert
Cells(Counter, 1).Value = "HeaderA,2"
Cells(Counter, 2).Value = "HeaderB,2"
Cells(Counter, 3).Value = "HeaderC,2"
Cells(Counter, 4).Value = "HeaderD,2"
Cells(Counter, 5).Value = "HeaderE,2"
Cells(Counter, 6).Value = "HeaderF,2"
Case "03"
Cells(Counter, 1).EntireRow.Insert
Cells(Counter, 1).Value = "HeaderA,3"
Cells(Counter, 2).Value = "HeaderA,3"
Cells(Counter, 3).Value = "HeaderA,3"
Cells(Counter, 4).Value = "HeaderA,3"
Cells(Counter, 5).Value = "HeaderA,3"
Cells(Counter, 6).Value = "HeaderA,3"
Case "04"
Cells(Counter, 1).EntireRow.Insert
Cells(Counter, 1).Value = "HeaderA,4"
Cells(Counter, 2).Value = "HeaderA,4"
Cells(Counter, 3).Value = "HeaderA,4"
Cells(Counter, 4).Value = "HeaderA,4"
Cells(Counter, 5).Value = "HeaderA,4"
Cells(Counter, 6).Value = "HeaderA,4"
End Select
Ken Johnson
Similar Threads
