Solved

VBA Excell = how do I make all cells be text

Posted on 2000-03-07
10
228 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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adding to a VBA? 6 82
How to set the sa password in a vb6 code for sql connection 9 62
How to make an ADE file by code? 11 99
vb6 - Transfer from MSHFlexgrid1 to xls issue 8 60
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

679 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