PDA

View Full Version : Sleeper: Circular reference in a macro



justakid
09-25-2005, 03:31 PM
HI Im new here and a bit of a novice when it comes to macros in excel.

i have a strange problem...
I created a macro for a report that I do every month however the data slightly changes (obviously!!).

Some months there are some departments and other times there isnt.
The macro that I have created pulls the raw data from the system and then seperates each department into seperate tabs and then puts totals at the bottom of the sheet.

The macro works great if there is data for all the departments, however when there is no data for a department the macro crashes and I get a circular reference.

any suggestions?

thanks
Samuel

Bob Phillips
09-25-2005, 03:54 PM
Post the code?

johnske
09-25-2005, 03:55 PM
Hi Samuel,

Welcome to VBAX.

The macro requires an error handling procedure in it. We'd need to see the macro for that. Can you paste the code please so someone can assist you?

Regards,
John :)

justakid
09-25-2005, 04:13 PM
yeah sorry i should've posted the code when I started the thread


'Filtering Branded Thermals
Selection.AutoFilter Field:=1, Criteria1:="BT"
Range("A1").CurrentRegion.Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = "Branded Thermals"
Range("A1").Select
ActiveSheet.Paste
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.Select
Cells.EntireColumn.AutoFit
'Produce totals
Range("A1").CurrentRegion.Select
rnum = Selection.Rows.Count
Range(Cells(rnum + 1, 11), Cells(rnum + 1, 13)).FormulaR1C1 = "=SUM(R[-1]C:R[-" & rnum - 1 & "]C)"
Range("A1").Select
Range("N1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
Selection.Style = "Percent"
Range(Cells(rnum + 1, 11), Cells(rnum + 1, 14)).Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = True
Range("A2").Select

Bob Phillips
09-25-2005, 05:03 PM
One shot


Selection.AutoFilter Field:=1, Criteria1:="BT"
Range("A1").CurrentRegion.Copy
Sheets.Add
ActiveSheet.Name = "Branded Thermals"
Range("A1").Select
ActiveSheet.Paste
Rows("2:2").Select
ActiveWindow.FreezePanes = True
Cells.EntireColumn.AutoFit
'Produce totals
Range("A1").CurrentRegion.Select
If rnum > 1 Then
rnum = Selection.Rows.Count
Range(Cells(rnum + 1, 11), Cells(rnum + 1, 13)).FormulaR1C1 = "=SUM(R[-1]C:R[-" & rnum - 1 & "]C)"
With Range("N1").End(xlDown).Offset(1, 0)
.FormulaR1C1 = "=RC[-1]/RC[-2]"
.Style = "Percent"
End With
With Range(Cells(rnum + 1, 11), Cells(rnum + 1, 14))
With .Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
.Font.Bold
End With
End If

justakid
09-25-2005, 11:40 PM
XLD,
Thanks it works!!
One slight problems - The totals at the bottom of each tab doesnt work. Ive tied to fix it however I cant get it to work - your help will be greatly appreciated

thanks
Samuel

Bob Phillips
09-26-2005, 01:21 AM
One slight problems - The totals at the bottom of each tab doesnt work. Ive tied to fix it however I cant get it to work - your help will be greatly appreciated

In what way do they not work.

One problem I had was that I couldn't get the code to run, although I did get the circular reference, presumably because of data. Can you post a sample workbook as well as saying what is wrong?

justakid
09-26-2005, 03:25 PM
XLD,
I really appreciate your help with this
Have you got an email address that I can send you a copy of my workbook.
I have changed the data as it is work related and I dont think they would approve having data on the net!!

thanks
Samuel

Bob Phillips
09-26-2005, 04:24 PM
XLD,
I really appreciate your help with this
Have you got an email address that I can send you a copy of my workbook.
I have changed the data as it is work related and I dont think they would approve having data on the net!!

You can try

xld dot bob at gmail dot com (do the obvious with it)

but I don't know when I can get to it. I am going to the States tomorrow, so I will travelling all day, then I have meetings, but give all the details of the problem and what you are trying to do and I will try.

.

justakid
09-26-2005, 04:59 PM
XLD,

Thanks
I have emailed you the details
much appreciated
Samuel