quantz
asked on
VBA Excell = how do I make all cells be text
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(xlC
Set c2 = ws2.Cells.SpecialCells(xlC
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
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(xlC
Set c2 = ws2.Cells.SpecialCells(xlC
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
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.
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.
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.
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.
dalbello changed the proposed answer to a comment
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.
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.
Dalbello,
what do you think the Format(Range) command does ?
Convert the number/text/anything to a string !
what do you think the Format(Range) command does ?
Convert the number/text/anything to a string !
ASKER
Wylker's comment should have been accepted as answer. Works great. Simple. Short. I meant to acknowledge it. PC froze.
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
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
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(
or any alike comparison, replace it by using the Format function. Example:
If Format(Workbooks("Book1").
Good Luck
Calacuccia