How to loop variant fields?

[Mike Callahan]

The following is a routine to loop and list fields 75 places starting at A1. Problem is that don't know how to list a variant with a filed callout. Ex: Below. Is this possible? Thanks for any advice.

Sub Macro1()
counter = 1
flag = 75
Do Until flag = 0

flag = flag - 1
Sheets("sheet1").Select
Range("A(counter)":"I(counter)").Select
Selection.Copy
Sheets("sheet2").Select
Range("A1 ").End(xlDown).Select
Range(ActiveCell.Address).Offset(1, 0).Select
ActiveSheet.Paste

counter = counter + 1


Loop

End Sub

 

[Norie]

Mike

I really don't understand what you mean, unless all you want to do is use counter to refer to cells.

If so, try this.

Range("A" & counter &":"I" & counter").Select

By the way you don't need to Select, if you post back with an explanation of the purpose of the code I'll try and explain.

 

[Mike Callahan]

Hi Norie,

Yes just trying to a list by rows to another sheet. Trying to work up to a routine where "if a field is x>=1 then copy that row to list on another sheet.

I tried your routine yet a get a syntax error. But I know your right.. I have to play with it.

Thanks, I do appreciate it!

 

[Norie]

Mike

I didn't post a routine I posted 1 line of code.

Mind you it did have a few typos.

Range("A" & counter & ":I" & counter).Select

As to what you are trying to do, that's still not clear.

You don't seem to be doing any sort of checking of a 'field' in the original code.

 

[Mike Callahan]

Thanks Norie! That seems to work!! I am still writing the code and will send you what I am trying to do. It is a list routine.

Sorry that I am not a pro at this. I learned Basic years ago and not too good on terminology.

 

[Mike Callahan]

This works! I can't attached the file but it copies text from sheet1 to sheet2. Now I will write a IF field is 1 then... to elinate rows that I do not want to copy over.



Dim counter As Integer
Dim flag As Variant

Sub Macro1()
counter = 4
flag = 28
Do Until flag = 0

flag = flag - 1
Sheets("sheet1").Select
Range("A" & counter & ":I" & counter).Select
Selection.Copy
Sheets("sheet2").Select
Range("A1 ").End(xlDown).Select
Range(ActiveCell.Address).Offset(1, 0).Select
ActiveSheet.Paste

counter = counter + 1


Loop

End Sub