Posts by Mark

Thank you for your reply,

The first thing I originally tried was to record a macro, but it does not write any vba for :-

selecting menu item=cf,

entering the Formula

selecting the formats

etc.

I just tried again, recording the macro with the VBA window open (ALT=F11)

and could see it writing code for normal actions like ...Range("D45").Select

but once I started selecting menu options, nothing got written to the new macro.

that is why I tried finding code examples on the internet, but I couldn't work out the syntax.

The code I found/worked out can work:-

eg:

Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=" & Selection.Cells(1).Address(False, False) & "=MIN(" & Selection.Address & ")")

does work (copied direct from i/n)

but my attempts (among many others) to write the formula I want, such as:

Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH("ISA",$A10)

or

Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH(""ISA"",$A10)"

get rejected

I have a pivot table whose source data is itself of variable size, and needs to be refreshed frequently.

This causes the pivot table to loose my 6 previous rules of conditional formatting, because the CF range sizes have changed.

I was thinking that if I could write the CF rules by macro then I could auto renew them as needed.

learning how to write CF rules by macro would help in many other workbooks & worksheets when my CF rules-ranges get jumbled up as I copy & paste.

Many of my CF rules use formula, which work, but which I have been unable to convert to VBA.

thanks for your time

I am trying to create multiple Conditional Format rules using VBA, but have been unable to work out the right syntax.

Using Excel itself I get the correct result as follows:-

> Home > Conditional Formating > Manage Rules > Edit Rule

>>> Rule type ..... formula

>>>Rule description ..... =SEARCH("ISA",$A10)

>>> formats are set

>>>Applies to ..... =$A$10:$Q$100

What is the right way to convert the Rule-description-foumula into VBA?

Thanks

This is the best I have been able to come up with:-

Sub formatting()
'Define the variables
Dim rng As Range
Dim condition1 As FormatCondition
Dim condition2 As FormatCondition


'Set/fix the range for conditional-formatting using the    VBA ‘Range’ function:
Sheets("Pivot-RB").Select
Set rng = Sheets("Pivot-RB").Range("A10:Q100")
rng.Select


'Delete/clear any existing conditional formatting (if any) from the range, using ‘FormatConditions.Delete’ :
rng.FormatConditions.Delete


'Now define and set the criteria and the formats
Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=" & Selection.Cells(1).Address(False, False) & "=MIN(" & Selection.Address & ")")
'fails  Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH("ISA",$A10)"
'fails  Set condition1 = rng.FormatConditions.Add(xlExpression, Formula1:="=SEARCH(""ISA"",$A10)"




With condition1
.Font.Color = vbBlue
.Font.Bold = True
.Interior.ColorIndex = 39
End With


With condition2
.Font.Color = vbRed
.Font.Bold = True
End With




End Sub

Re: vba__insert to cells =average() with variable range

Hi skywriter

Thank you for your code, I have tried it out and seen what it does. It is not quite what I needed;
however I continued to search and found another answer:-

ActiveCell.Offset(0, 1).FormulaR1C1 = "=AVERAGE(R[-3]C[0]:R[-09]C[0])"

this inserts a formula into the cell one column to the right of the ActiveCell
The formula then skips the two rows above (and in the same column),
and then selects the range of 7 rows upwards.

to get VBA to write the full Report Summary:-

' add some Report summaries


    ActiveCell.Offset(2, 10).Select
    
    ActiveCell.Offset(0, 0).Value = "(ignore 'Today' as incomplete) ..... 7-Day Average"
    ActiveCell.Offset(1, 0).Value = "28-Day Average"
    
    ActiveCell.Offset(0, 1).FormulaR1C1 = "=AVERAGE(R[-3]C[0]:R[-09]C[0])"
    ActiveCell.Offset(0, 2).FormulaR1C1 = "=AVERAGE(R[-3]C[0]:R[-09]C[0])"
    ActiveCell.Offset(1, 1).FormulaR1C1 = "=AVERAGE(R[-4]C[0]:R[-27]C[0])"
    ActiveCell.Offset(1, 2).FormulaR1C1 = "=AVERAGE(R[-4]C[0]:R[-27]C[0])"

Re: vba__insert to cells =average() with variable range

Quote from skywriter;799868

I would suggest you upload an actual workbook.

HI,
I have uploaded/attached a cut down version of the table

The whole table is actually created by macro from supporting sheets (not included)

my macro "Experimental_Report_Summary" prepares what I am aiming for; but the formulae at the bottom of the report are absolute references and do not alter as I add new rows. I want to write vba which will insert this summary with formulae that are relative to the bottom row of data.

my macro "Experimental_Report_Summary_2" shows some of my failed attempts.

any help much appreciated

forum.ozgrid.com/index.php?attachment/73832/

