Posts by Moe Kadhom

Fantastic.

its working great now.

Thanks for your help

Hi Kj
This code is working but when I delete a row, the priorities are not auto adjusted.
For example, top 5 rows have priorities of 1,2,3,4,5. If I delete row 2, the current result is 1,3,4,5,6 but it should adjust all the priorities to be 1,2,3,4,5.
The code need to re-adjust the priorities every time the number changes ( its doing that at the moment) or a row is deled or added to the spreadsheet.

See attached sample data.

Thanks

Hi KJ
I still get a debug when a row is deleted from the spreadsheet. Below line is highlighted.

thanks

Target.Value = Target.Value + Sgn(Target.Value - (Target.Row - .Row)) / 2

Hi
I have a spreadsheet that has a list of tasks with priorities ( 1,2,3,4,,,,etc) set in column A. I found below code that does the job but the priorities need to be in column B.

Basically When any of the numbers are changed in column B, the table is sorted automatically to move the rows to the correct position.

however, there are couple of issues:

• The macro debugs every time a row is deleted or column added or any other format changes to the priorities.
• There is a fixed range of data ( 300 rows) in the macro that preferably needs to be dynamic based on how many rows have data In them.

Would any of you smart people can modify this macro to rectify the issues listed above?

Happy New Year In advance.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RangeOfInterest As Range, i As Long
    Dim temp As String
    Set RangeOfInterest = Range("A1:AA300")


    If Not Application.Intersect(Target, RangeOfInterest.Columns(2)) Is Nothing Then
        With RangeOfInterest
            If .Row < Target.Row Then
                Application.EnableEvents = False
                Target.Value = Target.Value + Sgn(Target.Value - (Target.Row - .Row)) / 2
               
                .Sort key1:=.Cells(1, 2), order1:=xlAscending, Header:=xlYes
                .Columns(2).Offset(1, 0).Resize(.Rows.Count - 1, 1).Value = [Row(1:300)]
            End If
        End With
    End If
    Application.EnableEvents = True
End Sub

Thanks

Re: VBA fails first time but runs correctly the secound time

Hi Rory
yes, every time I open the workbook and it is not in protected view. Macros are always enabled.

thanks

Re: VBA fails first time but runs correctly the secound time

Hi Rory
This error only happens when the macro is run the first time. the second time there is no error and the macro run fine with the correct results.

thanks

Re: VBA fails first time but runs correctly the secound time

[ATTACH=CONFIG]73077[/ATTACH]

Hi

The workbook has many pictures in it. The macro deletes all the pictures but leaves the ones that is called "crown" and the one that matches the file name.

the code is below:

Sub FleetNationalDeletePic()
                 Dim pic As Picture
    wbName = WorksheetFunction.Replace(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, "."), 5, "")
    Sheets("Title").Select
    For Each pic In ActiveSheet.Pictures
        If pic.Name <> wbName And LCase(pic.Name) <> "crown" Then
            pic.Delete
        End If
    Next pic
End Sub

When the macro is run, an error appears and the debug highlights "If pic.Name <> wbName And LCase(pic.Name) <> "crown" Then". however, running the macro again does not run an error.

I don't understand why this is happening and I need to do this for a lot of workbooks. Can any of you smart people help me solve this issue?

the file is a bit too big to attach here so I am happy to email through If required.

thanks

Re: Average a range based on multiple Condition

Hi Charles
that's it!! thanks for your help

Re: Average a range based on multiple Condition

Hi Charles
The result in Q3 should show the average of the months that have 4 weeks. so if i do the formula manually
4 Week Average Q3=AVERAGE(B2,D2,E2,G2,H2,J2,K2,M2,N2)= 13,348
5 week Average Q4=AVERAGE(C2,F2,I2,L2) = 11,212

I would have done this excatly as done above but the months will change so the formula needs to automaticly average any month that is 4 weeks and also 5 weeks.

I hope I have made it clear. thanks for your help

Re: Average a range based on multiple Condition

Hi Charles
close but not really!!

I need the average across the total jobs calculated for all the 4 week months and also for the 5 weeks months. the formulas need to be entered in Q3 and Q4.

thanks

Hi
Please refer to the attached for a sample of my data.

I have data set out for 12 months starting from April that shows number of jobs that will be in each month. I have another set of data that shows how many weeks each month will have in them.

