Dim Brow As Long, Arow As Long, BCol As Long, ACol As Long, LRow As Long, i As Long, j As Long
Dim BeforeRange As Range, AfterRange As Range, myrange As Range
Sub checkPensionColumns()
With ThisWorkbook.Sheets(3)
Brow = .Range("A" & .Rows.Count).End(xlUp).Row
BCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
Set BeforeRange = .Range(.Cells(4, 1), .Cells(Brow, BCol))
End With
With ThisWorkbook.Sheets(4)
Arow = .Range("A" & .Rows.Count).End(xlUp).Row
ACol = .Cells(3, .Columns.Count).End(xlToLeft).Column
Set AfterRange = .Range(.Cells(4, 1), .Cells(Arow, ACol))
End With
Set myrange = BeforeRange
For i = 1 To myrange.Columns.Count
For j = 1 To myrange.Rows.Count
If AscW(myrange.Cells(j, i)) = 32 Then
With ThisWorkbook.Sheets(2)
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
End With
ThisWorkbook.Sheets(2).Cells(LRow, 1).Value = myrange.Cells(j, i).Address
Else
End If
Next j
Next i
Set myrange = AfterRange
For i = 1 To myrange.Columns.Count
For j = 1 To myrange.Rows.Count
If AscW(myrange.Cells(j, i)) = 32 Then
With ThisWorkbook.Sheets(2)
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
End With
ThisWorkbook.Sheets(2).Cells(LRow, 2).Value = myrange.Cells(j, i).Address
Else
End If
Next j
Next i
Set myrange = BeforeRange
For j = 1 To myrange.Rows.Count
If Application.Worksheet.Function.CountIf(Sheets(3).Range("A4:A" & Brow), myrange.Range("A" & j)) > 1 Then
With ThisWorkbook.Sheets(2)
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
End With
ThisWorkbook.Sheets(2).Cells(LRow, 3).Value = myrange.Cells(j, 1).Address
End If
Next j
MsgBox "Sub end"
End Sub
I've tried researching the error message just not understanding its context in this situation.
Symptoms
Error 438 occurs when running a program in which a form is assigned to a variable and that variable is used to access a control on the form if the program is on a system running Windows 95 with Regional Settings set to a setting other than English (United States). The error text is:
Run-Time Error #438:
Object doesn't support this property or method
Resolution
There are two possible workarounds for this problem.
Access the form directly rather than by a variable containing the form.
-or-
Create property procedures in the form's code to provide access to the properties of the controls on the form.
Bookmarks