# Search:

Type: Posts; User: Peter_SSs; Keyword(s):

Page 1 of 6 1

1. ## Thread: help with macro issue

by Peter_SSs
Replies
2
Views
15

### Re: help with macro issue

Here is another approach that may suit you.
Make sure that Sheet1 is the left hand sheet of your workbook and then try this macro.

Sub SumAcrossWorksheets()
With...
2. ## Thread: Highlight only last ones that finds in end of the column

by Peter_SSs
Replies
18
Views
284

### Re: Highlight only last ones that finds in end of the column

You're welcome.

You would have also needed to include the change to white font in that code.
3. ## Thread: Copy value from cell above if blank

by Peter_SSs
Replies
5
Views
81

### Re: Copy value from cell above if blank

In cell A1 of Sheet2, try this formula and copy down.

=IF(Sheet1!B1="","",LOOKUP("zzz",Sheet1!A\$1:A1))

by Peter_SSs
Replies
7
Views
171

### Re: Addig specif numbers within one cell

.. & a non-looping udf

Function BrSum(s As String) As Double
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "(^|\])(.*?)(\[|\$)"
BrSum = Evaluate(.Replace(s, "+")...

by Peter_SSs
Replies
7
Views
171

### Re: Addig specif numbers within one cell

If your sample is fairly representative. That is, the text in the cell is not too long then this standard worksheet formula may do what you want. Currently it is expecting a maximum of 5 numbers in...
6. ## Thread: Highlight only last ones that finds in end of the column

by Peter_SSs
Replies
18
Views
284

### Re: Highlight only last ones that finds in end of the column

Try

Sub Highlight_Last_v4()
Dim c As Range, cFound As Range

For Each c In Range("C3:K3")
Set cFound = Range("C6:C37").Find(What:=c.Value, SearchDirection:=xlPrevious)
c.Copy...
7. ## Thread: Too many arguments in IF function

by Peter_SSs
Replies
7
Views
65

### Re: Too many arguements in IF function

No, mine was untested - because i don't have the other workbook set up & I made a typo. Leave out this errant letter & try again. :oops:
8. ## Thread: Too many arguments in IF function

by Peter_SSs
Replies
7
Views
65

### Re: Too many arguements in IF function

You're welcome.

One further comment. I don't know if the data in your 'Staff info summary' sheet changes much but if any cell in columns B:DQ changes (that is over 120 million cells) that formula...
9. ## Thread: Too many arguments in IF function

by Peter_SSs
Replies
7
Views
65

### Re: Too many arguements in IF function

That depends on exactly what the formula is trying to achieve. My best guess is this.

=IF(\$D\$1="","",IF(VLOOKUP(\$D\$1,'[Employment Contract (Causual Employee) HC_May20180903_D_SB.xlsx]Staff info...
10. ## Thread: Ranking with Tiebreakers

by Peter_SSs
Replies
3
Views
124

### Re: Ranking with Tiebreakers

In that case, try this.

<b>Rank in Groups</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff;...
11. ## Thread: Highlight only last ones that finds in end of the column

by Peter_SSs
Replies
18
Views
284

### Re: Highlight only last ones that finds in end of the column

.. even simpler:

Sub Highlight_Last_v2()
Dim c As Range, cFound As Range

For Each c In Range("C3:K3")
Set cFound = Range("C6:C37").Find(What:=c.Value, SearchDirection:=xlPrevious)
...

by Peter_SSs
Replies
11
Views
246

### Re: Need some help a formula Please

OK, thanks for the confirmation.
13. ## Thread: Highlight only last ones that finds in end of the column

by Peter_SSs
Replies
18
Views
284

### Re: Highlight only last ones that finds in end of the column

Another way with the same comment as Fluff about spaces v No spaces.
I also assume that it is a mistake that you have cell C33 coloured and not C35?

Sub Highlight_Last()
Dim c As Range,...

by Peter_SSs
Replies
5
Views
220

### Re: Student Choice

Yes, as written, people at the bottom of the list are getting higher priority than those at the top. And of course Choice 1 gets a higher priority than...
15. ## Thread: Vba - insert two columns after each occurrence of dept name

by Peter_SSs
Replies
6
Views
162

### Re: Vba - insert two columns after each occurrence of dept name

Try

Sub Insert_Columns_v2()
Dim c As Long

For c = Cells(3, Columns.Count).End(xlToLeft).Column + 1 To 8 Step -4
Columns(c).Resize(, 2).Insert
Cells(3, c).Resize(, 2).Value =...

by Peter_SSs
Replies
3
Views
134

### Re: Is there a SUM-minus formula instead of SUMPRODUCT?

.. or possibly just this?

=SUM(B2:B5)-SUM(A2:A5)
17. ## Thread: Merge two ranges together and wrap them into each other

by Peter_SSs
Replies
4
Views
160

### Re: Merge two ranges together and wrap them into each other

Since I had been playing around with this too, I will post my offering. This is a bit different as it does not require any looping. My testing showed it to be about 3 times as fast as the post 2 code...

by Peter_SSs
Replies
1
Views
77

### Re: Udf vlookup

Why do you need a UDF? Could we use standard worksheet formulas like this?
I've done this on a single sheet but it could easily be adapted to two.

<b>Check Mark Up</b><br /><br /><table...
19. ## Thread: Ranking with Tiebreakers

by Peter_SSs
Replies
3
Views
124

### Re: Ranking with Tiebreakers

I don't fully understand the requirement.
You said you wanted to use column L as the tiebreaker instead of the order in the sheet. I can see that in the green rows but in the two amber sections you...
20. ## Thread: Conditional formatting formula for cell

by Peter_SSs
Replies
6
Views
145

### Re: Conditional formatting formula for cell

No problem. They were basically your formulas. ;)
21. ## Thread: Conditional formatting formula for cell

by Peter_SSs
Replies
6
Views
145

### Re: Conditional formatting formula for cell

I think so. For this sample select D3:H5 and enter the CF rules (formulas) as shown. Make sure your rules end up in this order when you go in to Conditional formatting -> Manage Rules ... (you can...

by Peter_SSs
Replies
11
Views
246

### Re: Need some help a formula Please

Hmm, I thought this meant that after 35% was deducted from the selling price, you still made your 10% profit.

So in my first example, if you sold for 169.23 and deducted 35% of that (59.23) you...

by Peter_SSs
Replies
5
Views
220

### Re: Student Choice

OK, let's see if this is any use.

In my sample, I have 20 students and 5 possible Activities and each student can have a maximum of 5 choices. Those choices are entered in A1:F21 as shown below.
...

by Peter_SSs
Replies
5
Views
190

### Re: Excel Header and Footers

Welcome to the MrExcel board!

Another code to try. Further detail on implementation ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use...

by Peter_SSs
Replies
11
Views
246

### Re: Need some help a formula Please

Is this what you mean?

<b>Selling Price</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; ...
Results 1 to 25 of 150
Page 1 of 6 1