Solved

VBA Excell = how do I make all cells be text

Posted on 2000-03-07
10
224 Views
Last Modified: 2010-08-05
I am trying to compare cells between two large excell files to be sure the data in each cell exactly corresponds to the same cell in the other xls.  This
works great if it is text or the value is > 0 and not null.   If the cell is 0 and the corresponding cell is null, they are not identified as different.

I think this is because they are "General" format.  How do I force all cells to be text?
0
Comment
Question by:quantz
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Hi quantz,

Use the Format(AnyRange) function to transform a Null cell to "" and a zero value cell to "0", than VBA will see the difference.

Example:
Supposing you're using this test:

If Workbooks("Book1").Sheets("sheet1").Range("A1")=Workbooks("Book2").Sheets("sheet1").Range("A1") Then

or any alike comparison, replace it by using the Format function. Example:

If Format(Workbooks("Book1").Sheets("sheet1").Range("A1"))=Format(Workbooks("Book2").Sheets("sheet1").Range("A1")) Then

Good Luck

Calacuccia
0
 
LVL 4

Accepted Solution

by:
wylliker earned 50 total points
Comment Utility
If a Cell does not have a value it will return the special value - Empty.

You could do a comparision to see if each Cell has a value using IsEmpty() that would allow you to tell the difference between 0 and empty.
0
 
LVL 22

Expert Comment

by:ture
Comment Utility
quantz,

You can use VARTYPE to determine if the cells are of the same type. This is good not only for seeing the difference between empty and non-empty cells but also for comparing cells containing values with cells containing errors, like #DIV/0! or other errors.

Sub CompareSheets()
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim c1 As Range, c2 As Range
  Dim rmax As Long, cmax As Long, r As Long, c As Long
  Dim ok As Boolean
 
  Set ws1 = Sheets("Sheet1")
  Set ws2 = Sheets("Sheet2")
  Set c1 = ws1.Cells.SpecialCells(xlCellTypeLastCell)
  Set c2 = ws2.Cells.SpecialCells(xlCellTypeLastCell)
 
  rmax = Application.Max(c1.Row, c2.Row)
  cmax = Application.Max(c1.Row, c2.Row)
 
  Debug.Print "Address", ws1.Name, ws2.Name
 
  For r = 1 To rmax
    For c = 1 To cmax
      Set c1 = ws1.Cells(r, c)
      Set c2 = ws2.Cells(r, c)
     
      If VarType(c1) <> VarType(c2) Then
        ok = False
      ElseIf c1 <> c2 Then
        ok = False
      Else
        ok = True
      End If
     
      If Not ok Then Debug.Print c1.Address, c1.Value, c2.Value
    Next c
  Next r
End Sub

Ture Magnusson
Karlstad, Sweden
0
 

Expert Comment

by:dalbello
Comment Utility
The best solution is to convert the data to compare in the string type for both files.  For this conversion you can use Cstr() function.

Sub CompareSheets()
  Dim w1 As Workbook
  Dim w2 As Workbook
  Dim s1 As Worksheet
  Dim s2 As Worksheet
  Dim a As String
  Dim b As String

  Set w1 = Workbooks("Cartel1")
  Set w2 = Workbooks("Cartel2")
  Set s1 = w1.Sheets("Sheet1")
  Set s2 = w2.Sheets("Sheet1")

  For c = 1 To 65536 ' for first to last possible row
    For r = 1 To 256 'for first to last possible column
        a = CStr(s1.Cells(c, r).Value)
        b = CStr(s2.Cells(c, r).Value)
        If CompareData(a, b) = False Then
            MsgBox ("Error in row " & r & " - column " & c)
        End If
    Next r
  Next c

End Sub


Function CompareData(a As String, b As String) As Boolean
If a <> b Then
    CompareData = False
Else
    CompareData = True
End If
End Function

I help this is the solution for your question.
Antonio Guadagnin
antonioguadagnin@dalbello.net
0
 

Expert Comment

by:dalbello
Comment Utility
The best solution is to convert the data to compare in the string type for both files.  For this conversion you can use Cstr() function.
Sub CompareSheets()
  Dim w1 As Workbook
  Dim w2 As Workbook
  Dim s1 As Worksheet
  Dim s2 As Worksheet
  Dim a As String
  Dim b As String
  Set w1 = Workbooks("Cartel1")
  Set w2 = Workbooks("Cartel2")
  Set s1 = w1.Sheets("Sheet1")
  Set s2 = w2.Sheets("Sheet1")
  For c = 1 To 65536 ' for first to last possible row
    For r = 1 To 256 'for first to last possible column
        a = CStr(s1.Cells(c, r).Value)
        b = CStr(s2.Cells(c, r).Value)
        If CompareData(a, b) = False Then
            MsgBox ("Error in row " & r & " - column " & c)
        End If
    Next r
  Next c
End Sub
Function CompareData(a As String, b As String) As Boolean
If a <> b Then
    CompareData = False
Else
    CompareData = True
End If
End Function
I help this is the solution for your question.
Antonio Guadagnin
antonioguadagnin@dalbello.net
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Expert Comment

by:dalbello
Comment Utility
dalbello changed the proposed answer to a comment
0
 

Expert Comment

by:dalbello
Comment Utility
The best solution is to convert the data to compare in the string type for both files.  For this conversion you can use Cstr() function.

Sub CompareSheets()
  Dim w1 As Workbook
  Dim w2 As Workbook
  Dim s1 As Worksheet
  Dim s2 As Worksheet
  Dim a As String
  Dim b As String

  Set w1 = Workbooks("Cartel1")
  Set w2 = Workbooks("Cartel2")
  Set s1 = w1.Sheets("Sheet1")
  Set s2 = w2.Sheets("Sheet1")

  For r = 1 To 65536 ' for first to last possible row
    For c = 1 To 256 'for first to last possible column
        a = CStr(s1.Cells(r, c).Value)
        b = CStr(s2.Cells(r, c).Value)
        If CompareData(a, b) = False Then
            MsgBox ("Error-  row " & r & " - column " & c)
        End If
    Next c
  Next r

End Sub


Function CompareData(a As String, b As String) As Boolean
If a <> b Then
    CompareData = False
Else
    CompareData = True
End If
End Function

Sorry for the bad comments. This is the correct code.
I help this is the solution for your question.
Antonio Guadagnin
antonioguadagnin@dalbello.net
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Dalbello,

what do you think the Format(Range) command does ?

Convert the number/text/anything to a string !
0
 

Author Comment

by:quantz
Comment Utility
Wylker's comment should have been accepted as answer.  Works great. Simple. Short.  I meant to acknowledge it. PC froze.
0
 
LVL 4

Expert Comment

by:wylliker
Comment Utility
Thanks!

After the day I had today ... I'm glad at least something I had done Worked great, was simple, and was short.

Short and simple is usually best.

I suppose a few people might have been thrown by the lead-in to your question - about making all cells text - as you were really in need of something a little bit different.

Trick is to look where the finger is pointing and not at the finger.


Take care
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 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