Macro Code not reliable

[HH]

I use the following code behind a command button to populate a "SignOutLog"
worksheet from about 35 worksheets in a workbook containing about 45
worksheets:
Sub SignOutLog()
'
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
'

'
Sheets("SignOutLog").Select
' Clear the existing values (if any)
Range("$A$2:$p$60").Value = " "
' J tracks the row number on the summary page
' I tracks the sheet number being processed
j = 2
For I = 2 To Sheets.Count
a$ = Sheets(I).Name
' Don't process a sheet if its name Other than "SignOutLog"
' or if the name is blank.
If (a$ = "Birthday") Then GoTo 10
If (a$ = "DepositRecord") Then GoTo 10
If (a$ = "MailLabels") Then GoTo 10
If (a$ = "PmtSummary") Then GoTo 10
If (a$ = "Templat") Then GoTo 10
If (a$ = "ID") Then GoTo 10
If (a$ = "SignOutLog") Then GoTo 10
If (a$ = "DepositRecord") Then GoTo 10
If (a$ = "Photos") Then GoTo 10
If (a$ = "Volunteers") Then GoTo 10
If (Sheets(a$).Range("$C$1").Value = "") Then GoTo 10
' Process the current sheet
Range("g" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R6C3"
Range("e" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R6C4"
Range("f" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R6C14"
Range("k" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R9C9"
Range("h" + Format(j)).FormulaR1C1 = "='" + a$ + "'!R44C11"

j = j + 1
10 Next I
End Sub

The code has always worked great until I added the last line --- Range("h"
+ Format(j)).FormulaR1C1 = "='" + a$ + "'!R44C11" --

Now when I run the macro I get the formula in the cell rather than the
value. To be more precise, I get the formula the second time I run the
code. The first time I push the command button the code runs great. I have
been able to get the code to run properly by formatting the column to
"general," but still the second time I run the code the formula appears in
the cells rather than the value.

Only the new column is doing this. The other 4 columns are displaying the
values each time I run the code.

Very confusing.... Where Have I Gone Wrong??

Hank

 

[Barb Reinhardt]

I'm not sure of the issue, but I cleaned up some of your code. I've not
used a$ for a variable and couldn't seem to dimension it, so changed it to
myName. Also note that I didn't use SELECT once.

Option Explicit
Sub SignOutLog()
'
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
'

'
Dim myWS As Worksheet
Dim myWB As Workbook
Dim j As Long
Dim WS As Worksheet
Dim myName As String

Set myWB = ThisWorkbook
Set myWS = myWB.Worksheets("SignOutLog")
' Clear the existing values (if any)
myWS.Range("$a$2:$p$60").ClearContents 'Why make it a space. Why not
clear
' J tracks the row number on the summary page
' I tracks the sheet number being processed
j = 2
For Each WS In myWB.Worksheets
If WS.Name <> myWS.Name Then
myName = WS.Name
' Don't process a sheet if its name Other than "SignOutLog"
' or if the name is blank.
If Not (myName = "Birthday") And _
Not (myName = "DepositRecord") And _
Not (myName = "MailLabels") And _
Not (myName = "PmtSummary") And _
Not (myName = "Templat") And _
Not (myName = "ID") And _
Not (myName = "SignOutLog") And _
Not (myName = "DepositRecord") And _
Not (myName = "Photos") And _
Not (myName = "Volunteers") And _
Not (Sheets(myName).Range("$C$1").Value = "") Then
' Process the current sheet
Debug.Print Format(j)
myWS.Range("g" + Format(j)).FormulaR1C1 = "='" + myName +
"'!R6C3"
myWS.Range("e" + Format(j)).FormulaR1C1 = "='" + myName +
"'!R6C4"
myWS.Range("f" + Format(j)).FormulaR1C1 = "='" + myName +
"'!R6C14"
myWS.Range("k" + Format(j)).FormulaR1C1 = "='" + myName +
"'!R9C9"
myWS.Range("h" + Format(j)).FormulaR1C1 = "='" + myName +
"'!R44C11"

j = j + 1
End If
End If
Next WS
End Sub


Note that I didn't use SELECT once. I do have a bit of an aversion to
testing against worksheet names because users like to change them far too
frequently. If this is your workbook, you know what will happen. If I were
to distribute this, I'd use worksheet code names. If you want more
information on that, come back and ask.

 

[HH]

Barb,
Your cleaned up code works great also.
From all I have learned, it appears the real problem is in the column
format.
When I run the code the First time - it works great. When I run it again -
the column results shows the formula rather than the value. I can select
the column, format it to "General", run the code again and the value shows.
The next time I run it the formula shows. If I select the column to format
the "General" selection is no longer highlighted - as it is in the other
columns.
Any idea what's going on here?
Hank

 

[Barb Reinhardt]

I think I'd put some debug,print code in to test the format for each cell

debug.print myWS.Range("g" + Format(j)).address, _
myWS.Range("g" + Format(j)).NumberFormat

 

[Gary Keramidas]

i may be missing something, but why would you test for all of the sheets if
you're only going to run code on 1 of them ("SignOutLog")?

 

[HH]

That made it work!

Many Thanks Barb

Hank

I think I'd put some debug,print code in to test the format for each cell

debug.print myWS.Range("g" + Format(j)).address, _
myWS.Range("g" + Format(j)).NumberFormat

 

[Barb Reinhardt]

I think he's pulling data from several sheets. It might make sense just to
check for those sheets, rather than the ones that it's not.