ExcelBanter - View Single Post - Can't make loop macro work

Ken Johnson

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