Posts by ir121973

Re: VBA Count Unique Values

Hi snb, thank you for taking the time to reply to my post.

I hadn't thought of advanced filter. I'll have a look at this though.

All the best and kind regards

Hi, I wonder whether someone may be able to please.

I'm trying to put together a small script which counts the unique values in a given column upon certain critera being met.

Using an example I found here: https://groups.google.com/foru…l.programming/Ht4z85p80uU I've put together the following code:

Sub countunique()
Dim c As Range, UC As New Collection
   On Error Resume Next
   Sheets("All Data").Select
   LR = Cells(Rows.Count, "F").End(xlUp).Row
    For Each c In Range("F8:F" & LR)
    If c.Offset(, -3) = "Consultancy & Requirements" And c.Offset(, -4) = "IDEA" And c.Offset(, 9) = "Yes" Then
        UC.Add c.Value, CStr(c.Value)
        End If
        Next c
Sheets("High Level Figures").Select
Range("K8") = UC.Count
End Sub

Looking in the sheet "All Data", the code basically searches column C 'offset(, -3)' for the value of "Consultancy & Requirements", in column B 'offset(,-4) column O for the value of "IDEA" and 'offset(,9)' for the value of "Yes".

Where these values are found I'm trying to count the unique values which contain a mixture of text and number values from column F starting at row 8 and then paste the figure in K8 on the sheet "High Level Figures".

Unfortunately, and I'm a little unsure why, the correct number of records are not being extracted.

I just wondered whether someone may be able to look at the code I've written and let me know where I've gone wrong.

Many thanks and kind regards

Re: VBA If Offset Cell Value

Hi StephenR, it's absolutely no problem at all. We all do that

Kind regards

Re: VBA If Offset Cell Value

Hi StephenR, thank you for coming back to me with thsi and for highlighting the error over the 4 rows/4 columns.

I was unable to run your code without receiving an error message, but your observation was key in finding a solution which is as follows:

'***This section of code adds the 'In Flight Projects' count for each month comparing the date in each column header to the 'End Date' on the "In Flight Projects Page"***
            With .Offset(11, 1)
                For Each rCell In Range(.Cells(1), Cells(.Cells(1).Row, Columns.Count).End(xlToLeft))
                    Sheets("In Flight Projects").Activate
                    Range("G8").Select
                    Do Until ActiveCell = ""
                        If ActiveCell.Offset(, -4).Value = "Consultancy & Requirements" Then
                            If ActiveCell.Value >= rCell.Value Then Y = Y + 1
                        End If
                        ActiveCell.Offset(1).Select
                    Loop
                    rCell.Offset(1).Value = Y
                    Sheets("Flexible Resources Profile").Activate
                    Y = 0
                    Next rCell
                End With
                '***End***

Many thanks for all your time.

All the best and kind regards

Re: VBA If Offset Cell Value

Hi StephenR, thank you for taking the time to reply to my post.

Forgive me for perhaps making this not as claer as it could be, but the value "C&R" is found in column C which is 4 columns from column G.

Many thanks and kind regards

Hi, I wonder whether someone can help me please.

I'm using the code below which performs the following function:

• Compare a column of project end dates (column G on the "In Flight Projects" sheet) with the dates in a row of column headers on the "Flexible Resources List" sheet, and
• Where there is a match, count the number of projects which are being worked within that given month.

'***This section of code adds the 'In Flight Projects' count for each month comparing the date in each column header to the 'End Date' on the "In Flight Projects Page"***
            With .Offset(11, 1)
                For Each rCell In Range(.Cells(1), Cells(.Cells(1).Row, Columns.Count).End(xlToLeft))
                    Sheets("In Flight Projects").Activate
                    Range("G8").Select
                    Do Until ActiveCell = ""
                        If ActiveCell.Value >= rCell.Value Then y = y + 1
                        ActiveCell.Offset(1).Select
                    Loop
                    rCell.Offset(1).Value = y
                    Sheets("Flexible Resources Profile").Activate
                    y = 0
                    Next rCell
                End With
                '***End***

The problem I have is that I'm trying to add an additional 'If' satement which before this line Do Until ActiveCell="" I want to say if the cell value offset minus 4 to G8 has the value "C&R" then run the rest of the code.

It think I'm right in using this line

If ActiveCell.Offset(-4, 0).Value = "C&R" Then

but despite inserting this at different points in the code I'm unable to get this to work.

Many thanks and kind regards

I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.

Re: VBA Send Email & Attachments To Recipients

Hi @scott_n_pnx,

I've now got this to work. I'd realised that the columns which contain the file paths must be the actual file paths and not the 'Displayed Name'. I've now changed this and the macro works fine.

Many thanks and kind regards

Re: VBA Send Email & Attachments To Recipients

Hi apo, thank you for takng the time to reply to my post and for pointing out the error.

I've now got this to work. I'd realised that the columns which contain the file paths must be the actual file paths and not the 'Displayed Name'. I've now changed this and the macro works fine.

Many thanks and kind regards

Re: VBA Send Email & Attachments To Recipients

Hi scott_n_phnx, thank you very much for taking the time to reply to my post.

I've amended my code to include the line you suggest, but unfortunately I receieve an 'Invalid qualifier' error message, with debug highlighting the newly inserted row as the source.

Many thanks and kind regards

Hi, I wonder whether someone could help me please.

