For Each Area pls Help.

[edlim85]

Hello there,


I am doing up a macro that can perform a specific task(match and return value) to an Area (set by x rows). For example,my raw data spans 30 rows each. ie A1:A30 for data set 1, A31:A61 data set 2...so on to eof.

Previously, i have used the code below to specify using xlCellTypeConstants to determine each area...How can tell excel for each Area(within x rows) >do task> Next Area.

anyone can give me some advise?

Dim Area As Range
For Each Area In wR.Range("B2", wR.Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas With Area

Regards
Edmund

 

[jbeaucaire]

For adjacent cells, it's common to use a FOR/NEXT using Row designations and stepping through 30 at a time.

Dim Rw As Long, LR As Long, RNG as Range

LR - wR.Range("B2", wR.Range("B" & Rows.Count).End(xlUp)).Row

For Rw = 1 to LR Step 30
    Set RNG = wR.Range("B" & Rw).Resize(30)

    'other stuff here....

Next Rw

 

[edlim85]

Hi jbeaucaire,


Thanks for your prompt reply+advise!

I'm still new to vb. I was prompt an error when i run sub.
"object variable or with block variable not set" what does it mean? the debug went to the line in blue.

my code
Sub reArr()
Dim w1 As Worksheet, wR As Worksheet
Dim LR As Long, Rw As Long, RNG As Range
Dim RowLen As Long
Set w1 = Worksheets("30JUN2011")
Set wR = Worksheets("Result")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
RowLen = 53
For Rw = 1 To LR Step RowLen
Set RNG = w1.Range("A" & Rw).Resize(RowLen)
wR.Cells(2, 2).Value = RNG.Find("vehicle", LookIn:=xlValues, lookAt:=xlWhole).Offset(0, 1).Value
Next Rw


End Sub


Regards
Edmund

 

[Peter_SSs]

I'd suggest that means that "vehicle" was not found in that particular RNG

 

[edlim85]

Peter_Sss you are right. "vehicle" isnt in my RNG.... i missed out that RNG specified above by jbeaucaire only refer to one column. ive amended on my end.

Sorry for the trouble and thanks again.

Regards
Edmund

 

[edlim85]

hi all again,

Dim Rw As Long, LR As Long, RNG as Range
LR - wR.Range("B2", wR.Range("B" & Rows.Count).End(xlUp)).Row

For Rw = 1 to LR Step 30
Set RNG = wR.Range("B" & Rw).Resize(30)
'other stuff here....
Next Rw

The above code works well if each set of my raw data is in fixed row length in this case 30...I realised! that my raw data set is not always in fixed row length. Example some set spans only 25 rows, if i set my RNG in the way above my data will be jumble up!

I have an alternative soln but i have no idea if excel can do that...
Each set of raw data has a head and tail
I wonder i can used the head and tail as my Start and end row marker.

For Rw = 1 to LR
SR = .FindNext("Production Statement").Row
ER = .FindNext("Amount Payable").Row
Set RNG =.Range(Cells( SR, 1), Cells( ER, 5)
Next Rw

Pardon me that my syntax might be wrong. My question is how do i tell excel
1)Find the Row no. of the FIRST "Production Statement" heading.
2)Find the Row no. of the FIRST "Amount Payable"
3)As u can see these values will be input to RNG
...
4)Find the Row no. of the Second "Production Statement" heading.
5)Find the Row no. of the Second "Amount Payable"


Plssss advise!

Regards
Edmund

Life's A Learning Journey!

 

[jbeaucaire]

This is how I'd do that... notice the typo correction in the LR command at the top.

Dim Rw As Long, LR As Long, RNG As Range
Dim rngTOP As Range, rngBTM As Range, rFIRST As Range

LR = wR.Range("B2", wR.Range("B" & Rows.Count).End(xlUp)).Row

On Error Resume Next
Set rngTOP = wR.Range("B:B").Find("Production Statement", LookIn:=xlValues, LookAt:=xlWhole)

If Not rngTOP Is Nothing Then
    Set rFIRST = rngTOP
    Do
        Set rngBTM = wR.Range("B:B").Find("Amount Payable", After:=rngTOP, LookIn:=xlValues, LookAt:=xlWhole)
        Set RNG = Range(rngTOP, rngBTM)
        
        'do stuff here to your range
        
        Set rngTOP = wR.Range("B:B").Find("Production Statement", After:=rngBTM, LookIn:=xlValues, LookAt:=xlWhole)
    
    Loop Until rngTOP.Address = rFIRST.Address
    
Else

    MsgBox "PRODUCTION STATEMENT was not found. Please check data."
    Exit Sub
    
End If