Why procedure over-writes previous cell contents?

[VBA_Newbie]

I created a procedure which tests for significant differences between two values. The details of how it works aren't all that important. But this is something like what my sheet looks like (It doesn't paste exactly as it looks on my screen, but you'll still get the idea)

ExcelCREATETABLE.xls
	A	B	C	D	E	F	G	H	I
69	Q.2 - How do you feel about attribute X?
70		TOTAL	CITY		AGE			GROUP
71			Toronto	Montreal	20-29	30-39	44-55	Users	Non-users
72		A	B	C	D	E	F	G	H
73	Base =	150	75	75	50	50	50	75	75
74		%	%	%	%	%	%	%	%
75	I like it a lot................	20	12	13	16	45	12	12	12
76	I like it somewhat........	44C	14	14	29	14	14	34	14
77	Neither like nor dislike	34	35	35	35	2	35	55	35
78	Dislike somewhat.......	22	24	24	24	24	11	24	24
79	Dislike a lot................	25C	35C	55B	13	13	13	-	13
80	Top Box.....................	20	12	13	16	88	12	12	12
81	TopTwo.....................	64C	26	27	45	60	26	46	26
TestPage1

When I run the procedure it tests 1 column against another 1...for instance Toronto against Montreal. If there is a significant difference, it will bold the value in Toronto and place the letter of the column from which it was different. For instance the bold 35 under Toronto, means it is significantly different than the corresponding value from Montreal (C). The problem I have is if I want to test Toronto (B) against another column (for instance TOTAL). It will write over the C, and place and A. Ideally I'd like it to indicate a difference from both like this 35CA.

My code to format the cell if there is a difference looks like this:

If Significant = True Then
FormatRange.Cells(i, Col1Num).Select
With Selection.Font
.FontStyle = "Bold"
End With
If Col2Num = 5 Then
Selection.NumberFormat = "0" & LetterD
End If
If Col2Num = 6 Then
Selection.NumberFormat = "0" & LetterE
End If
...etc...for every letter

This is in a loop, so the next time I run the loop it overwrites the previous letter.

So my main problem is, how can I get the previous letter to not be over written?

 

[martinee]

You are changing the cell format rather than the cell itself. Try something like:
Selection.Value = Selection.Value & "D"
Then it should not overwrite your data so much as add to it. HTH.

 

[VBA_Newbie]

I gave that a try, and it didn't quite work. The 35 is actually 34.6, I just formatted the cells so that it displays 35. When I tried your code, the next time it tried to use that cell to compare it to another cell, it was treated as 34.6B, so an error popped up. That's why I was using:

Selection.NumberFormat = "0" & LetterD

So that it will display the letter, but the value of the cell is still treated as 34.6 in future calculations. Any other thoughts?

 

[martinee]

It's always in the details. Try this:

If IsNumeric(Selection.Value) Then
Selection.Value = Round(Selection.Value, 0) & "D"
Else
Selection.Value = Selection.Value & "E"
End If

No errors for me, give it a try. HTH.

 

[VBA_Newbie]

It only works the first time through the loop. Subsequently it changes the value of that cell to be 35B (or whatever letter). So when comparing that cell (which now has a value of 35B) to another cell (which is a regular number) it gives me an error because it is trying to do calculations with two numbers, and one of them is not a number any more. I do appreciate your help though.

 

[martinee]

Well, could you edit your function : Significant : to compensate for this? You haven't shown me any code for that, so I can't really help you out there unless you do.

 

[VBA_Newbie]

Here is my ridiculous looking code. The is the first macro I've ever made, so keep that in mind when you see my stupid programming style.

'********SIGNIFICANCE TESTING*********** (NOT COMPLETE/WORKING)

'This prompts the user to indicate whether they want to
'do significance testing
Dim sigTest As String, anotherSigtest As String

sigTest = InputBox("Do you want significance testing? 'y' = yes")
If sigTest = "y" Then
Do