I have a Big table of data which adds a new row for each record (per Day).
At the bottom for some columns I want to add the formula for 7-day average and 28-day average to some columns.

the following code does what I want but does not adapt to the addition of new Rows of data

' Goto bottom of Report
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    Range("A1").Select
    ActiveCell.End(xlDown).Select
    
   
' add some Report summaries


    ActiveCell.Offset(2, 10).Select
    ActiveCell.Value = "(ignore 'Today' as incomplete) ..... 7-Day Average"
    ActiveCell.HorizontalAlignment = xlRight
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Value = "28-Day Average"
    ActiveCell.HorizontalAlignment = xlRight
    ActiveCell.Offset(-1, 1).Select
    Range(ActiveCell, ActiveCell.Offset(1, 1)).HorizontalAlignment = xlCenter
        
    ActiveCell.Formula = "=average(L261:L255)"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Formula = "=average(L261:L233)"
    ActiveCell.Offset(-1, 1).Select
    ActiveCell.Formula = "=average(M261:M255)"
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Formula = "=average(M261:M233)"

I have researched and experimented with various alternatives to define the ranges to be averaged

Sub zzz()
'ActiveCell.Formula = "=average(" & Range(ActiveCell.Offset(-3, 0), ActiveCell.Offset(-10, 0)) & ")"
'ActiveCell.Formula = "=average(" & ActiveCell.Offset(-3, 0) & ":" & ActiveCell.Offset(-10, 0) & ")"
'ActiveCell.Formula = "=average(" & Range(Cells.Offset(-3, 0), Cells.Offset(-10, 0)).Address(False, False) & ")"




Dim rng7 As Range
Set rng7 = Range(ActiveCell.Offset(-3, 0), ActiveCell.Offset(-10, 0))
ActiveCell.Formula = "=average(" & rng7 & ")"


 
' ActiveCell.Offset(0, 0).Value = WorksheetFunction.average(Range(ActiveCell.Offset(-3, 0).Address & ":" & ActiveCell.Offset(-10, 0).Address))
    


End Sub

but am unable to work out the proper syntax to define the ranges to be averaged so that they are not absolute addresses.

regards
Mark[ATTACH=CONFIG]73827[/ATTACH]

Re: Jump to Formula-source-cell

Quote from soulfire21;662740

You can do this:
=HYPERLINK("[Book1]Sheet2!M1","Go To M1")

The Book1 references the Workbook you'd like to go on, Sheet2 the sheet, and M1 the cell. The text "Go To M1" appears in the cell that contains the HYPERLINK formula.

You must include the entire reference within quotations, for example: "[Workbook1]Sheet1!A1" will jump to Workbook1, Sheet1, Cell A1 when clicked.

Thank you for your reply,

If I use this method I think I need to separately create each hyperlink, which is what I do at the moment.

Is there a way to generalise the formula?
I would like the hyperlink to goto a location based on the formula in the cell to the Left/Right/Up/Down of the hyperlink-cell

Regards

Re: Jump to Formula-source-cell

Quote from cytop;662611

Try, in the DoubleClick event handler:

Code

.Goto Sheets(Mid(v(0), 3, Len(v(0)) - 3)).Range(v(1))

Thank you,

This works with my example sheet, and shows that what I am looking for can be done

However this particular code appears to only work with the particular worksheet names in my example workbook. I am hoping that there is a way to generalise the code, so that I can copy it from one worksheet to another and into other workbooks which have different sheet names.

It would be so clever to be able to double-click a formula and then jump to the data-source of the formula. Any further ideas welcomed.

meanwhile as a different way to achieve something similar:
I have looked at the =hyperlink function
and it seems to me that I should be able to write a hyperlink which referenced a formula in a different cell, and would jump to the data source of the different cell ....

eg
if cell A1 is ... ='. (DifferentSheet)'!M1
and cell B2 is something like ... =hyperlink(___A1___,"linkOfUp")

I would like to be able to
* click the hyperlink in B2, and jump to cell M1 on DifferentSheet
* copy & Paste the formula in cell B2 and for the pasted cell to always create a hyperlink to the data-source referenced in the cell above

what is the real formula that I need in cell B2?

Thank you for your attention
Regards
Mark

Re: Jump to Formula-source-cell

Hello Stephen

THANK you for your reply. I like very much the way it begins when I double click on a cell.

Unfortunately I have not been able to make it work properly as it crashes at line "goto sheets" with an "run time error 9"

I attach a cut-down version of my much bigger spreadsheet, as an example of what I am trying to achieve .... On the summary sheet I would like to be able to select a cell, then jump to the data source. Is there something in the way I have designed my data and worksheet names which is causing your suggested code to fail?

Thanks for your attention
Mark

Re: Jump to Formula-source-cell

