asked on

Excel Cells.value function call

Excel Vb Question:

When I call the following function:

cf2 = ws2.Cells(r, 3).Value

cf2 gets populated fine.

BUT! if i create a variable column2 and set it to "3" and use:

dim column2 as long
column2 = InputBox("Enter the column number")
cf2 = ws2.Cells(r, column2).value

cf2 gets populated as an empty string???

What am I doing wrong?

mike1086

r is nothing.

at least put the row number in, and make sure its got a blue in it. then cf2 will be populated.

patrickab

Is there actually anything in cell ws2.Cells(r, column2)?

mike1086

You really need to defne r in your code....once you've done that, and theres something in cell (1,1) or A1 for instance, when r is 1, and you enter 1 in your input box, then you will get a result.

dprundle

ASKER

r is already defined. R is populated by a for loop.

mike1086

It alll looks fine...can you post your the full code, including DIMs etc

dprundle

ASKER

Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim column1, column2, row1, row2 As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
'Turn on updating
Application.ScreenUpdating = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2

ws1.Activate

'Find Column of Part Number in ARINC worksheet
column1 = InputBox("Enter the column number containg part number data from the ARINC workbook to compare.")
If CStr(column1) = "" Then
'User pressed cancel
Exit Sub
End If

'Find Row of Part Number in ARINC worksheet
row1 = InputBox("Enter the row number containg part number data from the ARINC workbook to compare.")
If CStr(row1) = "" Then
'User pressed cancel
Exit Sub
End If

ws2.Activate

'Find Column of Part Number in MASTER worksheet
column2 = InputBox("Enter the column number containg part number data from the MASTER workbook to compare.")
If CStr(column2) = "" Then
'User pressed cancel
Exit Sub
End If
'Find Row of Part Number in MASTER worksheet
row2 = InputBox("Enter the row number containg part number data from the MASTER workbook to compare.")
If CStr(row2) = "" Then
'User pressed cancel
Exit Sub
End If

'For c = 1 To maxC
Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..."
For r = row2 To lr2 'Going down the rows comparing the values lr2 = row count for master list
Application.StatusBar = "Comparing cells " & Format(r / lr2, "0 %") & "..."
cf1 = ""
cf2 = ""
On Error Resume Next
cf2 = ws2.Cells(r, column2).Value 'ws2 = master sheet

test = InputBox("r:" & r & "c:" & column2 & "cf2: " & cf2)
If CStr(test) = "False" Then
'User pressed cancel
Exit Sub
End If

foundit = "False" 'boolean var to determine if the variable was found
foundmultiple = "False" 'boolean var to determine if the variable was found more than once.

If cf2 <> "" And cf2 <> "UNKNOWN" Then

For r1 = row1 To lr1 'going down the rows of the arinc list lr1 = row count for arinc list
cf1 = ws1.Cells(r1, column1).Value 'ws1 = arincsheet

If cf1 = cf2 And foundit = "True" Then
foundmultiple = "True" 'Found it multiple = green
End If

If cf2 = cf1 And foundit = "False" Then
'MsgBox (cf2 & "=" & cf1)

foundit = "True" 'Found it once = no color
End If

Next r1

Else
'set color to red
foundit = "Red"
MsgBox ("RED")
End If

'Turn on updating
Application.ScreenUpdating = True

'Set the colors
If foundmultiple = "True" Then 'set color green because we found multiple
ws2.Cells(r, column2).Interior.ColorIndex = 4
foundmultiple = "False"
End If

If foundit = "True" Then 'do nothing because you found it
ws2.Cells(r, column2).Interior.ColorIndex = 2
End If

If foundit = "False" Then ' Set color Orange (it wasn't found)
ws2.Cells(r, column2).Interior.ColorIndex = 46
End If

If foundit = "Red" Then 'set color to red
ws2.Cells(r, column2).Interior.ColorIndex = 3
End If
foundit = "False"
Next r
'Next c
Application.StatusBar = "Comparison Complete "
End Sub

Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
'CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
' compare two different worksheets in two different workbooks
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim SheetList As String

workbook1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select ARINC Workbook")
If CStr(workbook1) = "False" Then
'User pressed cancel
Exit Sub
End If
workbook2 = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select Master Workbook")
If CStr(workbook2) = "False" Then
'User pressed cancel
Exit Sub
End If

'open the two workbooks
Set wb1 = Workbooks.Open(workbook1)
Set wb2 = Workbooks.Open(workbook2)

wb1.Activate

'Get the sheets in the first workbook
For x = 1 To wb1.Sheets.Count
SheetList = SheetList & vbNewLine & wb1.Sheets(x).Name
Next x
Worksheet1 = InputBox("Enter the worksheet from the ARINC workbook to compare. Your choices are:" & SheetList)
If CStr(Worksheet1) = "False" Then
'User pressed cancel
Exit Sub
End If

SheetList = ""

wb2.Activate

'Get the sheets in the second workbook
For x = 1 To wb2.Sheets.Count
SheetList = SheetList & vbNewLine & wb2.Sheets(x).Name
Next x
Worksheet2 = InputBox("Enter the worksheet from the Master workbook to compare. Your choices are:" & SheetList)
If CStr(Worksheet2) = "False" Then
'User pressed cancel
Exit Sub
End If

CompareWorksheets wb1.Worksheets(Worksheet1), _
wb2.Worksheets(Worksheet2)
End Sub

ASKER CERTIFIED SOLUTION

mike1086

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dprundle

ASKER

Thanks Mike, that was the problem... column2 wasn't getting defined.

I'll definately google the hungarian standard.

Thanks again