Dim Col1ToTest As String, Col2ToTest As String
Col1ToTest = InputBox("Choose the first column to test (ie. If the first if you want to test is column B against C, Type 'B'")
Do Until Col1ToTest = "A" Or Col1ToTest = "B" Or Col1ToTest = "C" Or _
Col1ToTest = "D" Or Col1ToTest = "E" Or Col1ToTest = "F" Or Col1ToTest = "G" Or _
Col1ToTest = "H" Or Col1ToTest = "I" Or Col1ToTest = "J" Or Col1ToTest = "K" Or _
Col1ToTest = "L" Or Col1ToTest = "M" Or Col1ToTest = "N" Or Col1ToTest = "O" Or _
Col1ToTest = "P" Or Col1ToTest = "Q" Or Col1ToTest = "R" Or Col1ToTest = "S"
MsgBox ("Your response is not valid, try again")
Col1ToTest = InputBox("Choose the first column to test (ie. If the first if you want to test column B against C, Type 'B'")
Loop

Col2ToTest = InputBox("Choose the second column (ie. If the first if you want to test column B against C, Type 'B'")
Do Until Col2ToTest = "A" Or Col2ToTest = "B" Or Col2ToTest = "C" Or _
Col2ToTest = "D" Or Col2ToTest = "E" Or Col2ToTest = "F" Or Col2ToTest = "G" Or _
Col2ToTest = "H" Or Col2ToTest = "I" Or Col2ToTest = "J" Or Col2ToTest = "K" Or _
Col2ToTest = "L" Or Col2ToTest = "M" Or Col2ToTest = "N" Or Col2ToTest = "O" Or _
Col2ToTest = "P" Or Col2ToTest = "Q" Or Col2ToTest = "R" Or Col2ToTest = "S"
MsgBox ("Your response is not valid, try again")
Col2ToTest = InputBox("Choose the second column (ie. If the first if you want to test column B against C, Type 'B'")
Loop


Dim Col1Num As Integer, Col2Num As Integer

'Assign numeric values to represent column letters for column 1
If Col1ToTest = "A" Then
Col1Num = 1 + 1
End If
If Col1ToTest = "B" Then
Col1Num = 2 + 1
End If
If Col1ToTest = "C" Then
Col1Num = 3 + 1
End If
If Col1ToTest = "D" Then
Col1Num = 4 + 1
End If
If Col1ToTest = "E" Then
Col1Num = 5 + 1
End If
If Col1ToTest = "F" Then
Col1Num = 6 + 1
End If
If Col1ToTest = "G" Then
Col1Num = 7 + 1
End If
If Col1ToTest = "H" Then
Col1Num = 8 + 1
End If
If Col1ToTest = "I" Then
Col1Num = 9 + 1
End If
If Col1ToTest = "J" Then
Col1Num = 10 + 1
End If
If Col1ToTest = "K" Then
Col1Num = 11 + 1
End If
If Col1ToTest = "L" Then
Col1Num = 12 + 1
End If
If Col1ToTest = "M" Then
Col1Num = 13 + 1
End If
If Col1ToTest = "N" Then
Col1Num = 14 + 1
End If
If Col1ToTest = "O" Then
Col1Num = 15 + 1
End If
If Col1ToTest = "P" Then
Col1Num = 16 + 1
End If
If Col1ToTest = "Q" Then
Col1Num = 17 + 1
End If
If Col1ToTest = "R" Then
Col1Num = 18 + 1
End If
If Col1ToTest = "S" Then
Col1Num = 19 + 1
End If