What I want is for a formula to get the average of the 4 weeks months and 5 weeks months separately. I tired to use vlookup but my formula got too big.

is there any way this can be done?

thanks for your help in advance

Re: Seprate Unique values for three tables into seprate sheets

Hi Charles
please ignore above, it was a mistake from my part!!!

thanks

Re: Seprate Unique values for three tables into seprate sheets

Hi Charles
I have added new data to the workbook but the macro keeps debugging. There are more rows to each table so unsure if this is causing the issue or not.

Can you kindly have a look and rectify?

Appreciate your help

Re: Seprate Unique values for three tables into seprate sheets

Hi Charles
The macro is working great now.

Thanks for your help

Re: Seprate Unique values for three tables into seprate sheets

Hi Charles
much appreciate your help with this.

I ran the macro but some sheets are showing the incorrect tables. as an example please refer to sheet "NSW North Area 1". the first table has "NSW ACT Trade Area 1" data which should not be in this sheet. the second table has the correct date but the incorrect heading.

thanks for you help

Hi
Please see attached the " Data" workbook.

What I want is for a macro to separate each unique value in column A into a separate sheet but keep the heading for each table. So basically three tables for every unique value in column A into separate sheet. if there are no data for some of the tables, then nothing needs to be shown.

See attached the desired result. This will save me a lot of time every month so I would appreciate it if someone can lend a hand and come up with a macro that does above.

Let me know if you have any questions.

Much appreciate your help.

Thanks

Hi
I have a proposal template ( which is in a word format) that I need to populate using a user form. Most of the template is generic and does not need to be changed but there are around 20-25 text that need to be linked to the user form so they can be populated.

The user form will have data entry boxes, drop down boxes and possibly check boxes. It also needs a button to clear so they can start over, a cancel button to exit and a button to convert the file to PDF.

The file is too big to attach here but Once the project is accepted I will communicate exactly what is required via email but above is basically what I am trying to achieve.

10 % deposit has already been paid to Ozgrid.

Thanks

Re: Conditional formatting based on duplicated values in two columns

Hi KJ
Works perfectly. Thanks for your help

Hi All
I have below sample data. also see attached.

I want a conditional formatting formula to highlight the range A:H when the value in B ( FSR) and value in H (Serial number) are duplicated. The condition should be when both are duplicated on the same row.

so for example, FSR 1243 should be highlighted as it has the serial number J905727 and both are repeated on the third line.

can one you wonderful people assist with a formula?

thanks for your help

[TABLE="width: 887"]

Sample Data

Date

FSR

Lead

Service Tech Name

Points

Customer Name

Job card Number

Serial Number

[TD="align: right"]42552[/TD]
[TD="align: right"]1243[/TD]

Overhaul

[TD="align: right"]20[/TD]

J905727

[TD="align: right"]42552[/TD]
[TD="align: right"]3511[/TD]

Overhaul

[TD="align: right"]20[/TD]

9A163652

[TD="align: right"]42552[/TD]
[TD="align: right"]1243[/TD]

Overhaul

[TD="align: right"]20[/TD]

J905727

[TD="align: right"]42552[/TD]
[TD="align: right"]3811[/TD]

Overhaul

[TD="align: right"]200[/TD]

1A187451R

[TD="align: right"]42555[/TD]
[TD="align: right"]3346[/TD]

Traction Battery

[TD="align: right"]20[/TD]

1A178318R

[TD="align: right"]42555[/TD]
[TD="align: right"]1549[/TD]

Traction Battery

[TD="align: right"]20[/TD]

9A141366R

[TD="align: right"]42555[/TD]
[TD="align: right"]2468[/TD]

Traction Battery

[TD="align: right"]20[/TD]

H3968

[TD="align: right"]42555[/TD]
[TD="align: right"]2125[/TD]

Traction Battery

[TD="align: right"]20[/TD]

W3402

[TD="align: right"]42555[/TD]
[TD="align: right"]2468[/TD]

Traction Battery

[TD="align: right"]20[/TD]

H3968

[TD="align: right"]42555[/TD]
[TD="align: right"]2959[/TD]

Traction Battery

[TD="align: right"]20[/TD]

W3443SH

[TD="align: right"]42555[/TD]
[TD="align: right"]3346[/TD]

Traction Battery

[TD="align: right"]20[/TD]

J905727

[/TABLE]