PDA

View Full Version : Solved: User Form syntax



kathyb0527
08-11-2009, 03:59 PM
I have code that compares two columns on a sheet and returns any discrepancies. The columns may be different for different users so I have a User Form that asks for the columns to compare. How do I set my range to go to the last row using the values?


iCompCol = Range(frmMatch.iRunCol.Value & "3").Column
Set myScanrange = wbSource.Sheets(1).Range(frmMatch.iScanCol.Value & "3", frmMatch.iScanCol.Value & ???????
iSourceCol = myScanrange.Column
iOffset = iCompCol - iSourceCol
lRow = wsReport.Range("StartRow").Row
For Each c In myScanrange
If c.Value = c.Offset(0, iOffset).Value Then
Else
wsReport.Cells(lRow, "A").Value = c.Row
wsReport.Cells(lRow, "B").Value = c.Value
wsReport.Cells(lRow, "C").Value = c.Offset(0, iOffset).Value

I'm just having the problem with the second line.

Thanks!

Bob Phillips
08-11-2009, 04:24 PM
iCompCol = Range(frmMatch.iRunCol.Value & "3").Column
With wbSource.Sheets(1)

LastRow = .Cells(.Rows.Count, frmMatch.iScanCol.Value).End(xlUp).Row
Set myScanrange = .Range(frmMatch.iScanCol.Value & "3", frmMatch.iScanCol.Value & LastRow)
End With
iSourceCol = myScanrange.Column
iOffset = iCompCol - iSourceCol
lRow = wsReport.Range("StartRow").Row
For Each c In myScanrange
If c.Value = c.Offset(0, iOffset).Value Then
Else
wsReport.Cells(lRow, "A").Value = c.Row
wsReport.Cells(lRow, "B").Value = c.Value
wsReport.Cells(lRow, "C").Value = c.Offset(0, iOffset).Value

kathyb0527
08-12-2009, 09:38 AM
Thank you!