Loops

S

SaraJane

I am a newby at macros - so be kind! Can I name ranges within a worksheet
and then write a loop so that I can perform the same functions on each range?
 
O

Otto Moehrbach

SaraJane
Something like this may work for you. You can add as many range names
as you want. HTH Otto
Sub LoopThruNames()
Dim TheName As Variant
For Each TheName In Array("ThisName", "ThatName", "OtherName")
'Code that operates on "TheName", for instance:
MsgBox Range(TheName).Address(0, 0)
Next
End Sub
 
S

SaraJane

I converted a PDF file to excel. I want to convert this data to a usable
form. Each record (l'll call them indiviidual well data) extends to many
columns and rows. I want to name each record "well" and then perform the
exact same functions on each of these records. Is that specific enough?
 
N

Nick Hodge

Sara Jane

It is tough to know exactly what you have and you've had one suggestion. You
can iterate collections, so for example if you have a range of A1:C100 you
could do this to work on each object in the collection

Dim myCell as Range
For Each myCell in Range("A1:C100")
myCell.Value=MyCell.Value+1
Next myCell

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
 
G

Gord Dibben

Is there any common factor that differentiates one record from another?

Blank row between?

Same number of rows per set?


Gord Dibben MS Excel MVP
 
S

SaraJane

Yes, one row between record and yes the same number of rows per set

Gord said:
Is there any common factor that differentiates one record from another?

Blank row between?

Same number of rows per set?

Gord Dibben MS Excel MVP
I converted a PDF file to excel. I want to convert this data to a usable
form. Each record (l'll call them indiviidual well data) extends to many
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
S

SaraJane

Yes, one row between record and yes the same number of rows per set

Gord said:
Is there any common factor that differentiates one record from another?

Blank row between?

Same number of rows per set?

Gord Dibben MS Excel MVP
I converted a PDF file to excel. I want to convert this data to a usable
form. Each record (l'll call them indiviidual well data) extends to many
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
S

SaraJane

Yes, one row between record and yes the same number of rows per set

Gord said:
Is there any common factor that differentiates one record from another?

Blank row between?

Same number of rows per set?

Gord Dibben MS Excel MVP
I converted a PDF file to excel. I want to convert this data to a usable
form. Each record (l'll call them indiviidual well data) extends to many
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
S

SaraJane

Yes, one row between record and yes the same number of rows per set

Gord said:
Is there any common factor that differentiates one record from another?

Blank row between?

Same number of rows per set?

Gord Dibben MS Excel MVP
I converted a PDF file to excel. I want to convert this data to a usable
form. Each record (l'll call them indiviidual well data) extends to many
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
B

Bernie Deitrick

SaraJane,

It would help to know what 'function' you want to perform on each range.

But, you could loop through the range areas. Try this test macro with the
sheet active:

Sub Test()
Dim myArea As Range
Dim myCells As Range
Dim i As Integer
i = 0
Set myCells = Cells.SpecialCells(xlCellTypeConstants)
MsgBox "There are " & myCells.Areas.Count & " ""wells"" of data"
For Each myArea In myCells.Areas
i = i + 1
MsgBox "Area #" & i & " is in cells " & myArea.Address
Next myArea
End Sub


If this macro appears to count the correct number of 'wells', then we can
apply functions once we know what you want.

HTH,
Bernie
MS Excel MVP




SaraJane said:
Yes, one row between record and yes the same number of rows per set

Gord said:
Is there any common factor that differentiates one record from another?

Blank row between?

Same number of rows per set?

Gord Dibben MS Excel MVP
I converted a PDF file to excel. I want to convert this data to a usable
form. Each record (l'll call them indiviidual well data) extends to many
Click to expand...
[quoted text clipped - 8 lines]
and then write a loop so that I can perform the same functions on each
range?
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

See Bernie's post with code to loop through each set.

As Bernie says"what will you do with each set"?

Transpose to one row per set or?


Gord

Yes, one row between record and yes the same number of rows per set

Gord said:
Is there any common factor that differentiates one record from another?

Blank row between?

Same number of rows per set?

Gord Dibben MS Excel MVP
I converted a PDF file to excel. I want to convert this data to a usable
form. Each record (l'll call them indiviidual well data) extends to many
Click to expand...
[quoted text clipped - 8 lines]
and then write a loop so that I can perform the same functions on each
range?
Click to expand...
Click to expand...
Click to expand...
 
Top