Quote from StephenR;662105

Have you considered Trace Precedents in the formula menu?

Thank you for your reply....
Yes I had considered Trace Prededents, but as the formulas refer to cells within other worksheets, all I get is a line-&-little-Grid symbol.

I can't seem to click onto the grid to jump to the source data ... am I supposed to be able to?

Also; I have scores of inter-worksheet-formulae that I want to jump between. After a few jumps Tracing precedents would clutter the display with blue lines

any other suggestions?

Regards
MArk

Hello,

I have spent today searching and have seen LOTS of messages about hyperlinks, but I have not been able to understand enough to adapt/create my own answers

I have a workbook with many sheets, an each sheet there are several/many formulae which dispaly values from other sheets within the same workbook.
eg =WeightHistory!B189
In a cell next to the formula I have manually added hyperlinks to the formula's-data-source.

The workbook is now cluttered with my hyperlinks; which also break down whenever I add a new row or column.
I have been reading about Dynamic-hyperlinking, but have not been able to get it to work;
Perhaps hyperlinking is a wrong approach?

I am looking for a way to:-
Click on the cell containing the formula, and then auto-jump to the source-data of the cell/formula?

or to:
select a cell; and run a macro shortcut which would check the formula within the active cell and then jump to the source-data.

or to:
create a hyperlink formula which would read the formula of the cell next to it, and provide a hyperlink to that formula's-data-source

can anyone help?

