Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Excell = how do I make all cells be text

Posted on 2000-03-07
10
Medium Priority
?
235 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 150 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Industry Leaders: 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!

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

885 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