Help with Loop Array Iteration and Processing

B

Bigced_21

New Member
Joined
Aug 26, 2010
Messages
11
I am a novice at excel vba and array's.
I want to be able to loop thru the wrkshts in the array and pass that wrksht as a variable to my formulas(if i even have those setup correctly) for processing of capturing cell range data for each of the worksheets from month to month basis. I need some serious help getting on track and headed in the right direction. I appreciate your assistance with this. Thanks

Code: 
Private Sub CommandButton1_Click()
Dim vDate
Dim vLinkPath
Dim vMonth1
Dim vYear1
Dim vStatus
Dim i As Integer
Dim ShtArr As Variant
vDate = ActiveSheet.Range("A1")
vLinkPath = ActiveSheet.Range("A13")
If IsDate(vDate) = False Then
    MsgBox "You must first enter an Date for this workbook in cell A1.", vbExclamation, "Missing Data"
    Range("A1").Select
    Exit Sub
End If
If IsEmpty(vLinkPath) = True Then
    MsgBox "You must first enter an Archive File Directory for Education Monthly Report in cell A13.", vbExclamation, "Missing Data"
    Range("A13").Select
    Exit Sub
End If
vMonth1 = MonthName(Month(DateAdd("m", -1, vDate)))
vYear1 = Format(DateAdd("m", -1, vDate), "yyyy")
ShtArr = Array("BCC", "BCFC", "EKCC", "FCDC", "GRCC", "KCIW", "KSP", "KSR", "LEA", "LLCC", "LSCC", "MAC", "NTC", "OCCC", "RCC", "WKCC")
For i = LBound(ShtArr) To UBound(ShtArr)
    With Sheets(arr(i))
    .Unprotect
        If Month(vDate) = 10 Then
                Range("B4:J4").Formula = "='ShtArr'!R4C2:R4C10"
                Range("B7:J7").Formula = "='ShtArr'!R7C2:R7C10"
                Range("D12").Formula = "='ShtArr'!D12"
                Range("G15").Formula = "='ShtArr'!G15"
        Else
                Range("B4:J4").Formula = "=(B4:J4)+'" + vLinkPath + vYear1 + "\[" + vMonth1 + " " + vYear1 + ".xls]ShtArr'!R4C2:R4C10"
                Range("B7:J7").Formula = "=(B4:J4)+'" + vLinkPath + vYear1 + "\[" + vMonth1 + " " + vYear1 + ".xls]ShtArr'!R7C2:R7C10"
                Range("D12").Formula = "=D12+'" + vLinkPath + vYear1 + "\[" + vMonth1 + " " + vYear1 + ".xls]ShtArr'!D12"
                Range("G15").Formula = "=G15+'" + vLinkPath + vYear1 + "\[" + vMonth1 + " " + vYear1 + ".xls]ShtArr'!G15"
        End If
                vStatus = "Links to external workbooks are set, based on " + MonthName(Month(vDate)) + " " + Format(vDate, "yyyy") + "."
    
    'Sheets("ShtArr").Protect DrawingObjects:=True
        .Protect DrawingObjects:=True
    End With
    
Next
ActiveSheet.Unprotect
With ActiveSheet
    .Range("A18") = vStatus
    .Range("A18").Font.ColorIndex = 0
    .Range("A18").Interior.ColorIndex = 15
End With
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True
MsgBox vStatus, vbInformation, "Setup Complete"
End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
MonthName appears to be a separate function -- Can you show the code lines for it?


MonthName(Month(DateAdd("m", -1, vDate)))
 
Upvote 0
jim may said:
MonthName appears to be a separate function -- Can you show the code lines for it?


MonthName(Month(DateAdd("m", -1, vDate)))
Click to expand...
Jim,
The following code: MonthName(Month(DateAdd("m", -1, vDate)))
is pulling from cell "A1" which contain the date October 1...That part works fine retrieving the cell data. My issue starts with the If...Else statement.
 
Upvote 0
Hi

You need to refernce it like this:

Rich (BB code): 
With Sheets(arr(i))
    .Unprotect
        If Month(vDate) = 10 Then
                Range("B4:J4").Formula = "='" & .Name & "'!R4C2:R4C10"
 
Upvote 0
Richard Schollar said:
Hi

You need to refernce it like this:

Rich (BB code): 
With Sheets(arr(i))
    .Unprotect
        If Month(vDate) = 10 Then
                Range("B4:J4").Formula = "='" & .Name & "'!R4C2:R4C10"
Click to expand...
Where is the ".Name" coming from? do i have the entire loop setup correctly? Also, i know i've butchered the "Else" part of the "if...else" code and need some guidance dealing with that.
 
Upvote 0
It will pass the element of arr(i) - the other way to have written it would have been:

Rich (BB code): 
With Sheets(arr(i))    
  .Unprotect        
  If Month(vDate) = 10 Then                
    Range("B4:J4").Formula = "='" & arr(i) & "'!R4C2:R4C10"

Both have identical results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Count - specific consecutive order
Replies
6
Views
515
Peter_SSs
Peter_SSs
L
  • Solved
Looking for an sequence of consecutive days within an array of dates while ignoring weekends
Replies
6
Views
581
Anonymous1378
A
M
  • Question
Help making code faster
Replies
3
Views
313
michaelsmith559
M
M
  • Solved
Update formula on mouse click without macro
Replies
3
Views
211
Fluff
Fluff
K
  • Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
141
keranali
K

Forum statistics

Threads
1,215,064
Messages
6,122,942
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top