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?
dprundleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mike1086Commented:
r is nothing.

at least put the row number in, and make sure its got a blue in it. then cf2 will be populated.
0
patrickabCommented:
Is there actually anything in cell ws2.Cells(r, column2)?
0
mike1086Commented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dprundleAuthor Commented:
r is already defined. R is populated by a for loop.
0
mike1086Commented:
It alll looks fine...can you post your the full code, including DIMs etc
0
dprundleAuthor Commented:
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




0
mike1086Commented:
some comments first

when you do this

Dim column1, column2, row1, row2 As Integer

only row2 is integer, the rest are variant

also, ever thought of using a variable naming standard. Check out Hungarian naming standard,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dprundleAuthor Commented:
Thanks Mike, that was the problem... column2 wasn't getting defined.

I'll definately google the hungarian standard.

Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.