Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code not reliable
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code not reliable
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. -- HTH, Barb Reinhardt "HH" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code not reliable
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" wrote in message ... 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. -- HTH, Barb Reinhardt "HH" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code not reliable
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 -- HTH, Barb Reinhardt "HH" wrote: 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" wrote in message ... 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. -- HTH, Barb Reinhardt "HH" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code not reliable
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")? -- Gary "HH" wrote in message .. . 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code not reliable
That made it work!
Many Thanks Barb Hank "Barb Reinhardt" wrote in message ... 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 -- HTH, Barb Reinhardt "HH" wrote: 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" wrote in message ... 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. -- HTH, Barb Reinhardt "HH" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Code not reliable
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. -- HTH, Barb Reinhardt "Gary Keramidas" wrote: 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")? -- Gary "HH" wrote in message .. . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dir function to check for file not reliable | Excel Programming | |||
Is Excel reliable | Excel Discussion (Misc queries) | |||
Excel 97 VBA - CURDIR, not reliable | Excel Programming | |||
FAST, RELIABLE, LEGAL MONEY MAKING OPPORTUNITY | Excel Programming | |||
excel 2000 workbook.activate not 100% reliable with alt+tab | Excel Programming |