I'm using the tutorial at the link below to create a script which emails designated email recipients and two excel sheets for each.

www.rondebruin.nl/win/s1/outlook/amail6.htm

The code works fine except for this section.

Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")

I'd like to change this so the range starts at row 4. I thought this would be a simple change, amending the row 1 reference to row 4, but unfortunately the macro fails to craete the email.

I'm not really sure how to progress, but I just wondered whether someone my be able to look at this please and offer some guidance on how I may go about achieving this.

Re: VBA Apply Subtotals To Specific Sheets

Hi pike, thank you for taking the time to reply to my post and for the solution, it's exactly what I was after.

All the best and kind regards

Hi, I wonder whether someone can help me please.

I'm using the code below to apply subtotals to a given sheet.

Sub Subtotals()
    
    Dim LastRow As Long
    Dim LngCounter As Long
    Dim lngStart As Long
    
    Application.ScreenUpdating = False
    
    Const StartRow As Long = 8
    
    lngStart = Range("B" & Rows.Count).End(xlUp).Row
    For LngCounter = Range("B" & Rows.Count).End(xlUp).Row To 8 Step -1
        If Cells(LngCounter, "B").Value <> Cells(LngCounter - 1, "B").Value Then
            Rows(lngStart + 1).Insert
            With Cells(lngStart + 1, "C")
                .Value = Cells(LngCounter, "B").Value & " FTE Subtotal"
                .HorizontalAlignment = xlRight
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 8388608
                End With
                With .Font
                    .Name = "Lucida Sans"
                    .Bold = True
                    .Size = 10
                    .ColorIndex = 2
                End With
                
                'The code below is used to populate the 'Subtotal' row in columns D,E and F
                With .Offset(0, 1)
                    .Formula = "=SUM(D" & LngCounter & ":D" & lngStart & " )"
                    .NumberFormat = "#,##0.00"
                    With .Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 16764057
                    End With
                    With .Font
                        .Name = "Lucida Sans"
                        .Bold = True
                        .Size = 10
                    End With
                    .AutoFill Destination:=.Resize(1, 14), Type:=xlFillDefault
                End With
                lngStart = LngCounter - 1
            End With
        End If
        Next LngCounter
        Columns("B:Q").AutoFit
    End Sub

The code works, but I'm now looking to change this and I'm a little unsure how to proceed.

I'd like to apply the subtotals to all but the following sheets:

Macros
All Data
Resources List, and
Unique Records

I just wondered whether someone could possibly look at this please and offer some guidance on how I may be able to acheive this.

Many thanks and kind regards

Re: VBA Count & Sum Matching Records

Hi venkat1926, thank you for coming back to me with this.

I've checked the sheet I posted and the "Expected Outcome" values are correct. The figure shown i.e. "1" is correct because it is the only record where the date shown in column J of the "All Data" sheet is the same as cell B2 on the "Expected Outcome" sheet.

Kind Regards

Re: VBA Dynamic No. Of Column Headers

Hi royUK, thank you for confirming this.

All the best and kind regards

Re: VBA Dynamic No. Of Column Headers

Hi royUK, ah I see. Yes, I'm learning, so I appreciate that my code may not be the best.

But sincere thanks for putting the code together, I really appreciate it, and certainly from a beginners perspective one I understand.

May I ask please why this method is more efficient than a loop?

Many thanks and kind regards

Re: VBA Dynamic No. Of Column Headers

Hi snb, thank you for taking the time to come back to me with this.

As you'll see HaHoBe, was able to provide the line I was looking for, and as explained to royUK, I'm trying to automate the process for the less experienced Excel user.

Many thanks and kind regards

Re: VBA Dynamic No. Of Column Headers

Hi royUK, thank you for taking the time to reply to my post.

As you will see HaHoBe was able to provide the line I needed.

I too though had thought of using A formula, but this was part of a much larger script, and I'm trying to automate the process as much as possible to cater for you users who are not quite as proficient in using Excel as one would hope.

Many thanks and kind regards

Re: VBA Count & Sum Matching Records

Hi venkat1926, as promised I've looked at the file I sent, and everything is correct, but I will clarify the points you raised.

• The range "ADPeriod" is column J on the "All Data" sheet and must be the same value in cell B2 on the "Expected Outcome" sheet .
• The range "ADPLOB" is column B on the "All Data" sheet and must be the same values as those in the range B6:B10 on the "Expected Outcome" sheet, and,
• The range "ADJRole" is column I on the "All Data" sheet and must be the same value in cell C4 on the "Expected Outcome" sheet.

The values which I'd like to be counted please are in the range "ADSName" which is column D on the "All Data" sheet, and the values which I'd liked to be summed are in the range "ADAFTE" which is column N on the "All Data" sheet.

I hope this helps, and once again for taking the time to help.

Many thanks and kind regards

Re: VBA Dynamic No. Of Column Headers

Hi HaHoBe, thank you very much for taking the time to rply to my post, and for the solution which works great.

All the best and kind regards

Re: VBA Count & Sum Matching Records

Hi venkat1926, thank you very much for taking the time to reply to my post, and my sincere apologies for the errors, I had thought the spreadsheet was ok.

I'm very sorry but I'm at work at the moment and unable to download and amend the sheet.

I will however make the changes as soon as I arrive home and update the file for you, if that's ok?

Once agian, may apologies, many thanks and kind regards