Posts by p45cal

Re: Autofilter Copy Blank Row

re:"note: you mentioned that you found 'Sheet1!_FilterDatabase', and 'Current!_FilterDatabase' , could you tell me where this was so that I might read it as well?"

I Googled and found
http://msdn2.microsoft.com/en-…/aa140046(office.10).aspx

p45cal

Re: Autofilter Copy Blank Row

Maybe I too should have gone down the complete rewrite road, however I came across an interesting nugget or two about Autofilter while researching the answer. Specifically that Excel creates a hidden named range in the workbook when AutoFilter is on. It's called 'Sheet1!_FilterDatabase', or in this case 'Current!_FilterDatabase'. I didn't use it in the end.

See attached file. It seems to work for whether there are 0, 1 (your code had some problems with 1) or any number of 'New Entry' rows.

p45cal

Re: "next Command" Not Going Past Row 19

Yes, put this in the Immediate pane of the VBE and see if it comes back with what you expect..
?sheet3.Name

Results might be a bit more predicatable with
mLastRow = Sheets("TheSheetsName AsOnTheTab").Cells(Rows.Count, "I").End(xlUp).Row

mLastRow = Sheet3.Cells(Rows.Count, "I").End(xlUp).Row

As for a better way to do it? Well here's a different way to do it, no vba:

This is for row 20, you can copy up/down:
In any column in row 20 place this formula:

=SUMPRODUCT(--({"Critical";"Serious";"Moderate";"Minor"}=I20),{10;8;5;1})*SUMPRODUCT(--({"High";"Medium";"Low"}=J20),{10;5;1})

Note that it refers to I20 and J20. This will give the same result of the multiplication as yours. Copy the formula up/down as required (and yes, you're right, this doesn't automatically find how far down to go).

Next format all those cells with a green background.

Finally in whichever cell you've chosen to put the formula you can add some conditional formatting. See the attached cf1 graphic. Alternatively, to be able to apply the conditional formatting to other cells, for example to colour the cells containing the words themselves see graphic cf2 attached which was looking at the value in cell, but could be applied to any cell, most usefully, in the same row.

Finally, without using any extra cells containing formulae, but requiring 2 small vlookup tables (2 columns each) anywhere in the workbook, even on another sheet, I created these and made them Named ranges, this one I called 'Grade':
High 10
Medium 5
Low 1

and this one 'Critical'
Critical 10
Serious 8
Moderate 5
Minor 1

then I changed the 'Formula is' formulae to:
Condtion 1
=(VLOOKUP($J20,Grade,2,0)*VLOOKUP($I20,Critical,2,0))>=50
Condition 2
=(VLOOKUP($J20,Grade,2,0)*VLOOKUP($I20,Critical,2,0))>=25
Condition 3
=(VLOOKUP($J20,Grade,2,0)*VLOOKUP($I20,Critical,2,0))>=8

not forgetting to have the default cell background set to green.
The conditional format still refers to the cells $I20 and $J20, which allows the conditional format to be copied and pasted (eg. with the format painter) to any row or column.

p45cal

Re: Will This Link Be Garbled?

re:"Motivational to preview posts, huh?"

Previewing doesn't show up the problem, you'll either have to know what's going to be autolinked, or post anyway and edit your post afterwards if necessary. I'm a bit surprised that it does autolink stuff within the url code tags.

Anyway, now I understand what to do, so thanks to all, regards,
p45cal

Re: Will This Link Be Garbled?

We are in the test area and I was testing whether a url which previewed OK would still be OK on posting. The letters vb in the test message do constitute a hyperlink, but not to the page I pasted. In the end I edited my post on the live forum giving a tinyurl link instead of the original. So no hidden question! (although I sent a message to Andy Pope about it)

see
http://www.developersdex.com/v…9D2E2F%40microsoft.com%3E
p45cal

Re: Remove Cell Borders With Macro

You might be suffering from an Excel Gotcha; see
http://tinyurl.com/yua6xr

I had no problems activating or selecting my cell by just uncommenting either of those lines.

However, the line
.BorderAround LineStyle:=xlContinuous, Weight:=xlThin, ColorIndex:=xlColorIndexAutomatic
(which has a doubtful line continuation character btw) wouldn't undo your borders, only change it from dashed to continuous. Default is no borders.

p45cal

Re: Sum Values For One Column According To The Values In Another Column?

see;
Five ways to subtotal values in repeating data

and take your choice.

p45cal

Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

This in the worksheet code module for the sheet in question (where the flashing cursor is when you right click on the worksheet's tab and choose 'View Code...'

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Range("$A$1:$G$3"), Target)
If Not isect Is Nothing Then
Application.EnableEvents = False
        FoundNoOfConsecutives = 0
        FoundConsecutiveVal = -1
        noOfConsecutives = 1
        ConsecutiveVal = -1
        For Each cll In Range("B" & Target.Row & ":G" & Target.Row).Cells
            If cll.Value = cll.Offset(0, -1).Value Then
                noOfConsecutives = noOfConsecutives + 1
                ConsecutiveVal = cll.Value
                If noOfConsecutives > 3 Then
                    FoundNoOfConsecutives = noOfConsecutives
                    FoundConsecutiveVal = ConsecutiveVal
                End If
            Else
                noOfConsecutives = 1
                ConsecutiveVal = -1
            End If
        Next cll
        If FoundNoOfConsecutives Then
            Range("H" & Target.Row).ClearComments
            Range("H" & Target.Row).AddComment
            Range("H" & Target.Row).Comment.Text Text:="there were  " _
            & FoundNoOfConsecutives & " " & FoundConsecutiveVal & "s"
        Else
            Range("H" & Target.Row).ClearComments
        End If
