PDA

View Full Version : Looping Macro



Beach Boy
05-29-2018, 02:01 PM
I have a macro that is supposed to populate values in sheets1 and sheets2. It works fine for sheet1 but not sure where I am going wrong for it not work as intended. Could you please help me find the error below?


Option Explicit
Dim cXref As Collection
Dim rSource As Range
Dim stDestination As String
Dim wDestination As Workbook
Dim sDestination As Worksheet
Dim rDestination As Range
Dim i As Integer, j As Long
Dim sPW As String
Sub UpdateDNS()

Set cXref = New Collection
Call buildXREF

stDestination = Application.GetOpenFilename()
sPW = InputBox("Password?", "DNS Cross-Reference")
Workbooks.Open stDestination, , , , sPW


Dim a As Integer
For a = 1 To 2


Sheets(a).Activate


MsgBox ActiveSheet.Name


With ActiveSheet

ActiveSheet.AutoFilterMode = False
Range("A2").Select
ActiveCell.CurrentRegion.Select
Selection.Offset(1, 4).Select
Selection.Resize(Selection.Rows.Count - 1, 1).Select
Set rDestination = Selection

j = rDestination.Rows.Count
On Error Resume Next
For i = 2 To j + 1
If Cells(i, 9).Value = 0 Then Cells(i, 9).Value = cXref(Cells(i, 5).Value)
Next
On Error GoTo 0
Err = 0

ActiveCell.CurrentRegion.AutoFilter
Range("A1").Select

End With
Next a
End Sub


Sub buildXREF()
Range("A2").Select
ActiveCell.CurrentRegion.Select
Selection.Offset(1, 0).Select
Selection.Resize(Selection.Rows.Count - 1, 2).Select
Set rSource = Selection
j = rSource.Rows.Count
For i = 2 To j + 1
cXref.Add Cells(i, 2).Value, Cells(i, 1).Value

Next
End Sub

Paul_Hossler
05-29-2018, 07:34 PM
1. I added CODE tags to your macro -- you can use the [#] icon to insert them and paste your macro between

2. Guessing, the ...



Call buildXREF


… would work with whatever sheet is active since there are no Worksheets() specified.

Could that be it?

The other thing to try is commenting out of removing the On Error Resume Next and see if it throws an error that might identify the issue

Beach Boy
05-30-2018, 08:07 AM
Good point. I removed the Call buildXREF section and commented out the On Error Resume which returned the error: Run-time error '5' Invlid procedure call or argument This refured to the line 'Then Cells(i, 9).Value = cXref(Cells(i, 5).Value)'. This goes back to the Collection "Set cXref = New Collection". Should this be inside the For statement?

Paul_Hossler
05-30-2018, 08:16 AM
Maybe ...


Then Cells(i, 9).Value = cXref(CStr(Cells(i, 5).Value))


It'd be easier to see if you attached a small workbook that shows the issue

Aussiebear
06-03-2018, 04:06 PM
Have you posted this issue elsewhere Beachboy?

Aussiebear
06-04-2018, 06:32 AM
Hmmm.... I asked this because it seems you have, haven't you? Paul has gone out of his way to try to find a solution to your issue but you failed to mention that you posted in another forum..... disrespectful in my opinion. If thats how you want it then let it be delivered to you

Beach Boy
06-04-2018, 12:41 PM
If you find my post in violation of your service use policy then I am requesting that my subscription to this forum be terminated effective immediately.

Paul_Hossler
06-04-2018, 02:15 PM
We only ask that if you ask the same question in another help forum that you let us know so we don't spend our time answering a question that has already be answered

Almost every other forum also has a similar requirement

The FAQ (#4 in my sig) has more details

http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3


You're certainly welcome to stay a member