'Assign numeric values to represent column letters for column 2
If Col2ToTest = "A" Then
Col2Num = 1 + 1
End If
If Col2ToTest = "B" Then
Col2Num = 2 + 1
End If
If Col2ToTest = "C" Then
Col2Num = 3 + 1
End If
If Col2ToTest = "D" Then
Col2Num = 4 + 1
End If
If Col2ToTest = "E" Then
Col2Num = 5 + 1
End If
If Col2ToTest = "F" Then
Col2Num = 6 + 1
End If
If Col2ToTest = "G" Then
Col2Num = 7 + 1
End If
If Col2ToTest = "H" Then
Col2Num = 8 + 1
End If
If Col2ToTest = "I" Then
Col2Num = 9 + 1
End If
If Col2ToTest = "J" Then
Col2Num = 10 + 1
End If
If Col2ToTest = "K" Then
Col2Num = 11 + 1
End If
If Col2ToTest = "L" Then
Col2Num = 12 + 1
End If
If Col2ToTest = "M" Then
Col2Num = 13 + 1
End If
If Col2ToTest = "N" Then
Col2Num = 14 + 1
End If
If Col2ToTest = "O" Then
Col2Num = 15 + 1
End If
If Col2ToTest = "P" Then
Col2Num = 16 + 1
End If
If Col2ToTest = "Q" Then
Col2Num = 17 + 1
End If
If Col2ToTest = "R" Then
Col2Num = 18 + 1
End If
If Col2ToTest = "S" Then
Col2Num = 19 + 1
End If

'************CAUTION: Might be made obsolete by previous statements***********

'Step 1: Define Variables.

'Variables that contain the letters A, B, C, D,....etc.
numofletters = columnCount - 1
Dim aa As Integer
Dim LetterA As String, LetterB As String, LetterC As String
Dim LetterD As String, LetterE As String, LetterF As String
Dim LetterG As String, LetterH As String, LetterI As String
Dim LetterJ As String, LetterK As String, LetterL As String
Dim LetterM As String, LetterN As String, LetterP As String
Dim LetterQ As String, LetterR As String, LetterS As String

For aa = 2 To numofletters
If aa = 2 Then
LetterA = FormatRange.Cells(4, aa).Text
End If
If aa = 3 Then
LetterB = FormatRange.Cells(4, aa).Text
End If
If aa = 4 Then
LetterC = FormatRange.Cells(4, aa).Text
End If
If aa = 5 Then
LetterD = FormatRange.Cells(4, aa).Text
End If
If aa = 6 Then
LetterE = FormatRange.Cells(4, aa).Text
End If
If aa = 7 Then
LetterF = FormatRange.Cells(4, aa).Text
End If
If aa = 8 Then
LetterG = FormatRange.Cells(4, aa).Text
End If
If aa = 9 Then
LetterH = FormatRange.Cells(4, aa).Text
End If
If aa = 10 Then
LetterI = FormatRange.Cells(4, aa).Text
End If
If aa = 11 Then
LetterJ = FormatRange.Cells(4, aa).Text
End If
If aa = 13 Then
LetterK = FormatRange.Cells(4, aa).Text
End If
If aa = 14 Then
LetterL = FormatRange.Cells(4, aa).Text
End If
If aa = 15 Then
LetterM = FormatRange.Cells(4, aa).Text
End If
If aa = 16 Then
LetterN = FormatRange.Cells(4, aa).Text
End If
If aa = 17 Then
LetterO = FormatRange.Cells(4, aa).Text
End If
If aa = 18 Then
LetterP = FormatRange.Cells(4, aa).Text
End If
If aa = 19 Then
LetterQ = FormatRange.Cells(4, aa).Text
End If
Next aa


'**************SIGNIFICANCE TESTING: BANNER 1********************


Dim i As Integer
For i = 7 To rowCount

'if cell contains dash, treat as 0
Dim Catchdash As Variant
FormatRange.Cells(i, Col1Num).Select
Catchdash = Selection.Value
If Catchdash = "-" Then
x1 = 0
Else
x1 = Selection.Value
End If

Dim Catchdash2 As Variant
FormatRange.Cells(i, Col2Num).Select
Catchdash = Selection.Value
If Catchdash = "-" Then
x2 = 0
Else
x2 = Selection.Value
End If


