?
Solved

Excel Cells.value function call

Posted on 2007-03-20
8
Medium Priority
?
899 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:dprundle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:mike1086
ID: 18759720
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
 
LVL 45

Expert Comment

by:patrickab
ID: 18759721
Is there actually anything in cell ws2.Cells(r, column2)?
0
 
LVL 11

Expert Comment

by:mike1086
ID: 18759756
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:dprundle
ID: 18759803
r is already defined. R is populated by a for loop.
0
 
LVL 11

Expert Comment

by:mike1086
ID: 18759826
It alll looks fine...can you post your the full code, including DIMs etc
0
 

Author Comment

by:dprundle
ID: 18759838
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
 
LVL 11

Accepted Solution

by:
mike1086 earned 2000 total points
ID: 18759877
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
 

Author Comment

by:dprundle
ID: 18759916
Thanks Mike, that was the problem... column2 wasn't getting defined.

I'll definately google the hungarian standard.

Thanks again
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question