PDA

View Full Version : [SOLVED] Code Not called on button click



NWE
03-18-2020, 02:34 PM
Hi,

So the following code works on it's own


Sub PullUniques()
With Sheets("Line Items")
Dim rngCell As Range
For Each rngCell In Range("A2:A1000")
If WorksheetFunction.CountIf(Range("B2:B1000"), rngCell) = 0 Then
Range("C" & Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
For Each rngCell In Range("B2:B1000")
If WorksheetFunction.CountIf(Range("A2:A1000"), rngCell) = 0 Then
Range("D" & Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
End With
End Sub


but does not work in this button call:


Sub InsertIsolate()
Call FileImport
Call AuditLine
Call AsBuiltLine
Call PullUniques
End Sub



and I cannot figure out why..any thoughts?

Dave
03-18-2020, 04:02 PM
U can trial this...

Sub PullUniques()
With Sheets("Line Items")
Dim rngCell As Range
For Each rngCell In .Range("A2:A1000")
If WorksheetFunction.CountIf(.Range("B2:B1000"), rngCell) = 0 Then
.Range("C" & .Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
For Each rngCell In .Range("B2:B1000")
If WorksheetFunction.CountIf(.Range("A2:A1000"), rngCell) = 0 Then
.Range("D" & .Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
End With
End Sub
HTH. Dave

p45cal
03-18-2020, 04:03 PM
Perhaps because your With…End With is never used:
Sub PullUniques()
With Sheets("Line Items")
Dim rngCell As Range
For Each rngCell In .Range("A2:A1000")
If WorksheetFunction.CountIf(.Range("B2:B1000"), rngCell) = 0 Then
.Range("C" & Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
For Each rngCell In .Range("B2:B1000")
If WorksheetFunction.CountIf(.Range("A2:A1000"), rngCell) = 0 Then
.Range("D" & Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
End With
End Sub

NWE
03-18-2020, 08:27 PM
Perhaps because your With…End With is never used:
Sub PullUniques()
With Sheets("Line Items")
Dim rngCell As Range
For Each rngCell In .Range("A2:A1000")
If WorksheetFunction.CountIf(.Range("B2:B1000"), rngCell) = 0 Then
.Range("C" & Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
For Each rngCell In .Range("B2:B1000")
If WorksheetFunction.CountIf(.Range("A2:A1000"), rngCell) = 0 Then
.Range("D" & Rows.Count).End(xlUp).Offset(1) = rngCell
End If
Next
End With
End Sub

Wait, what do you mean the With isn't used? I am confused

p45cal
03-19-2020, 04:14 AM
Wait, what do you mean the With isn't used? I am confused
See https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/with-statement?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3Fap pId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1009555)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

You use that With…End With pair of statements, between which you'd expect to see some references beginning with just a dot; there are none.
I put big red dots in your code to show where they might be missing. Dave did a more thorough job of it.

NWE
03-19-2020, 07:45 AM
See https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/with-statement?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3Fap pId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vblr6.chm1009555)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

You use that With…End With pair of statements, between which you'd expect to see some references beginning with just a dot; there are none.
I put big red dots in your code to show where they might be missing. Dave did a more thorough job of it.

Yep..I see it...Thank you Pcal and Dave. I will give that a try and let you guys know what happened.

NWE
03-19-2020, 09:56 AM
Yep..I see it...Thank you Pcal and Dave. I will give that a try and let you guys know what happened.

Yea that worked thanks guys.

Dave
03-20-2020, 03:04 PM
You are welcome. Thanks for posting your outcome. Dave (and P45cal)