Posts by fifijazz

This isn't a very elegant solution, but perhaps it will work for you. Modify the the code below to suit your worksheet. Set link updating to manual. Run the code below. Changed cells will now be red. Create a second macro to delete the old stuff in column AA and reformat the formula cells like the rest of your workbook when you are through.

For Each targ In Sheets("sheet1").UsedRange.SpecialCells(xlCellTypeFormulas, 23)
If InStr(2, targ.Formula, "[") Then
Range("AA" & targ.Row) = targ.Value
targ.FormatConditions.Add Type:=xlExpression, Formula1:="=" & targ.Address & "<>" & "AA" & targ.Row
With targ.FormatConditions(1).Font
.Bold = True
.ColorIndex = 3
End With
End If
Next
End Sub

You are endangering your data if you use Excel in this way. I would strongly suggest you use a database application instead.

I think you will need to use ActiveX controls from the Control toolbox. You are currently using controls from the Forms toolbar. ActiveX controls have a Visible property that you can set to True or False. You can also set the Enabled property of the control to True or False.

I'm off to a meeting, but if you have trouble getting started, I'm sure someone else will help you. You may need to describe in more detail what you are trying to do.

If I am understanding correctly what you are trying to do, this should work. If the number you want to input is in cell G1, you would:

1. Create a dynamic named range for the column in which your X-axis values are. Let's assume they are in column A. We'll use the name "dyna". The formula for the name will be =offset($A$1,0,0, $g$1,1).

2. In your chart Source Data, set the X-axis range to =Sheet1!dyna
(You have to enter the sheet name or workbookname followed by an apostrophe.)

Take a look at the Shell function and AppActivate statement in VBA help. This will get you where you want to go.

hth

You can Alderaic's code in a new workbook in an auto_open sub, then use Windows task scheduler to open the new workbook at a certain time each night if you want this to happen when you aren't working.

You don't have to activate an add-in to use SUMIF. At least not in XP or 2000.

or this:
=SUMPRODUCT(((A2:A7="dog")+(A2:A7="cat")+(A2:A7="emu"))*(B2:B7))

activesheet.usedrange.rows.count, or
Range("a60000").End(xlUp).Row is probably better

Here you go. This will handle any future changes to the workbook and add comments only if the cell contains a forumula. If you already have formulas in the workbook you would like to comment, modify this code to loop through them and add the comments initially.

hth

Private Sub worksheet_change(ByVal Target As Range)






Dim comtxt
'
On Error Resume Next


     If TypeName(Target) = "Range" And Target.Count = 1 And Target.HasFormula = True Then _


    ActiveWindow.DisplayFormulas = True
    comtxt = Target.Formula
   Target.Comment.Delete
    Target.AddComment (comtxt)
    ActiveWindow.DisplayFormulas = False
    Application.CommandBars("Formula Auditing").Visible = False


End If




End Sub

try something like:

loopcount = inputbox("Enter the number of times to execute")
for i = 1 to loopcount
<do whatever>
next

For starters, press End, Home to find the last cell of your used range. If it is below and/or to the right of your data, delete ALL of the blank rows and columns in your worksheet, then save the file. Also eliminate any special formatting you don't really need. And yes, you really really ought to transfer this data to Access!

hth

The code below will work for a specific workbook. It must be copied into ThisWorkbook, not into a standard module. If you really want to set the orientation to landscape for every new workbook's current and future sheets, have a look at the NewWorkbook event of the Application object. In addition to setting the orientation for the existing sheets in the new workbook, your code would also have to copy the procedure below into the new workbook. Or at any rate, that's the only way I know of to do it. Hope this gets you started.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
   Sh.PageSetup.Orientation = xlLandscape
End Sub

More information is needed. Are these select queries or action queries? If they are action queries, what do they do? Do you want to return the data to Excel, or are you using Access by itself?

Doing this is not going to delete anything. If you filter in place, it will hide the rows that are true duplicates. If you copy to another location, it copies only the unique rows but still will not delete anything from your matster list.

If your list shows robbie williams - angels listed twice, filtering will hide one of those rows. robbie williams - gremlins would not be hidden, because it is not exactly the same as robbie williams - angels.

Are you just trying to sort the rows, or are you wanting to end up with all the values in one cell? If you are just wanting to sort the rows, take a look at "Sort a list" in Excel help. If your rows don't sort correctly, the cells probably contain text, not numbers--even though they might LOOK like numbers.

The easiest way is to click on any cell in your list. Then go to Data>Filter>Advanced Filter. Either filter in place, or copy to another location, as you choose. Be sure to check the "Unique Records only" box at the bottom of the dialog box.

Can you be more specific? There must be something about the rows that allows you to identify that you don't need them. Or, something about the rows you want to keep that differentiates them from the others.

Worked okay for me when I tested it with the little sub below on a worksheet that contained formulas linked to the referenced workbook.

Set ws = ActiveSheet
    
        With ws.Range("a2:a12")
            Set linkString = .Find("[AttainmentDB.xls]Database!", LookIn:=xlFormulas)
            If Not linkString Is Nothing Then
                MsgBox ("Link found in " & ws.Name)
             End If
        End With

Can you post the code you used when you tried it out? If you copied and pasted from the snippet I posted, could it be that I mis-typed the substring that you are looking for in your linked formulas?

Hope this snippet will help you get started.

Set linkString = .Find("FY05REPS", LookIn:=xlFormulas)