End If
Application.EnableEvents = True

The
Range("$A$1:$G$3")
bit is the specified range mentioned below. No checks have been put in to check that only one row of cells has been changed; it's just for ideas.

As far as making a user defined function is concerned, the following basic UDF will give a result in a cell (not its comment):

Function blah2(myRange As Range)
       Application.Volatile
        FoundNoOfConsecutives = 0
        FoundConsecutiveVal = -1
        noOfConsecutives = 1
        ConsecutiveVal = -1
        i = myRange.Cells.Count
        myCount = 0
        For Each cll In myRange.Cells
            myCount = myCount + 1
            If cll.Offset(0, 1).Value = cll.Value Then
                noOfConsecutives = noOfConsecutives + 1
                ConsecutiveVal = cll.Value
                If noOfConsecutives > 3 Then
                    FoundNoOfConsecutives = noOfConsecutives
                    FoundConsecutiveVal = ConsecutiveVal
                End If
            Else
                noOfConsecutives = 1
                ConsecutiveVal = -1
            End If
            If myCount + 1 >= i Then Exit For
        Next cll
        If FoundNoOfConsecutives Then
            blah2 = "there were  " _
            & FoundNoOfConsecutives & " " & FoundConsecutiveVal & "s"
        Else
            blah2 = "None"
        End If
End Function

Put this preferably in a standard code module.
It won't work for anything but a single contiguous row of cells with at least 2 cells, but you're looking for more than 3 consecutives anyway.

p45cal

Quote from ceruin

Dear p45cal,

What do you mean by: "it could be made to run on a worksheet change event if the specified range is changed."?

Thanks for your help, now I have a better understanding of VBA on excel. You are my champion! :smile:

Re: Automatically Comment Cell When Row With Consecutive Column Has Same Entry

I think a formula might be very complicated, and I think impossible as far as writing a comment is concerned. So I've gone down the vba route. Explore this (albeit amateur) offering:

Sub blah()
For rw = 1 To 3 'to process rows 1 to 3
FoundNoOfConsecutives = 0
FoundConsecutiveVal = -1
noOfConsecutives = 1
ConsecutiveVal = -1
For Each cll In Range("B" & rw & ":G" & rw).Cells
If cll.Value = cll.Offset(0, -1).Value Then
    noOfConsecutives = noOfConsecutives + 1
    ConsecutiveVal = cll.Value
    If noOfConsecutives > 3 Then
        FoundNoOfConsecutives = noOfConsecutives
        FoundConsecutiveVal = ConsecutiveVal
    End If
Else
    noOfConsecutives = 1
    ConsecutiveVal = -1
End If
Next cll
If FoundNoOfConsecutives Then
    Range("H" & rw).ClearComments
    Range("H" & rw).AddComment
    Range("H" & rw).Comment.Text Text:="there were  " _
    & FoundNoOfConsecutives & " " & FoundConsecutiveVal & "s"
Else
    Range("H" & rw).ClearComments
End If


Next rw


End Sub

The above code has to be manually run but it could be made to run on a worksheet change event if the specified range is changed.

In column H I just put the formula
=sum(A1:G1) in the first row and copied down.

At the very least it should give you some suggestions.

p45cal

Quote from ceruin

Can anyone suggest a formula to automatically comment a cell when rows with given number consecutive columns has same entry example:

A B C D E F G H
1 0 0 0 0 5 6 7 18
2 0 0 5 6 7 0 8 26
3 0 5 0 0 0 0 0 5

row 1: there are minimum 4 '0' in consecutive columns, so H1 is sum of A1:G1 = 18 and automatically comment cell H1 "there are 4 zero"

row 2: because there are no 4 '0' in consecutive columns, H2 is 26 , and no comment

row 3: there are 5 '0' in consecutive columns, so H3 is 5 and automatically commented cell H3 "there are 5 zero"

Display More

Re: Worksheets: Run-time Error 9

Most likely because when execution gets to that line there isn't a sheet named "HiWin04"

Are you renaming sheets here?
try
ThisWorkbook.Worksheets(i).Name = sheet_name(i)
in the loop
Pascal

Re: Issue with format of dates

How does the data get into man.xls?
It is already wrong there. If the data is imported into man.xls, xl can be stopped from trying to interpret dates. At the moment, when it sees a valid English date it seems to flip month and day!
If the file it is imported from is text, then Data|Import External Data|Import Data allows you to specify DMY or MDY

I notice that the file is called 'man', is that because the data comes from the Man stocks and shares data feed supplier? If so, it looks like you've got future prices there - now that would be interesting!
Pascal

Re: Linking Spreadsheets

You have a workbook called pricelist.xls and one called stock.xls.
In stock.xls you have column A headed 'Part No.', column B headed 'Quantity', and you add a third column (C) headed 'Price'; this is where the price will appear.

In the pricelist.xls workbook you have column A headed 'Part No.' and column B headed Price, a;ready populated with prices.

In workbook stock.xls place something similar to the following formula in cell C2 and copy it down:
=VLOOKUP(A2,[Pricelist.xls]Sheet1!$A$1:$B$8,2,FALSE)

you should first replace the 8 in the formula above to the row number of the last part no. in pricelist.xls. I don't think that pricelist.xls needs to be open.

Pascal