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
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