FormatRange.Cells(5, Col1Num).Select
n1 = Selection.Value


FormatRange.Cells(5, Col2Num).Select
n2 = Selection.Value


x1 = x1 * 0.01
x2 = x2 * 0.01


x1Temp = x1 * (1 - x1)
x2Temp = x2 * (1 - x2)


NewTemp = (x1Temp / n1) + (x2Temp / n2)
sqrtNewTemp = NewTemp ^ (1 / 2)

TValue = Abs(x1 - x2) / sqrtNewTemp

Dim Significant As Boolean
Significant = TValue > 1.96

If Significant = True Then
FormatRange.Cells(i, Col1Num).Select
With Selection.Font
.FontStyle = "Bold"
End With

If Col2Num = 2 Then
Selection.NumberFormat = "0" & LetterA
End If

If Col2Num = 3 Then
Selection.NumberFormat = "0" & LetterB
End If


If Col2Num = 4 Then
Selection.NumberFormat = "0" & LetterC
End If

If Col2Num = 5 Then
Selection.NumberFormat = "0" & LetterD
End If
If Col2Num = 6 Then
Selection.NumberFormat = "0" & LetterE
End If
If Col2Num = 7 Then
Selection.NumberFormat = "0" & LetterF
End If
If Col2Num = 8 Then
Selection.NumberFormat = "0" & LetterG
End If
If Col2Num = 9 Then
Selection.NumberFormat = "0" & LetterH
End If
If Col2Num = 10 Then
Selection.NumberFormat = "0" & LetterI
End If
If Col2Num = 11 Then
Selection.NumberFormat = "0" & LetterJ
End If
If Col2Num = 12 Then
Selection.NumberFormat = "0" & LetterK
End If
If Col2Num = 13 Then
Selection.NumberFormat = "0" & LetterL
End If
If Col2Num = 14 Then
Selection.NumberFormat = "0" & LetterM
End If
If Col2Num = 15 Then
Selection.NumberFormat = "0" & LetterN
End If
If Col2Num = 16 Then
Selection.NumberFormat = "0" & LetterO
End If
If Col2Num = 17 Then
Selection.NumberFormat = "0" & LetterP
End If
If Col2Num = 18 Then
Selection.NumberFormat = "0" & LetterQ
End If
If Col2Num = 19 Then
Selection.NumberFormat = "0" & LetterR
End If
If Col2Num = 20 Then
Selection.NumberFormat = "0" & LetterS
End If

End If
Next i

anotherSigtest = InputBox("Do you want to do anymore significance testing? 'y' = yes")
Loop While anotherSigtest = "y"
End If 'end of of statement for significance testing

 

[VBA_Newbie]

As ridiculous as my code looks, for some reason it actually works. But when I say 'y' I'd like to do another significance test. If I choose a column I've already chosen it will write over the letter.

 

[martinee]

I've run out of time to analyze your full code, but I've hopefully simplified the first several lines for you. Check it out to see if you can make sense of it:

Sub significance()
Dim col1totest As Range, col2totest As Range

Do
ans = MsgBox("Would you like to run significance testing?", _
            vbYesNo, "Test")
If ans = vbNo Then Exit Sub

Do
Set col1totest = _
    Application.InputBox("Please select the first column" & _
        vbCrLf & "to test.", "First Column To Test", Type:=8)
Loop While col1totest.Column > 19
    
Do
Set col2totest = _
    Application.InputBox("Please select the second column" & _
        vbCrLf & "to test.", "Second Column To Test", Type:=8)
Loop While col2totest.Column > 19

'**********Your Code Goes Here******************8
MsgBox ("Tested Column " & Mid(col1totest.Address, 2, 1) & _
    " against " & Mid(col2totest.Address, 2, 1) & ".")
   
Loop While ans = vbYes
End Sub

HTH

 

[VBA_Newbie]

I haven't tested it out, I'll have to do that tomorrow. But thanks for helping me out, I certainly do appreciate it.