Array code troubles

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

With the three sheets in the array, find the text "DEPR-GENERATORS" in columb B and with the next five rows below "DEPR-GENERATORS", preceed the text in those cells with the text "DEPR".

This code finds the "DEPR-GENERATORS" in columb B and preceeds the five rows each with 3 "DEPR"'s and only on Sheet1.

A sample row before and after code runs:

-COMPUTER EQUIP

and after:

DEPRDEPRDEPR-COMPUTER EQUIP

And only runs on sheet 1.

Thanks.
Howard

Sub AFindIt()

Dim i As Long, ii As Long
Dim MyArr As Variant

MyArr = Array("Sheet1", "Sheet2", "Sheet3")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)

With MyArr(i)

' "DEPR-GENERATORS" will be in Column B1:Bn
Cells.Find(What:="DEPR-GENERATORS", After:=ActiveCell, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

' Put DEPR in front of the text in the next 5 rows below found text "DEPR-GENERATORS"
For ii = 1 To 5
ActiveCell.Offset(ii) = "DEPR" & Trim(ActiveCell.Offset(ii))
Next ' ii

End With 'MyArr(i)
Next 'i
Application.ScreenUpdating = True
End Sub
 
Hi Howard,

Am Sat, 9 Aug 2014 03:50:22 -0700 (PDT) schrieb L. Howard:
With MyArr(i)
Click to expand...

an array is not an object so you cannot write With MyArr(i)

Try it this way:

Sub AFindIt()

Dim i As Long, ii As Long
Dim MyArr As Variant
Dim c As Range

MyArr = Array("Sheet1", "Sheet2", "Sheet3")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)
With Sheets(MyArr(i))
' "DEPR-GENERATORS" will be in Column B1:Bn
Set c = .Range("B:B").Find(What:="DEPR-GENERATORS",
After:=.Range("B1"), _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext)

' Put DEPR in front of the text in the next 5 rows below found
text "DEPR-GENERATORS"
For ii = 1 To 5
c.Offset(ii) = "DEPR" & Trim(c.Offset(ii))
Next ' ii
End With

Next 'i
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
Hi Howard,

Am Sat, 9 Aug 2014 13:19:11 +0200 schrieb Claus Busch:

if DEPR-GENERATORS occurs more than once in the column you have to use
FindNext:

Sub AFindIt()

Dim i As Long, ii As Long
Dim MyArr As Variant
Dim c As Range
Dim FirstAddress As String

MyArr = Array("Sheet1", "Sheet2", "Sheet3")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)
With Sheets(MyArr(i))
' "DEPR-GENERATORS" will be in Column B1:Bn
Set c = .Range("B:B").Find(What:="DEPR-GENERATORS",
After:=.Range("B1"), _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
' Put DEPR in front of the text in the next 5 rows below found
text "DEPR-GENERATORS"
For ii = 1 To 5
c.Offset(ii) = "DEPR" & Trim(c.Offset(ii))
Next ' ii
Set c = .Range("B:B").FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
Next 'i
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
I was sorta close, but no cigar.

Works very well.

Thanks much, Claus.

Regards,
Howard
 
For clarity...

[A]
MyArr contains sheetnames: The With block requires a fully qualified
ref to an object...

With Sheets(MyArr(i))

...otherwise your code implicitly refs the active sheet.


Setting a ref to an object requires its properties be ref'd via a
'dot'...

With Sheets(MyArr(i))
.Cells...

-OR-

With Sheets(MyArr(i)).Cells
.Find...

...since what you're actually doing is searching the Cells collection of
each sheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top