Solved

VBA Excell = how do I make all cells be text

Posted on 2000-03-07
10
229 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
[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
  • 2
  • 2
  • +2
10 Comments
 
LVL 17

Expert Comment

by:calacuccia
ID: 2594005
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
ID: 2594795
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
ID: 2595361
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Expert Comment

by:dalbello
ID: 2597239
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
ID: 2597241
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
ID: 2597268
dalbello changed the proposed answer to a comment
0
 

Expert Comment

by:dalbello
ID: 2597278
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
ID: 2597316
Dalbello,

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

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

Author Comment

by:quantz
ID: 2597876
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
ID: 2599217
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…

751 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