Solved

Excel Cells.value function call

Posted on 2007-03-20
8
888 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
  • 4
  • 3
8 Comments
 
LVL 11

Expert Comment

by:mike1086
Comment Utility
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
Comment Utility
Is there actually anything in cell ws2.Cells(r, column2)?
0
 
LVL 11

Expert Comment

by:mike1086
Comment Utility
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
 

Author Comment

by:dprundle
Comment Utility
r is already defined. R is populated by a for loop.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Expert Comment

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

Author Comment

by:dprundle
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Thanks Mike, that was the problem... column2 wasn't getting defined.

I'll definately google the hungarian standard.

Thanks again
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now