(Is is possible to add a macro-shortcut to the mouse's right-button-click menu?)

Thank
Mark

Re: highlight current cell with or without conditional formating

HI
Thanks for your previous message:

Quote from Aaron Blood;256957

One last thought then...

Rather than change the background of the cell, just have your macro select the entire row but keep the activecell active within the range selection.

Essentially by selecting the entire row you're highlighting it. Doesn't change any formats and would provide similar functionality.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Target.EntireRow.Select
    Target.Activate
End Sub

Display More

I know that it was posted 4 1/2 years ago, but it has just worked for me

I have a spreadsheet with many conditional formats and after several hours checking out other "solutions" only your idea is working for me ... thanks.

Ignorant question ... Do I have to post the code into each VBAProject / microsoft excel object > sheet#(??)
As I would like the feature to work on ALL sheets is there a place to store it once only, and for it to apply to all sheets?

Thanks
Mark

Re: Test if cell contains integer

Hello cytop,

Thanks again for such a fast reply.
Using your previous reply I have now achieved the conditional formatting which I was aiming for:-
Starting with cell A295 if I add conditional formating rule :-
Rule: =INT($A295)<>$A295
Applies to: =$A$295:$B$312

Then I can copy the format to range A295:b312
meaning that the formatting for each row is dependent on the contents of column A for that row (ie is it an integer?)

**Thanks for my solution.**

As for Check_cell, I have had a look at the link and the information is well beyond my knowledge level (sad smile) However I plan to experiment with the methods & properties later on, so thanks for that referal also.

Regards
Mark

Re: Test if cell contains integer

Thanks Rob & Cytop, for the fast answers

I will give these a try.

However as a more general issue ... would it be possible to create other tests such as:-
Function IsInt(Check_Cell As Range)
IsFormula = Check_Cell.HasInteger
End Function

What I don't know about is, what options can I use with Check_Cell.Has??? to check for other stuff.

does this make sense?
Thanks

Hi

I have recently found the very helpful =isFunction UDF
at http://www.ozgrid.com/VBA/ExcelIsFormula.htm
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function
which I dont understand but can use nicely to do very useful conditional formatting.

I have done 3 hours of looking (F1 help, search ozgrid / search google etc) without getting very far, enough to think that it ought to be possible to create other types of cellchecking.

Is it possible to create other 'cellchecking' functions? what are they? can I find a list somewhere?

specifically can I create a function which checks if a cell's contents are an integer (rather than a decimal)?

Thanks...

Re: Enhanced Workbook Index

Hello Norie

Thanks again for your code improvements. I have studied it and fiddled around with it to make the output look formated in a way I like. It works well for me.

For the sake of anyone else who may be interested I list the final version below: (I expect the code changes are cumbersome and basic, but they get done what I was looking for)

regards
Mark

Private Sub Worksheet_Activate()
    Dim wSheet As Worksheet
    Dim i As Long
    i = 1
     
    'On Index page, Format the Colum-A title "Index"
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
        .Cells(i, 1).Font.Size = 20
        .Cells(i, 1).ColumnWidth = 25
        .Cells(i, 1).Interior.ColorIndex = 54
    End With
     
    'On Index page, Set column-titles and formats for columns B&C
    [B1] = "Title"
    [C1] = "Description"
    With Range("B1:C1")
        .Font.Size = 14
        .Interior.ColorIndex = 54
    End With
    
    
    
    ' On each worksheet, Add [back-to-index] in cell A1
    '( does not work for  graph/chart-sheets)
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            i = i + 1
            With wSheet
                .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                SubAddress:="Index", TextToDisplay:="[Back-to-Index]"
                With .Range("A1")
                    .Name = "Start" & wSheet.Index
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .Font.Size = 8
                    .Font.Color = vbBlue
                    .Interior.ColorIndex = 54
                End With
            End With
    
            ' On Index sheet, pick up titles/descriptions from source sheet
            With Me
             ' Column A contents = hyperlink
                .Hyperlinks.Add Anchor:=Me.Cells(i, 1), Address:="", _
                SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
                ' On Index page, Column-A format cells
                With Me.Cells(i, 1)
                    .Font.Size = 10
                    .ColumnWidth = 25
                End With
                ' On Index page, link Column-B&C to source pages & format cells
                With Me.Cells(i, 1)
                    .Offset(0, 1).Formula = "='" & wSheet.Name & "'!B2"
                    .Offset(0, 2).Formula = "='" & wSheet.Name & "'!B3"
                    .Offset(0, 1).Font.Size = 8
                    .Offset(0, 2).Font.Size = 8
                End With
            End With
        
        End If
    Next wSheet
     
End Sub

Re: Enhanced Workbook Index

Sorry for the delay in checking back to this forum over Christmas.

Thanks for your reply, I will try it out when I next get home (probably next week) and let you know.

again
Thanks.
Mark

Hello

Is anyone kind enough (clever enough?) to help me change the following code which I downloaded from this forum a few weeks ago.

I have tried experimenting, but as I have no training as a programmer I am getting it wrong.

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1


    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="[Back-to-Index]"
                
                End With
                
                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
                
                
                
        End If
    Next wSheet
    
  


End Sub

This super, short code creates an index page of all the worksheets within a workbook

[1] One each existing workbook
I would like the code to format the [Back to Index] text that it puts in cell A1 on each sheet. For example to
Align vertical = centre
Align horizontal = centre
Font Sixe = 8
Backgroung colour = beige
text colour = blue

[2] The current index page lists all worksheets in column A

[2a]
I would like the code to format the cells. For Example
font size = 18
column width = 25

[2b]
It would be just spiffing if I could get the cell to toggle/change background colour then the mouse cursor went over it

[2c]
And the one I have really struggled with ...
I would like the code to add new data in columns B & C.

Specifically:
* each time the code puts a worksheet name/link into column A
* I would like the code to move to column B and input a formula which picks up the contents of cell B2 from the worksheet which it has just listed in column A
* then I would like the code to move to column C and input a formula which picks up the contents of cell B3 from the worksheet which it has just listed in column A

----------
The idea is to build a index page which picks up title and description information from eah of the worksheest in the workbook.

I think this would make a really neat index page, and I am hoping that someone clever thinks so too

Thanks for your attention
Regards
Mark

Re: define range anchor points within a formula by cell values

Thank you Andy

The =match formula has done the trick.

I can now just type in 2 dates and find the distribution of sales by weekday between them.

This is exactly what I was aiming for when I started, so Thanks Again.

regards
Mark

Re: define range anchor points within a formula by cell values

Superb!!!

With a bit of tweeking I put in a middle step so that all I need to do is enter the start and end row values into two cells, and the whole graph is updated. Then by liberal use of range names the formulae become virtually understandable:-)

to wit:
step1 - name two cells StartCurrent & EndCurrent

step2
*** calculate bigCompare range and display by string formula
...="$B"&+StartCurrrent&+":$B"&+EndCurrent to give ... $B467:$B832

*** calculate SumifRanges for each column and display by string formula
... ="$D"&+StartCurrrent&+":$D"&+EndCurrent to give ... $D467:$D832
... ="$E"&+StartCurrrent&+":$E"&+EndCurrent to give ... $E467:$E832

(I'm sure I can now get rid of absolutes too)

Step3
I now have the 'text' decsription I need for the INDIRECT function which you showed me

and I end up with nice graph-data formulae such as
=COUNTIF(INDIRECT(BigCompare),B843)
=SUMIF(INDIRECT(BigCompare),B845,INDIRECT(SumifColD))
=SUMIF(INDIRECT(BigCompare),B845,INDIRECT(SumifColE))

This technique would have bee soooo useful in the past, thanks for the tuition.

Quote

Post back if you need more help.

A rash offer!

Actually yes,there is one more refinement I would like.
I would like to be able to type in the relevant start & end dates and then for the StartCurrent & EndCurrent cells cells to look up the row number in the source data table.
any ideas?

I attach my revised version in case you are intrigued to see what I've done with your help.

regards